Post reply

Adding to a lookup table

  • January 19, 2010 at 9:21 am

    #219401

    Hi,

    I have an SSIS routine that has a typical OLE DB Source passing data to an OLE DB destination.

    However, my requirements have changed and I now need to check the source data (the OLE DB source is accessed via a stored procedure so the data I get from that is what I have to work with) for foreign keys that do not already exist in a lookup table in my destination database & if they don't exist add them before I break

    I'm in the slightly fortunate situation that my destination does not have enforced relationships so I can add to my destination table without worrying about foreign keys existing so I could add an after the fact Execute SQL Task to fix my database but I would rather resolve before inserting to avoid future problems if any decides to tidy the database structure.

    Any pointers appreciated because I cannot get my head round it for some reason.

    Hope my explanation is ok!

    Thanks

  • January 20, 2010 at 5:52 am

    #1105970

    I've figured it out now so in case anyone else needs to figure it out :

    1) Load data from source db

    2) lookup against destination DB lookup table

    3) Redirect error rows from (2) to a multicast

    (a) multicast output 1 goes to OLE DB to add to the lookup table

    (b) multicast output 2 goes to (4) below

    4) Add a union all to join multicast output (2) and success rows from (2)

    Job done.

    There is a caveat. If your source data includes lookup values and potential details,eg my source contains supplier id and supplier name - I want to check that the supplier exists, if it doesn't I'm going to add both fields to my supplier lookup, however, if it does exist I want to replace the supplier name I currently have with the one on the lookup table because my destination DB holds supplier name (3rd party product so not my fault!) - there is a problem here in that the lookup replaces the supplier name with null if it doesn't find a record overwriting the value being brought in. So the solution is to do a first lookup to identify existence and on the successful rows add a second lookup to replace the values (so that would be 2(a) above).

    I've reached a point where SSIS becomes spaghetti.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply