Problem With SSIS Error (Lookup and OLE DB Command)

I'm getting an error with an SSIS package which I'm not sure how to开发者_JAVA技巧 fix. I feel I will need to give a background as to what I am trying to do to help you understand:

I am using an OLE DB Source database (SRC DB) and the input from this is output to a Lookup (DEST DB). I am checking if a field in the SRC DB exists in the DEST DB. In my case the field I am checking is called the inkexID, which is an INT NOT NULL and is a composite primary key along with an ID field.

The lookup is checking if the inkexID which exists in the SRC DB exists in the DEST DB. If it doesn't exist it inserts it and 3 other fields into the DEST DB, if it does exist it just updates 1 field in the DEST DB.

My problem lies here: When it comes to the lookup, it checks the inkexID over the 2 tables and see's that there is no match, but the component then fails. It doesn't seem to send any error output (which is what is needed to call the initial insert OLE DB command.). If I set the error config to redirect rows or ignore failure, it then fails on the insert. It gives me an error about violating my database schema, even though the field I am trying to insert is 12345.

I removed the field as a primary key and set it to allow nulls, so when I ran it again I was surprised to see it is trying to insert a NULL into the field, even though the value in the SRC DB is 12345.

It seems that there is nothing output from the Lookup. Can anybody help as to why nothing would be coming out of the lookup???

Much appreciated, Cian

Use the SQL Server Profiler to run a trace on the server.

Start the trace, then execute the package. The profiler trace will capture all of the actual T-SQL Commands that are being sent to the database server. You can then copy each command into a query editor window, execute, and see the result set as well as look at the execution plan for tuning.

I have found out what the error was:

I had set up the inkexID field of SRC DB to lookup the inkexID from the DEST DB. I had however accidently set it up ticked and set the column from the SRC DB to replace the inkexID in DEST DB. I simply unticked it and simply set up the relationship.

RESULT!!! It now checks the inkexID and if it exists in the DEST DB will just update the lastUpdated time with the current time, otherwise it inserts the correct inkexID, the current time as dateAdded and dateUpdated and the statusID as active.

Yay!