Changing Collation In SSIS

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Integration Services » Changing Collation In SSIS

Changing Collation In SSIS

Rate Topic

Display Mode

Topic Options

Author

Message

waqqas.zia

Posted Wednesday, January 16, 2013 10:47 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, April 11, 2013 9:49 AM
Points: 19, Visits: 75

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

Post Attachments

Capture.PNG (1 view, 13.18 KB)

Post #1407999

Phil Parkin

Posted Thursday, January 17, 2013 5:47 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:39 AM
Points: 4,328, Visits: 9,667

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.

____________________________________________________________________________________________

Help us to help you. For better, quicker and more focused answers to your questions, consider following the advice in this link:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #1408368

« Prev Topic | Next Topic »

Permissions