1

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!

1 Answer 1

2

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;
2
  • Thank you! That solved the data type issue, however now I have a new problem. Now I am attempting to perform a lookup within my data flow. Within the "columns" page of the Lookup Transformation Editor, it allows me to join the columns that I need (because now the data types are the same thanks to your help). However, it is saying that there are 0 matches. Right now almost all of them should match (I've compared them in excel). What am I doing wrong? Any help is appreciated!
    – Jonathan
    Mar 30, 2017 at 17:36
  • Sounds like a new question (and shakes magic 8 ball the answer will be case sensitivity or trailing space)
    – billinkc
    Mar 30, 2017 at 19:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.