June 3, 2013 at 1:56 pm
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 First Last Address Postcode
12345James Peterson 5 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 First Last Address Postcode
00001James Peterson 5 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 First Last Address Postcode
00001James Peterson 5 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 3, 2013 at 3:35 pm
From your explanation it look's like that your database design is very poor.
If you still want to perform the task, then I would suggest you to dump everything to staging tables and then use MERGE statement.
June 4, 2013 at 1:03 am
Your correct about the database design but I was asked to use ssis (so the bad design continues) so any guidance on how to go about it in ssis would be helpful.
Thanks
June 4, 2013 at 9:36 am
As I said import everything into Staging table using Data Flow and then run MERGE by Execute SQL Task.
Or if by ssis you mean specifically Data Flow then use OLE DB Command and Lookup components.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply