Sql-server – SSIS database source imports as BLOB incorrectly

datatypessql serverssisssis-2016

Friends,

I am a beginner user to SSIS (Microsoft SQL Server Integration Services). While I have experience coding in other languages (Python, MATLAB, R, and others), I have never experienced a language whose data types are so confusing to me.

I have uploaded data to a database that I created using a Data Flow object. The data type of the column in SSIS is a Unicode string [DT_WSTR] and the data type of the column in the database is ntext.

After uploading the data to the database, I am retrieving it from that database in a new Data Flow object. However, when it loads the data from the database into SSIS, it is now a Binary Large Object Block (BLOB). It seems near impossible for me to be able to do any sort of transformation to the column with it as a BLOB object.

Is there any way to either convert the BLOB object to DT_WSTR? Or to a useable datatype?

Any help is appreciated!

Best Answer

The table definition that it appears you let SSIS create defined a column as ntext. NTEXT, besides being deprecated, is a large object type so it's reasonable that when you extract data from that source, SSIS will simply call it BLOB because it doesn't matter to the data flow engine why it's large, it just is.

You can either fix your table to be a non LOB type and then update the loader process to account for it.

Approximately

ALTER TABLE dbo.MyTable ALTER COLUMN Foo nvarchar(4000);

Or you can perform an explicit cast when you extract data from the table to make it into a "regular" string.

Approximately

SELECT CAST(T.Foo AS nvarchar(4000)) AS Foo FROM dbo.MyTable AS T;
Related Question