Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Update column and pass new value to other dataflow Expand / Collapse
Author
Message
Posted Monday, June 3, 2013 1:56 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, September 21, 2014 5:35 PM
Points: 67, Visits: 572
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
12345 James 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
00001 James 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
00001 James 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!!!!
Post #1459449
Posted Monday, June 3, 2013 3:35 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 12:35 PM
Points: 204, Visits: 963
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.



Alex Suprun
Post #1459483
Posted Tuesday, June 4, 2013 1:03 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, September 21, 2014 5:35 PM
Points: 67, Visits: 572
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
Post #1459567
Posted Tuesday, June 4, 2013 9:36 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 12:35 PM
Points: 204, Visits: 963
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.



Alex Suprun
Post #1459837
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse