Ultimately, what I am trying to do is create an SSIS package in the QA environment for another team which truncates the destination tables and inserts from the source tables. However, on one table, ITEMMAST
, I am receiving an error due to a unique constraint violation when trying to copy the data using a data flow task.
What is odd is that a SELECT...INSERT
using a linked server succeeds! However, I can't use linked servers because this other team wants to be able to specify the environment for the source so I have to parameterize the connection strings.
Here is the definition of the unique constraint:
ALTER TABLE [dbo].[ITEMMAST]
ADD CONSTRAINT [ITESET1]
PRIMARY KEY CLUSTERED
(
[ITEM_GROUP] ASC,
[ITEM] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90)
ON [PRIMARY];
I've tried dropping the primary key constraint, copying the data, and then recreating the primary key constraint but that also produces an error.
Msg 1505, Level 16, State 1, Line 46 The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.ITEMMAST' and the index name 'ITESET1'. The duplicate key value is (SALO , ). Msg 1750, Level 16, State 0, Line 46 Could not create constraint. See previous errors. The statement has been terminated.
A developer gave me the following query to run which helped me identify the culprits, one row with 32 spaces in the ITEM
column and another row with the ASCII character of 0, a null character, in the ITEM
column.
SELECT TOP 10
patindex('%[^ !-~]%' COLLATE Latin1_General_BIN,ITEM) as [Position],
substring(ITEM,patindex('%[^ !-~]%' COLLATE Latin1_General_BIN,ITEM),1) as [InvalidCharacter],
ascii(substring(ITEM,patindex('%[^ !-~]%' COLLATE Latin1_General_BIN,ITEM),1)) as [ASCIICode]
,*
FROM Lawson.dbo.ITEMMAST
WHERE ITEM_GROUP = 'SALO'
Here is more information about the QA environment. These settings match our PROD environment so I'm unable to modify QA to make this work. I've thought about altering the unique constraint definition and changing the destination database collation.
Source Database Collation: Latin1_General_BIN
Collation for the ITEM
Column in the Source Table: Latin1_General_BIN
Destination Database Collation: SQL_Latin1_General_CP1_CI_AS
Collation for the ITEM
Column in the Destination Table: Latin1_General_BIN
I checked that the codepage for the Latin1_General_BIN
collation is 1252 which matches the codepage for the SQL_Latin1_General_CP1_CI_AS
collation.
Does anyone have any suggestions for other things that I can try? I'm new to SSIS so there might be a setting that I did not set correctly.
[UPDATE #1]
Screen shot showing the result of the following query from the source table.
SELECT TOP 2
CONVERT(VARBINARY(500), [ITEM])
,ITEM COLLATE SQL_Latin1_General_CP1_CI_AS AS [ITEM_Collated]
,*
FROM PROD.dbo.ITEMMAST
WHERE ITEM_GROUP = 'SALO'
CHAR(0)
before inserting the values, hence the duplicate. This is also why the Linked Server works: it maintains the collation since it knows of the destination column's collation. I don't work with SSIS (anymore, thankfully ;) so I am not entirely sure how to proceed. Do you have any options for specifying the collation of the operation / query / etc? Can you specify a property for 1 column?Latin1_General_BIN
but the data flow task still failed. I also tried changing the OLE DB Source to use a SQL command as the Data access mode instead of the name of a table or view and explicitly specifying the collation for theITEM
column but that didn't work either.SELECT ITEM_GROUP COLLATE Latin1_General_BIN AS ITEM_GROUP,ITEM COLLATE Latin1_General_BIN AS ITEM ... FROM ITEMMAST
CAST(ITEM AS CHAR(32)) COLLATE Latin1_General_BIN AS ITEM
I tried making theITEM
column nullable but then it can't be used as part of the unique constraint definition.@
name so that they get notified of the message, unless they are the one who posted the question or answer being commented on (in which case they always get notified). So I will use @JacobH to notify that person of your response above. (FYI: you can only use one@
reference in a comment!)