SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Export to tab delimited file via SSIS


Export to tab delimited file via SSIS

Author
Message
Cory E.
Cory E.
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1883 Visits: 10845
Good day -

Here were the requirements I was given: Create numerous Tab Delimited files from SQL server tables. End result needs to be one line per record in a text file.

I created the SSIS package, and all works well, EXCEPT, I am getting carriage returns / line breaks from the text fields. I tried doing a series of replaces "REPLACE(REPLACE(REPLACE(CAST(s._Comments AS VARCHAR(8000)), CHAR(10) + CHAR(13), ''), CHAR(10), ''), CHAR(13), '')", and this appears to work in T-SQL. I then placed it in the SSIS data flow task, however I started getting the following error, "Error: 0xC0208265 at _Information, xxx [1]: Failed to retrieve long data for column "Comments"". Next I tried to make a derived column, trying to do the replace there instead of the TSQL, but that failed as well.

Question - is there a way to return a text field with carriage returns and line breaks to be one long string in SSIS?

Thanks!

-- Cory
Frank Bazan
Frank Bazan
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1492 Visits: 1087
Hi Cory,

Probably the easiest way to achieve this would be a script transformation component.

In the script component editor, add a column to the output.

In the script designer, try something like this:
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
If Row.Text.Contains(vbLf) Then
Row.OutputClean = Row.Text.Replace(vbLf, "")
Else
Row.OutputClean = Row.Text
End If
End Sub


I gave this a quick test with one of my tables and it seemed to pick up the linefeeds, so I'm guessing it would probably do the trick.

Let us know how it goes.

Kindest Regards,

Frank Bazan
Cory E.
Cory E.
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1883 Visits: 10845
Frank,
Thank you for the help! I did try this, however, this is something I have never done before, and seem to have come to a couple road blocks...
First off, a copy n paste (changing the column name) resulted in this error: ' ' is not a member of 'Microsoft.SQLServer.DTS.Pipeline.BlobColumn'.
Next, I went with:If Row.Comments.ToString.Contains(vbLf) Then row.outputclean...however, output clean does not seem to be in the drop down list...

Am I missing something? Any help is always greatly appreciated.

C

Frank Bazan (2/12/2008)
...
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
If Row.Text.Contains(vbLf) Then
Row.OutputClean = Row.Text.Replace(vbLf, "")
Else
Row.OutputClean = Row.Text
End If
End Sub


I gave this a quick test with one of my tables and it seemed to pick up the linefeeds, so I'm guessing it would probably do the trick.

Let us know how it goes.


-- Cory
Frank Bazan
Frank Bazan
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1492 Visits: 1087
Hi Cory,

My apologies, the explanation wasn't particularly detailed.

1st thing to do is drop the script component editor on the the dataflow, between your OLE DB Source and your flat file destination. You will be asked what kind of script you want. Choose "Transformation".
Then drag the pipeline from your OLE DB source to your script component. Once you have connected these, the upstream columns will be available to your script.
Next, go to the tab [Input Columns] where you will see a list of columns. Check the one which you are using as part of the transform (Comments I think you said).
Now, go to [Inputs and Outputs], expand "outputs" and highlight the folder "Output Columns". Add a column to this output and name it whatever you want ensuring that you have assigned it the correct datatype. (In your case will likely be DT_WSTR).
Finally, go to [script] and write your code. Intellisense should give you most of the info you need (type "Row." and you will find that the input and output columns should appear in a drop down box).

Let me know if you are still have probs.

Cheers

Kindest Regards,

Frank Bazan
Cory E.
Cory E.
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1883 Visits: 10845
Update - It looks like I was able to get the correct syntax:

If Row.CommentsClean.ToString.Contains(vbLf) Then
Row.CommentsClean.ToString.Replace(vbLf, "")
Else
Row.CommentsClean = Row.CommentsClean
End If

but now I get this, any help anyone?:

