OLE DB to get BlobColumn Data in SSIS Dataflow.

Question

Hello All,

When I use ADO.net source in DataFlow to read Blob Column and pass it to Script Component to do further validations - need script compoment to do further validations on each column to generate master / child error records master (for each row) and child (for each error column). This works fine.

As I need to parameterize my source, I can't use ADO.net and instead need to use the OLEDB Source which supports parameters. When I use this OLEDB source, the script component doesnt recognise the BLOB data being passed by OLEDB source. It reports datatype problems i.e., convering nonunicode to unicode.

How can this be done.

Regards

Answer 1

Can you confirm what your source database is (SQL Server, Oracle, etc).

I had the same problem using the 'Oracle OLEDB provider for Oracle' data source. The provider seems to convert every varcahr into an nvarchar. I solved this by adding a 'data conversion' component, and explicitly converting all nvarchar columns to varchar here.

The new columns are incuded in the output of this compnent, so you can link them to the fields on your spreadsheet.