Changing Collation In SSIS

• waqqas.zia

  SSC-Addicted

  Points: 462

  More actions

  January 16, 2013 at 10:47 am

  #144065

  Good Morning, Afternoon or Evening where ever you may be.

  Ive taken the plunge into SSIS after studying the Stairway to integration services set on this website and i absolutely love it!!! It makes transforming and manuipulating data SOOO Much easier.

  That being said i have run into a little problem...

  I have created a package which looks at an excel source and the inserts and updates into a table within my datawarehouse (see attched)

  The problem that i have is this table is required to communicate with another database in another part of the datawarehouse which is in Language:

  "SQL_Latin1_General_CP850_CI_AS"

  and when i insert this column into my database it appears as:

  "SQL_Latin1_General_CP1_CI_AS"

  and to top it all off i have found another Language:

  "Latin1_General_CI_AS"

  So as you know my next question is...How do i set up a conversion between the "Excel Source" and the "Lookup" Transformation?

  Forgive me for the non-technical language i have come from a mechanical engineering background so this is all new to me!!

  Also THanks in advance

• Phil Parkin

  SSC Guru

  Points: 247158

  More actions

  January 17, 2013 at 5:47 am

  #1578266

  After your Excel source, add a data conversion component.

  Use it to convert your problematical column to DT_STR. You will see at this point that you are able to specify the code page of the converted column (which will be added to your pipeline as a new column called 'Copy of xxxx' by default).

  Use this new column to do your lookup. Won't be blindingly fast, but should work.

  Alternatively, when creating your lookup, use a query to specify what is going to be 'looked up' and in that change the collation of the lookup column back to what Excel wants.

  ---