SSIS how to update while migrating?

Question

SSIS how to update while migrating?

winseelan

SSC Rookie

Points: 33
More actions
October 4, 2011 at 2:43 am

#150599

hi there,
i have two tables
MASTER AND CHILD
MASTER has the following columns master_id, master_name
CHILD has the following columns child_id, master_id and child_name
so, child table is referring master table master_id column.
i created an SSIS package to copy my data from Db2 to MsSql Server. I scheduled this pack every 5 minutes its working fine only.
but, at some situation the same master_id data will come from db2(that means the same data exist on sql server) so, this time i would like to
update the record but i dont see any option while doing import job.
experts please help me to resolve this issue.

Viewing 10 posts - 1 through 9 (of 9 total)

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

rgtft Hall of Fame Points: 3518 More actions · Answer 1

winseelan (10/4/2011)
hi there,
i have two tables
MASTER AND CHILD
MASTER has the following columns master_id, master_name
CHILD has the following columns child_id, master_id and child_name
so, child table is referring master table master_id column.
i created an SSIS package to copy my data from Db2 to MsSql Server. I scheduled this pack every 5 minutes its working fine only.
but, at some situation the same master_id data will come from db2(that means the same data exist on sql server) so, this time i would like to
update the record but i dont see any option while doing import job.

Just the plain import/export wizard won't do this for you. You need to modify the package created by the wizard and add a lookup component. If the record is found, branch down an update path otherwise branch to your OLE destination (insert) path.

HTH,

Rob

winseelan SSC Rookie Points: 33 More actions · Answer 2

rgtft (10/4/2011)
winseelan (10/4/2011)
hi there,
i have two tables
MASTER AND CHILD
MASTER has the following columns master_id, master_name
CHILD has the following columns child_id, master_id and child_name
so, child table is referring master table master_id column.
i created an SSIS package to copy my data from Db2 to MsSql Server. I scheduled this pack every 5 minutes its working fine only.
but, at some situation the same master_id data will come from db2(that means the same data exist on sql server) so, this time i would like to
update the record but i dont see any option while doing import job.
Just the plain import/export wizard won't do this for you. You need to modify the package created by the wizard and add a lookup component. If the record is found, branch down an update path otherwise branch to your OLE destination (insert) path.
HTH,
Rob

Wow!! could you please guide me how to do this?

rgtft Hall of Fame Points: 3518 More actions · Answer 3

You've got to open your .dtsx package in BIDS to do the modifications. It sounds like you're not familiar with SSIS. Take a look at Andy Leonard's intro to SSIS stairway series here on SSC: http://www.sqlservercentral.com/stairway/72494/

winseelan SSC Rookie Points: 33 More actions · Answer 4

rgtft (10/4/2011)
You've got to open your .dtsx package in BIDS to do the modifications. It sounds like you're not familiar with SSIS. Take a look at Andy Leonard's intro to SSIS stairway series here on SSC: http://www.sqlservercentral.com/stairway/72494/

thanks for your response. i lernt that but there is a topci about how to update the records but there is no heyperlink. so, my exact requirement wasnt completed.

rgtft Hall of Fame Points: 3518 More actions · Answer 5

winseelan (10/5/2011)
rgtft (10/4/2011)
You've got to open your .dtsx package in BIDS to do the modifications. It sounds like you're not familiar with SSIS. Take a look at Andy Leonard's intro to SSIS stairway series here on SSC: http://www.sqlservercentral.com/stairway/72494/
thanks for your response. i lernt that but there is a topci about how to update the records but there is no heyperlink. so, my exact requirement wasnt completed.

Google/Bing the OLE DB Command transformation. You can write an update stored proc and call it from the OLE DB Command transformation.

winseelan SSC Rookie Points: 33 More actions · Answer 6

rgtft (10/6/2011)
winseelan (10/5/2011)
rgtft (10/4/2011)
You've got to open your .dtsx package in BIDS to do the modifications. It sounds like you're not familiar with SSIS. Take a look at Andy Leonard's intro to SSIS stairway series here on SSC: http://www.sqlservercentral.com/stairway/72494/
thanks for your response. i lernt that but there is a topci about how to update the records but there is no heyperlink. so, my exact requirement wasnt completed.
Google/Bing the OLE DB Command transformation. You can write an update stored proc and call it from the OLE DB Command transformation.

thanks for your response.

im using SSIS conditional split for new rows and changed rows. for changed rows im using below condition like below

([CODE1] != [dest_CODE1] ) || ([NUMBER1] != [dest_NUMBER1]) .........etc.,

but im getting followin error

on "output "changed rows" (1081)" evaluated to NULL, but the "component "Conditional Split" (1017)" requires a Boolean results. Modify the error row disposition on the output to treat this result as False (Ignore Failure) or to redirect this row to the error output (Redirect Row). The expression results must be Boolean for a Conditional Split. A NULL expression result is an error.

so, i changed output error log as Ignore Failure and run this time it was run perfectly but the data wasnt added.

this is my flow, experts please help me..

rgtft Hall of Fame Points: 3518 More actions · Answer 7

One (or more) of your fields in the conditional split expression are Null. Put an ISNULL() in there to handle this case. That should fix your problem.

Rob

winseelan SSC Rookie Points: 33 More actions · Answer 8

hi now i changed the flow like this

and this is my condition

now im passing 18 records to destination(those records already in destination) now this time the records should be update but instead all are going to insert operation. may i know how to resolve this issue?

rgtft Hall of Fame Points: 3518 More actions · Answer 9

Your condition doesn't look correct; the ISNULL() returns back true or false. You want something like:

!(ISNULL(field1)) && !(ISNULL(field2) && field1 == field2