SSIS ISNULL to empty string

Question

So I am currently working on a migration from an old Advantage database server to SQL 2005 using SSIS 2008. One of the columns in the old Advantage database is a MEMO type. By default this translates to a DT_TEXT column. Well in the new database I do not need this large of field, but can limit it to something such as VARCHAR(50). I successfully set up a derived column transformation to convert this with the following expression:

(DT_STR,50,1252)[ColumnName]

Now I want to go a step further and replace all NULL values with an empty string. This would seem easy enough using an ISNULL([ColumnName])?"":(DT_STR,50,1252)[ColumnName] expression, but the problem is that the OLE DB Destination contains the following error

Cannot convert between unicode and non-unicode strings...

So apparently the whole ISNULL expression converts the data type to Unicode string [DT-WSTR]. I have tried a variety of casts upon the whole expression or different parts, but I cannot get the data type to match what I need it.

First, is it possible to convert the DT_TEXT type directly to unicode? From what I can tell, the casts don't work that way. If not, is there a way to get an expression to work so that NULL values get converted to empty strings?

Thank you for all your help!

Fishbolt

Answer 1

I figured something out that works. It may not be the best solution, but it will work for my situation.

From my OLE DB source I first did a Derived Column. This I used the ISNULL which ended up converting it to a DT_WSTR unicode type. although I could not get any casts to get it back to the type required, I then added a Data Conversion transformation in-between the Derived Column and the OLE DB Destination. This would take the input string and convert it back to a DT_STR. This all feels a little annoying converting so many times, but the column does not contain any funky information that I should have to worry about, so I suppose it will work.

Thanks for all those who pondered the solution, and if you find some awesome way to tackle it, I would be more than interested.

Fishbolt

Answer 2

Give this a try in your derived column.

(DT_STR,50,1252) (ISNULL(ColumnName) ? "" : (DT_STR,50,1252) ColumnName)

It includes an additional type cast with the Conditional (?:) in parentheses to ensure the desired processing sequence. I think your original expression was implicitly casting to DT_WSTR because the "" defaults to DT_WSTR. With this new version, you force the cast to DT_STR after the expression is evaluated.

Tristantristas

I did give this a try in my many casting attempts. I even went as far as to do this (DT_STR,50,1252)(ISNULL(ColumnName)?(DT_STR,1,1252)"":(DT_STR,50,1252)ColumnName ... still with no luck. I appreciate the idea, and unless I did something wrong with my casting, it ended up not working – Fishbolt