SSIS package "_Export.dtsx" starting.
Information: 0x4004300A at _Information, DTS.Pipeline: Validation phase is beginning.
Information: 0x40043006 at _Information, DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at _Information, DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x402090DC at _Information, Flat File Destination [4826]: The processing of file "\\server\share\_Info.txt" has started.
Information: 0x4004300C at _Information, DTS.Pipeline: Execute phase is beginning.
Error: 0xC0047062 at _Information, Script Component [4935]: Microsoft.SqlServer.Dts.Pipeline.UnsupportedValueTypeForTextStreamColumnException: The value being written to DT_TEXT or DT_NTEXT column is not a string or an array of bytes.
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e)
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper90 wrapper, Int32 inputID, IDTSBuffer90 pDTSBuffer, IntPtr bufferWirePacket)
Error: 0xC0047022 at _Information, DTS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Script Component" (4935) failed with error code 0x80131600. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
Error: 0xC0047021 at Practice Information, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0x80131600. There may be error messages posted before this with more information on why the thread has exited.
Information: 0x40043008 at _Information, DTS.Pipeline: Post Execute phase is beginning.
Information: 0x402090DD at _Information, Flat File Destination [4826]: The processing of file "\\server\share\_Info.txt" has ended.
Information: 0x40043009 at _Information, DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at Practice Information, DTS.Pipeline: "component "Flat File Destination" (4826)" wrote 0 rows.
Task failed: Practice Information
SSIS package "_Export.dtsx" finished: Success.

-- Cory
Cory E.
Cory E.
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1883 Visits: 10845
Frank! That was incredibly helpful! It now works...well, it runs...however, my linebreaks are still there...

question, since I dont know for sure, but is vbLf like line feed? perhaps I also need to test for something like vbCr for carriage return?

here is what ended up working:

If Row.Comments.ToString.Contains(vbLf) Then
Row.CommentsClean = Row.Comments.ToString.Replace(vbLf, "")
Else
Row.CommentsClean = Row.Comments.ToString
End If

Frank Bazan (2/12/2008)
Hi Cory,

My apologies, the explanation wasn't particularly detailed.

1st thing to do is drop the script component editor on the the dataflow, between your OLE DB Source and your flat file destination. You will be asked what kind of script you want. Choose "Transformation".
Then drag the pipeline from your OLE DB source to your script component. Once you have connected these, the upstream columns will be available to your script.
Next, go to the tab [Input Columns] where you will see a list of columns. Check the one which you are using as part of the transform (Comments I think you said).
Now, go to [Inputs and Outputs], expand "outputs" and highlight the folder "Output Columns". Add a column to this output and name it whatever you want ensuring that you have assigned it the correct datatype. (In your case will likely be DT_WSTR).
Finally, go to [script] and write your code. Intellisense should give you most of the info you need (type "Row." and you will find that the input and output columns should appear in a drop down box).

Let me know if you are still have probs.

Cheers


-- Cory
Frank Bazan
Frank Bazan
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1492 Visits: 1087
Hi Cory,

Thats right - you may need to change it to vbCRLF, vbNewLine or whatever the special character happens to be. (If you hit F1 when your cursor is on vbLf you should get a list of all the special character codes). Failing that you could use the actual Character code in the same way that you do with SQL. i.e. Replace vbCrLf with Chr(13) & Chr(10).

Try putting a message box in the IF clause too and you should be able to see the actual text that is being trapped.

Msgbox(row.Text) - Alternatively, if that's going to produce too many rows, do something else to the Clean row like concatenating it on the end of an obvious string, then add a dataviewer to the script output and again you should be able to see what rows are affected so you can inspect them more closely.

Good luck

Kindest Regards,

Frank Bazan
radekjaszek
radekjaszek
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 8
I have the same problem as it was described in this post. Can You explain me how to use DT_TEXT data type in this way (like DT_WSTR in Comments) ?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search