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