SSIS getting wrong column type with OLEDB connector

Question

Halfway through a SSIS project certain table fields changed from char(30) to nvarchar(30)

However, when running the SSIS packages, an error stating cannot convert from unicode to non-unicode appears. I am trying to transfer data directly from a database source to its destination.

Both connections use the same database schema, so there should be no conversion.

When checking the external column data type it shows D_STR, which is not the case anymore.

I tried deleting both source and destination in hope that it would clean any sort of cached data, but it did not work.

Any ideas?

Answer 1

Sounds to me like the metadata in your data flow task is cached and needs to be refreshed to reflect the new type.

Open the source, go to columns, and uncheck the column, then check the column. Click ok. The metadata should refresh now.

Answer 2

nvarchar and nchar are unicode. Conversely, varchar and char are non-unicode.

http://msdn.microsoft.com/en-us/library/ms187752.aspx

As a result if you are moving data from one data type to another you will have to perform some additional transformation (CAST or CONVERT). The other option is to look at your adapters such that char will use SSIS DataType of DT-STR and nvarchar will use SSIS DataType DT-WSTR

http://msdn.microsoft.com/en-us/library/ms141036.aspx

Without knowing how your packages work I cannot be much more specific but hopefully this will get you going.