Post reply

NOOB Error 30311: Value of type 'String' cannot be converted to 'Microsoft.SqlServer.Dts.Pipeline.BlobColumn'

  • August 4, 2009 at 5:14 pm

    #137108

    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

    #1034818

    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

    #1035138

    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

    #1035151

    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

    #1035152

    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

    #1759936

    Thanks very much, this helped me a ton.

  • January 28, 2016 at 8:35 am

    #1854977

    I grabbed your solution, translate it in C#, works like a charm.

  • August 8, 2016 at 8:42 am

    #1894357

    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