August 4, 2009 at 5:14 pm
I am trying to store a large formated string in an TEXT (text stream [DT_TEXT]) column.
The error above is self explanitory.
How do I convert the String in the Script Component of SSIS (VS 2005) so that it is accepted by the NText output column? The text needs to be able to also be visible (not stored as binary).
Changing from SQL 2000 Server or altering the configuration of the server is not currently an option.
Any assistance appreciated.
Cheers
Wee!
August 5, 2009 at 8:31 am
Can you post your script component code?
I'd recommend using a Data Conversion transformation to change your string data to DT_TEXT or DT_NTEXT.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 5, 2009 at 5:30 pm
Imports System
Imports System.Data
Imports System.Math
Imports System.Text
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim DSNotes As New GetDSNotes()
Row.OutputColumn = DSNotes.getValueStringForRow(Row.Column1, CInt(Row.Column2))
Do While Row.NextRow()
Row.OutputColumn = DSNotes.getValueStringForRow(Row.Column1, CInt(Row.Column2))
Loop
End Sub
End Class
Intellisence suggests the following method for populating the Row: Row.OutputColumn.AddBlobData(data() as byte). This is where I have come unstuck ... too noob...
August 5, 2009 at 7:22 pm
Why are using a script component? Is there more you are doing in the script? I think you can probably do what you need with the builtin components like data conversion and derived column.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 5, 2009 at 7:35 pm
Hi Jack
I have found my answer... which is as follows:
Imports System
Imports System.Data
Imports System.Collections
Imports System.Math
Imports System.Text
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim DSNotes As New GetDSNotes()
Dim strNTENotes As String
strNTENotes = DSNotes.getValueStringForRow(Row.Column1, CInt(Row.Column2))
Row.OutputColumn.AddBlobData(Text.Encoding.GetEncoding(1252).GetBytes(strNTENotes))
Do While Row.NextRow()
strNTENotes = DSNotes.getValueStringForRow(Row.Column1, CInt(Row.Column2))
Row.OutpuColumn.AddBlobData(Text.Encoding.GetEncoding(1252).GetBytes(strNTENotes))
Loop
End Sub
End Class
Obvious if you (I) know where to look.
Thanks for your time.
Cheers
Wee!
November 20, 2014 at 11:53 am
Thanks very much, this helped me a ton.
January 28, 2016 at 8:35 am
I grabbed your solution, translate it in C#, works like a charm.
August 8, 2016 at 8:42 am
Hi,
We are also facing same issue.'unable to convert DT_NText to TextStream'.We are using SSDTBI Tool (VS 2013)
Could you please let me know how you have added GetDSNotes() to get string value for row.
Thanks,
sanmati
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply