# Database Discussions > Microsoft SQL Server 2005 >  SQL 2005: SSIS: Script Component: Working with BLOB

## 0010

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
''-----------------------------------------------------

----------


## 0010

Okay, so nobody knows what to do here.  Me neither.  But can anyone offer another solution to what I am trying to do?

----------


## rmiao

Check www.sqlis.com, may have something there.

----------


## 0010

Thanks for the reference to www.sqlis.com!  They helped me out and I am happy to share the answer with the world here.



```
        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
```

----------


## greg.bakker

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.



```
    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:



```
    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:



```
    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
```

----------

