Flat File with random bad rows.

I have a text file that come from our client that is Column deliminated by ~ and row deliminated by {CR}{LF}.

There is a comment field that appearently is not cleaned up and has {CR}{LF} within the comment field.

I am new to SSIS and I'm wondering if there is a way to detect and correct the bad rows?

example file formet:

ORDERID~DATE~Comment~Address

1~2/3/2007~Some Comment~1234 oak st

2~2/3/2007~Some messed

up comment~345 oak st.

3~2/3/2007~Another comment~3214 asdf blvd.

Thank you.

You can use the Microsoft Visual Basic .NET RTrim function in a script run from the Script Component (configured as a transformation), to remove white space characters such as line feed and carriage return characters.

So the package data flow would include a Flat File Source connected to a Script Component. The output of the Script Component can then be sent to a destination or another transformation.

For information about the VB function, see "LTrim; RTrim; and Trim functions" at http://msdn2.microsoft.com/en-us/library/h9wz3dez(VS.71).aspx. For information about the Script Component, see "Extending the Data Flow with the Script Component" at http://msdn2.microsoft.com/en-us/library/ms136118.aspx.

|||If you do not want to mess with scripting you could use the REPLACE function in a Derived Column task and replace the space with another character.|||

How do you specify the line-feed character in the REPLACE function?

|||

Try

Code Snippet

\n

Generally, you use a \ character to escape special characters. \n indicates new line, \t indicates tab, etc.

|||

Thanks John, that works great

|||

If you enclose the escape character in quotes ("\n"), the expression will parse. For more information about using characters that require escape sequences in string literals, see "Literals (SSIS)" at http://msdn2.microsoft.com/en-us/library/ms141001.aspx.

Flat File with random bad rows.

I have a text file that come from our client that is Column deliminated by ~ and row deliminated by {CR}{LF}.

There is a comment field that appearently is not cleaned up and has {CR}{LF} within the comment field.

I am new to SSIS and I'm wondering if there is a way to detect and correct the bad rows?

example file formet:

ORDERID~DATE~Comment~Address

1~2/3/2007~Some Comment~1234 oak st

2~2/3/2007~Some messed

up comment~345 oak st.

3~2/3/2007~Another comment~3214 asdf blvd.

Thank you.

You can use the Microsoft Visual Basic .NET RTrim function in a script run from the Script Component (configured as a transformation), to remove white space characters such as line feed and carriage return characters.

So the package data flow would include a Flat File Source connected to a Script Component. The output of the Script Component can then be sent to a destination or another transformation.

For information about the VB function, see "LTrim; RTrim; and Trim functions" at http://msdn2.microsoft.com/en-us/library/h9wz3dez(VS.71).aspx. For information about the Script Component, see "Extending the Data Flow with the Script Component" at http://msdn2.microsoft.com/en-us/library/ms136118.aspx.

|||If you do not want to mess with scripting you could use the REPLACE function in a Derived Column task and replace the space with another character.

|||

How do you specify the line-feed character in the REPLACE function?

|||

Try

Code Snippet

\n

Generally, you use a \ character to escape special characters. \n indicates new line, \t indicates tab, etc.

|||

Thanks John, that works great

|||

If you enclose the escape character in quotes ("\n"), the expression will parse. For more information about using characters that require escape sequences in string literals, see "Literals (SSIS)" at http://msdn2.microsoft.com/en-us/library/ms141001.aspx.

Flat File with random bad rows.

I have a text file that come from our client that is Column deliminated by ~ and row deliminated by {CR}{LF}.

There is a comment field that appearently is not cleaned up and has {CR}{LF} within the comment field.

I am new to SSIS and I'm wondering if there is a way to detect and correct the bad rows?

example file formet:

ORDERID~DATE~Comment~Address

1~2/3/2007~Some Comment~1234 oak st

2~2/3/2007~Some messed

up comment~345 oak st.

3~2/3/2007~Another comment~3214 asdf blvd.

Thank you.

You can use the Microsoft Visual Basic .NET RTrim function in a script run from the Script Component (configured as a transformation), to remove white space characters such as line feed and carriage return characters.

So the package data flow would include a Flat File Source connected to a Script Component. The output of the Script Component can then be sent to a destination or another transformation.

For information about the VB function, see "LTrim; RTrim; and Trim functions" at http://msdn2.microsoft.com/en-us/library/h9wz3dez(VS.71).aspx. For information about the Script Component, see "Extending the Data Flow with the Script Component" at http://msdn2.microsoft.com/en-us/library/ms136118.aspx.

|||If you do not want to mess with scripting you could use the REPLACE function in a Derived Column task and replace the space with another character.|||

How do you specify the line-feed character in the REPLACE function?

|||

Try

Code Snippet

\n

Generally, you use a \ character to escape special characters. \n indicates new line, \t indicates tab, etc.

|||

Thanks John, that works great

|||

If you enclose the escape character in quotes ("\n"), the expression will parse. For more information about using characters that require escape sequences in string literals, see "Literals (SSIS)" at http://msdn2.microsoft.com/en-us/library/ms141001.aspx.

Flag Random Record

I am using this select statement to radomly display a record

SelectCommand="SELECT TOP 1 * FROM [TBL_Example] ORDER BY NEWID()

I need to, however, flag this record, to determine if it has already been previously randomly selected, and won't take part in future random selections.

I will need to add a where clause to the above, but what I am unsure of is what I should do for the insert statement. I guess I could figure this out on my own as well if I could determine a means to prgramatically store my PK from the above record in session.

Any ideas?I probably need a statement like this in my code behind:

Protected Sub SqlDataSource1_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) Handles SqlDataSource1.Selecting
SqlDataSource1.UpdateParameters("Flag").DefaultValue = "True"
End Sub

and then I probably need some insert parameters, here is what I am working with right now with my sqldatasource

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT TOP 1 * FROM [CIT_ContactInfo] ORDER BY NEWID()"
updatecommand="update [CIT_ContactInfo] set [Flag] = @.Flag where [UserName] = @.UserName">
<UpdateParameters>
<asp:Parameter Name="Flag" Type="boolean" />
</UpdateParameters>
</asp:SqlDataSource
Does any of this look remotely correct?