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
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:
Collation for the
ITEM Column in the Source Table:
Destination Database Collation:
Collation for the
ITEM Column in the Destination Table:
I checked that the codepage for the
Latin1_General_BIN collation is 1252 which matches the codepage for the
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.
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'