Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Export to tab delimited file via SSIS Expand / Collapse
Author
Message
Posted Monday, February 11, 2008 3:15 PM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, January 11, 2012 2:47 PM
Points: 681, Visits: 10,845
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
Post #454100
Posted Tuesday, February 12, 2008 6:15 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, February 28, 2014 4:38 AM
Points: 268, Visits: 1,072
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
Post #454366
Posted Tuesday, February 12, 2008 6:42 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, January 11, 2012 2:47 PM
Points: 681, Visits: 10,845
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
Post #454384
Posted Tuesday, February 12, 2008 7:03 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, February 28, 2014 4:38 AM
Points: 268, Visits: 1,072
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
Post #454398
Posted Tuesday, February 12, 2008 7:05 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, January 11, 2012 2:47 PM
Points: 681, Visits: 10,845
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
Post #454401
Posted Tuesday, February 12, 2008 7:14 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, January 11, 2012 2:47 PM
Points: 681, Visits: 10,845
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
Post #454406
Posted Tuesday, February 12, 2008 8:23 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, February 28, 2014 4:38 AM
Points: 268, Visits: 1,072
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
Post #454458
Posted Thursday, May 29, 2008 7:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 20, 2008 2:40 AM
Points: 1, 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) ?
Post #508387
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse