SSIS cast to DT_WSTR not casting, or so it seems

Question

I'm creating an SSIS 2008 package that reads data from an ASCII flat file source and writes it to a SQL Server 2008 database. BIDS was complaining about the implicit cast between unicode and non-unicode data types, so I used a derived column tool to make the cast. This is what it looks like:

Derived Column Name  |  Derived Column  |  Expression          |  Data Type
AccountName2         |  Replace 'AName' | (DT_WSTR,100) AName  |  string [DT_STR]

I'm still getting the same error:

Validation error. InsertAccountRecords: InsertAccountRecords: Columns "AccountName2" and "AName" cannot convert between unicode and non-unicode string data types.

The error is showing up in the SQL Server Destination, which leads me to believe the problem is the Data Type in the derived column. I'm casting it from DT_STR to DT_WSTR, but the Data Type is still nominally DT_STR according to this, no?

I've Googled around, and I can't seem to find any good answers to this question. Can anyone provide any guidance?

Edit: Yup. Looking at the Data Viewer downstream of the Derived Columns, AccountName2 is still coming up as DT_STR. Why isn't the cast casting?

Solution

The documentation notes that the new data type is set correctly only when you choose to add a new column. You need to select "add as new column" in the Derived Column drop-down.

Or just use the Data Conversion transformation, which is probably easier if changing the data type is the only transformation you're making.

Comments

[Chris]

Yep. That was 100% the case, and adding the Data Converstion transformation made things much simpler.

[igorludi]

Still, I have to wonder, why, why, why !? Why should they choose NOT to set the data type correctly on replace...?

[Pondlife]

@igorludi I don't know, but SSIS is very conservative about handling data types, e.g. implicit conversion is not allowed. So my guess would be that the design decision was made that a single column can't have different data types at different places in the package. That would avoid problems with packages suddenly breaking because someone adds a new data type transformation and doesn't update the 'downstream' logic: by using a new column, you have to update the whole package. And it might make some metadata operations like lineage analysis easier. But I have no evidence for this guess.

[igorludi]

OK, seems reasonable enough. One thing thoough, I'm not so sure about implicit conversions. For instance, I have setup two data flows, using: AdoNet->AdoNet OledDb->OleDb using the same databases (and tables) for src and dest. Former automatically converts non-unicode to unicode string, and latter results in an error (and that is how I came here :)).