SSIS: column cannot convert between unicode and non-unicode

Not sure if this is still a problem but I found this simple solution:

  1. Right-Click Ole DB Source
  2. Select ‘Edit’
  3. Select Input and Output Properties Tab
  4. Under “Inputs and Outputs”, Expand “Ole DB Source Output” External Columns and Output Columns
  5. In Output columns, select offending field, on the right-hand panel ensure Data Type Property matches that of the field in External Columns properties

Hope this was clear and easy to follow

The problem of converting from any non-unicode source to a unicode SQL Server table can be solved by:

  • add a Data Conversion transformation step to your Data Flow
  • open the Data Conversion and select Unicode for each data type that applies
  • take note of the Output Alias of each applicable column (they are named Copy Of [original column name] by default)
  • now, in the Destination step, click on Mappings
  • change all of your input mappings to come from the aliased columns in the previous step (this is the step that is easily overlooked and will leave you wondering why you are still getting the same errors)
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s