February 11, 2008 at 3:15 pm
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
February 12, 2008 at 6:15 am
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:
[font="Courier New"]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[/font]
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
February 12, 2008 at 6:42 am
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)
...[font="Courier New"]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[/font]
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
February 12, 2008 at 7:03 am
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
February 12, 2008 at 7:05 am
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
February 12, 2008 at 7:14 am
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
February 12, 2008 at 8:23 am
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
May 29, 2008 at 7:38 am
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) ?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply