April 6, 2015 at 1:08 pm
I have an SSIS package that updates an Item table in a homegrown database from the item table in our SAP program. Please keep in mind that the primary key in both the source (SAP) and the destination (Homegrown db) tables are the PK, the item numbers never repeat.
SSIS Package Steps
1. Run a query to grab just the item data that is required from the SAP database.
2. Perform a data conversion to the data types used in the home grown database.
3. Do a look up it match the data based on the Item number.
4. Use a conditional split to either Update an existing row / ignore. OR Insert a new row if the item number does not already exist.
So everything works until it tries to insert the new rows of data! I get the following error and cannot figure out how to get around it. I have attached a screenshot of my package when debugging and then the errors I receive in a text file. I have done some research on the "The value violated the integrity constraints for the column" error and unfortunately I cannot remove the primary key every time I need to update. This SSIS package will be running once a day to update the homegrown database table with the newly created items in SAP.
Any help would be appreciated!
April 6, 2015 at 2:25 pm
Not sure exactly how you are doing your lookups, but the Lookup transformation is case sensitive. It is possible that you are getting the same items but with different case, and SSIS will see those as new rows.
I'd start with that...please post details of the lookup and other transformations if the problem still persists.
April 7, 2015 at 9:59 am
Martin,
I was actually able to get it working by changing the Data access mode in the OLE DB Destination Editor to "Table or view" from "Table or view - fast load". I don't understand why it would work all of a sudden after that change. I have attached a couple of screenshots from my look up, can you tell me if this looks right? The package does work, I just want to make sure I have it setup correctly. Maybe there's a better way to do this?
Thanks!
April 9, 2015 at 9:22 am
Nothing jumps out at me as being incorrect. Personally, I like to stage the data and perform lookups with T-SQL...usually staying away from the Lookup Transformation. It is a personal choice/preference though.
The only different with the "Fast Load" option is that it uses a Bulk Insert essentially to insert the data in batches. If your destination wasn't an SQL Server, that could be the reason for the incompatibility with the Bulk Insert operation.
Performance will be slower without using "Fast Load", but a working solution is the first goal.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply