October 18, 2010 at 8:33 am
I did read your first post. You didn't say anything about "passes" in that post. Hence the reason I asked the question.
As far as your primary key violation goes... Is the GUID in the Excel file or are you defaulting on that?
October 18, 2010 at 8:42 am
All the data under Error Output is rejected, on the second pass.
The Error Output data is a result of re-directing the erroneous data, on a fail condition.
October 18, 2010 at 11:46 am
Brandie Tarvin (10/18/2010)
As far as your primary key violation goes... Is the GUID in the Excel file or are you defaulting on that?
Please answer this question.
October 19, 2010 at 1:47 am
Hi,
Yes, the primary key GUIDs are in the Excel file.
Each time the insert is attempted a new random set of GUIDs are generated using NewID(). Each new set is then rejected, as can be seen in the Error Output section of the Excel file. This happens consistently each time.
The primary key column is called 'PK_LiveItemInstanceID' and the column 'newGuid' is mapped to this column in the SSIS package. The insert attempt fails with the message:
Violation of PRIMARY KEY constraint 'PK_LiveDataInstance'. Cannot insert duplicate key in object 'dbo.LiveItemInstance'.
The constraint 'PK_LiveDataInstance' refers to column 'PK_LiveItemInstanceID' which is the primary key for that particular data table.
October 19, 2010 at 4:31 am
The only thing I can think of at this point is either that the GUIDs already exist in the table that you're trying to load the Excel sheet into or that the Excel really does have duplicates in it.
I'm leaning toward the former. The best way to test is to upload your Excel into a staging table or a brand new table. Then do an inner join between the staging table and the final destination on the GUID. If you come up with records, then you know that the GUID is duplicated for sure.
October 25, 2010 at 4:25 am
I have fixed this problem.
There were duplicate keys being inserted into the primary key column, but the data was not making it into the error output. The duplicate keys were being inserted at the same time, so this meant none of the erroneous data could be seen.
The fix was to add a collate property to this query, as can be seen here:
SELECT MinValue, MaxValue, UnitsMnemonic, Mnemonic, PK_TextID, UnitsPK_TextID, NEWID() AS newGuid
FROM (
SELECT DISTINCT MinValue, MaxValue, UnitsMnemonic, [Import.LiveDataExcel].Mnemonic, TextId.PK_TextID AS PK_TextID, UnitsTextId.PK_TextID AS UnitsPK_TextID
FROM [Import.LiveDataExcel]
LEFT OUTER JOIN TextId ON [Import.LiveDataExcel].Mnemonic = TextId.Mnemonic
LEFT OUTER JOIN TextId AS UnitsTextId ON [Import.LiveDataExcel].UnitsMnemonic = UnitsTextId.Mnemonic Collate Latin1_General_CS_AS
WHERE MinValue IS NOT NULL
AND MaxValue IS NOT NULL
AND [Import.LiveDataExcel].Mnemonic IS NOT NULL
AND Description IS NOT NULL) tblNewLiveItems
It was hard to trace as the erroneous data was inserted quite some distance from where the packaged went red when executed.
Never mind it was good experience, and I have learnt some valuable lessons.
Thanks to all who tried to help me.
October 25, 2010 at 5:34 am
You're very welcome. We're glad you figured it out.
Thanks for posting your solution. That will help other people in the future.
January 25, 2013 at 3:02 am
January 25, 2013 at 3:18 am
paklich (1/25/2013)
http://support.microsoft.com/?id=972498
This was a collation issue, not a low memory issue.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply