latest Post

Converting Blob Column to string in SSIS Script Component.

Recently I found myself needing to work with blob columns when transforming CRM data with SSIS. Specifically I was wanting to query and interrogate "Plugin Trace Logs" and output a daily email of any plugin errors to help the team become more proactive rather than reactive. 

Using the below fetch query and the Kingswaysoft CRM Source component.  I noticed the multi line text fields mapped to the SQL data type "ntext".

When reading this value from the data flow buffer in a SSIS Script component the "ntext" column actually returns the data as a "BlobColumn" and  in order to get the text string of the data in the column I had to use the GetBlobData() method in the DTS.Pipeline namespace.

After a bit of investigating I was able to convert the blob text to a string to be able to read the returned value and manipulate the data how I wanted. 

 The below snippet is the code I used.  Hopefully, this will help someone out.

Recommended Posts × +