SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Update column and pass new value to other dataflow


Update column and pass new value to other dataflow

Author
Message
azdeji
azdeji
SSC-Enthusiastic
SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)

Group: General Forum Members
Points: 186 Visits: 682
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!!!!
Alexander Suprun
Alexander Suprun
Mr or Mrs. 500
Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)

Group: General Forum Members
Points: 535 Visits: 1516
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
azdeji
azdeji
SSC-Enthusiastic
SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)

Group: General Forum Members
Points: 186 Visits: 682
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
Alexander Suprun
Alexander Suprun
Mr or Mrs. 500
Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)

Group: General Forum Members
Points: 535 Visits: 1516
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search