Results 1 to 5 of 5

Thread: SQL 2005: SSIS: Script Component: Working with BLOB

  1. #1
    Join Date
    Jan 2008
    Posts
    3

    SQL 2005: SSIS: Script Component: Working with BLOB

    I have few tables that I need to export to an MS Access database each day to send off to the customer. In these tables I have a few columns that I need to strip all the HTML out of before they are put into Access since Access does not display the formatting correctly. Some of the fields are varchar and some are text, but all of the fields that are varchar are over 255 in size, so this forces me to use Access' Memo type for both.

    In SQL 2000 I used the ActiveX Script to modify these fields, stripping out the HTML, and it gave me no complaints. In SQL 2005, I am adding a Script Component into the Data Flow before the Destination and adding the script in there. So far so good. The problem arises when I try to retrieve and update the data in these fields because they are treated as BLOB data. I believe what I need to do is to retrieve the Byte array from the row using the GetBlobData(), then convert that to a string, strip out the HTML, convert back to a Byte array, then clear the original value using the ResetBlobData(), and then update the field using the AddBlobData. I think?

    I am not even sure that my code below is converting the byte array into a string correctly. If I throw a Messagebox in there to see what it has for the string, it just gives me the first character in the field. But even ignoring that, the package will not execute and I am stumped.

    The error I now get is:
    Array cannot be null.
    Parameter name: bytes

    ''-----------------------------------------------------
    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

    Dim b As Byte()
    If (Row.ProjectDescription.Length > 0) And (Not
    (Row.ProjectDescription_IsNull)) Then
    b = Row.ProjectDescription.GetBlobData(0,
    CInt(Row.ProjectDescription.Length))
    End If

    Dim str As String
    Dim enc As New System.Text.ASCIIEncoding()
    str = enc.GetString(b)
    str = RemoveHTML(str)

    b = enc.GetBytes(str)

    Row.ProjectDescription.ResetBlobData()

    If b.Length > 0 Then
    Row.ProjectDescription.AddBlobData(b)
    End If

    End Sub
    ''-----------------------------------------------------

  2. #2
    Join Date
    Jan 2008
    Posts
    3
    Okay, so nobody knows what to do here. Me neither. But can anyone offer another solution to what I am trying to do?

  3. #3
    Join Date
    Sep 2002
    Posts
    5,938
    Check www.sqlis.com, may have something there.

  4. #4
    Join Date
    Jan 2008
    Posts
    3
    Thanks for the reference to www.sqlis.com! They helped me out and I am happy to share the answer with the world here.

    Code:
            Dim intBlobLen As Integer = Convert.ToInt32(Row.ProjectDescription.Length)
            Dim intFinish As Integer = intBlobLen - 1
    
            Dim bytBlob(intFinish) As Byte
            bytBlob = Row.ProjectDescription.GetBlobData(0, intFinish)
    
            Dim strBlob As String = System.Text.Encoding.Unicode.GetString(bytBlob)
            strBlob = RemoveHTML(strBlob)
    
            If strBlob.Length > 0 Then
                Row.ProjectDescription.ResetBlobData()
                Row.ProjectDescription.AddBlobData(System.Text.Encoding.Unicode.GetBytes(strBlob))
            End If

  5. #5
    Join Date
    Jan 2012
    Posts
    1
    Thanks for sharing your answer user 0010! You made my day! There was one slight error in the code that needed to be fixed. The GetBlobData function was chopping one byte off the end of the BLOB field because the intFinish variable is set to intBlobLen - 1 but the second parameter of GetBlobData is the number of bytes of data to retrieve. So, I added 1 to intFinish in the GetBlobData function call.

    Code:
        Dim intBlobLen As Integer = Convert.ToInt32(Row.ProjectDescription.Length)
        Dim intFinish As Integer = intBlobLen - 1
    
        Dim bytBlob(intFinish) As Byte
        bytBlob = Row.ProjectDescription.GetBlobData(0, intFinish + 1)
    
        Dim strBlob As String = System.Text.Encoding.Unicode.GetString(bytBlob)
        strBlob = RemoveHTML(strBlob)
    
        If strBlob.Length > 0 Then
            Row.ProjectDescription.ResetBlobData()
            Row.ProjectDescription.AddBlobData(System.Text.Encoding.Unicode.GetBytes(strBlob))
        End If
    If you wanted to simplify the code a bit, you could get rid of the intFinish variable and write it like:

    Code:
        Dim intBlobLen As Integer = Convert.ToInt32(Row.ProjectDescription.Length)
    
        Dim bytBlob(intBlobLen - 1) As Byte
        bytBlob = Row.ProjectDescription.GetBlobData(0, intBlobLen)
    
        Dim strBlob As String = System.Text.Encoding.Unicode.GetString(bytBlob)
        strBlob = RemoveHTML(strBlob)
    
        If strBlob.Length > 0 Then
            Row.ProjectDescription.ResetBlobData()
            Row.ProjectDescription.AddBlobData(System.Text.Encoding.Unicode.GetBytes(strBlob))
        End If
    Or, if you really wanted to condense the code and get rid of all the variables besides strBlob you could write it like:

    Code:
        Dim strBlob As String = System.Text.Encoding.Unicode.GetString(Row.ProjectDescription.GetBlobData(0, Convert.ToInt32(Row.ProjectDescription.Length)))
        strBlob = RemoveHTML(strBlob)
    
        If strBlob.Length > 0 Then
            Row.ProjectDescription.ResetBlobData()
            Row.ProjectDescription.AddBlobData(System.Text.Encoding.Unicode.GetBytes(strBlob))
        End If

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •