June 3, 2013 at 2:30 am
Hi All,
I created a SSIS package that check first whether the ID in the file being imported matches to an existing record in the database but I need to provide a second layer of checking based on Name and Address (lookup)because we also get sent existing clients under new ID’s that creates duplicates’ in the database.
See example below –
Flat file imports sample row below—
ID FirstLastAddressPostcode
12345James Peterson5 Green SW14
I use lookup to check that James does not already exist in the database under a different ID number by doing a lookup against the database by using Name, address and postcode to lookup –
And If James is already under another ID number (like below) I want to update the new ID number with OLD number in the destination table on the database.
Exist in database
ID FirstLastAddressPostcode
00001James Peterson5 Green SW14
I now want the import file to use the database ID to import the data into the destination table
Imported into the destination table in sql server
ID FirstLastAddressPostcode
00001James Peterson5 Green SW14
I also want to pass the change ID number into the other dataflow import files they also contain the same ID (see picture) with Old ID number so they can also update their destination tables with 00001 not 12345 and audit the changes made to the ID’s.
I have been struck on this problem for a while! Any help Greatly Appreciated!!!!
June 4, 2013 at 7:56 am
Perhaps you should try designing a staging table in your database that can store the initial data and then you can run an "Execute SQL Task" on the table that does the checks that you wanted. I have a data flow that adds an ID to a staging table before the staging table data is dumped into the main table. If you place the Execute SQL Task just before your data flow tasks, or replace the data flow tasks with the SQL tasks then you may have a better chance of solving this problem.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply