SSIS cast to DT_WSTR not casting, or so it seems

4
votes

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?

1
castingssis

1 Answers

8
votes

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.