How can I convert "SQL_Latin1_ General" Collate Using an SSIS Package?

sql sql-server-2008 ssis collate
TJH picture TJH · Nov 1, 2012 · Viewed 9.4k times · Source

We've just noticed 2 of our servers have been set up using SQL_Latin1_General_CP1_CI_AS and Latin1_General_CI_AS.

I've created a script which compares two sources of data again each other (as it's a simple SELECT statement I haven't included it, it uses the same columns but from different sources) when I tried to union them together I received the collate error.

Msg 468, Level 16, State 9, Line 2 Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the UNION operation.

One of the sources is a table which is being populated by an SSIS package, is there a way of adapting the SSIS project to convert the "SQL_Latin1_General_CP1_CI_AS" format to "Latin1_General_CI_AS" before it's sent to the destination table?

Thanks!

Answer

Rednaxel picture Rednaxel · Nov 1, 2012

You can use the sentence COLLATE Latin1_General_CI_AS after each column in the table that use SQL_Latin1_General_CP1_CI_AS like this.

SELECT COLUMN_A COLLATE Latin1_General_CI_AS AS COL1 
FROM TABLE

Related questions

Select Query for the fixed length

I have an table called customer where i am selecting few columns and making its fixed length,where i need to send the values to SSIS packages for an fixed length output and is written in a text file customerID:10 …

Read More
SSIS OLEDB destination with SQL command

I have a simple SSIS package with a data flow task with ADO.NET source & destination: Normally, I would just select 'table or view' on the OLEDB destination, but although the connection is correct it's not showing me any …

Read More
SSIS Count of Object Variable?

I am using SSIS in BIDS 2008 and I am trying to get a count of an Object Variable containing a list of files. Any idea on how to do this? Thanks!

Read More