February 19, 2015 at 9:19 am
I have a SSIS pkg that gets data from SQL and do data conversion and Insert into OLE db AS400 destination, There is a flag column in SQL table , that has to be updated to true, once the records are inserted in AS400 how do i do that in SSIS
SQL oledb ---------> dataConversion ---------------> AS400 OLE db Destination
|
update SQL table Flag column<---------------------------------|
Any help appreciated.. thank you
February 19, 2015 at 9:50 am
I would simply put an Execute SQL Task after the data flow that will update the source tabel with an UPDATE statement.
Make sure to use an OnSuccess precedence constraint (the green arrow) between the data flow and the Execute SQL Task.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 19, 2015 at 10:05 am
Hi
Thanks but actually.. what i'm trying to do is after the destination got inserted successfully.. and my destination has only red arrow that gets error
SQL oledb ------> dataConversion 10 rows-----> AS400 OLE db Destination--->(10 rows inserted successfully)-->update 10 rows flag in SQL
SQL oledb ----> dataConversion 10 rows -----> AS400 OLE db Destination--->(5 rows inserted successfully)-->update 5 rows flag in SQL
Any other workarounds ? any help appreciated thank you
February 19, 2015 at 11:07 am
bharathi.kannan (2/19/2015)
HiThanks but actually.. what i'm trying to do is after the destination got inserted successfully.. and my destination has only red arrow that gets error
SQL oledb ------> dataConversion 10 rows-----> AS400 OLE db Destination--->(10 rows inserted successfully)-->update 10 rows flag in SQL
SQL oledb ----> dataConversion 10 rows -----> AS400 OLE db Destination--->(5 rows inserted successfully)-->update 5 rows flag in SQL
Any other workarounds ? any help appreciated thank you
Hmmm. This means you have to insert rows row by row (otherwise you won't know which row has succeeded or not) or otherwise the transaction will roll everything back. This means your AS400 OLE DB Destination has to support this. Using the red arrow, you could record the failed records to an error table. After the data flow, you can update your source table and set the flag for all records that are not found in the error table.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 19, 2015 at 10:02 pm
Try using OLE DB Command instead and update flag while inserting new records.
____________________________________________________________
APFebruary 19, 2015 at 10:53 pm
wrong solution provided by me....... 😛
_________________________________________________________________________
SSC Guide to Posting and Best Practices
February 19, 2015 at 11:03 pm
anshulparmar (2/19/2015)
Try using OLE DB Command instead and update flag while inserting new records.
How will you achieve it ?
Think of the scenario where OP wants to do this exercise for 1 million rows 🙂
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
February 20, 2015 at 12:17 am
I've had my morning coffee and think this might work, but you need a secondary table.
After the conversion task add Mulitcast, On one side send the data into the Destination, on the other send it into an JOIN via a derived column and add a Successflag (1 or Y)
On the error of the Destination, send the Error into a derived column and set the SuccessFlag to the inverse (0 or N).
Join (Left Join) the two sets on the Key, and pass into a condition, that checks for the 'N' flag on the error input, if NULL send the Key into the secondary table, ie its been a success.
If you really wanted to you could also pipe the rows that failed into a Quarantine holding table to check why they failed.
Add an SQL container outside dataflow and write a simple update to Set the flag on the source.
Just remember to truncate the subsidiary tables at the start of the process.
Its a bit convoluted but its about the only way to ensure you only update those rows that succeeded.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
February 20, 2015 at 12:36 am
Step1:Load SQL to AS400
Step2: Update SOurce table flag with Destination data for matched as 1 and mismatched with 0
February 20, 2015 at 1:20 am
rhythmk (2/19/2015)
anshulparmar (2/19/2015)
Try using OLE DB Command instead and update flag while inserting new records.How will you achieve it ?
Think of the scenario where OP wants to do this exercise for 1 million rows 🙂
It doesn't matter. The OP wants individual records to succeed or to fail, without the data flow crashing or transactions rolling back. This means the batch size will always have to be 1. In other words, incredibly slow.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 20, 2015 at 2:02 am
Koen Verbeeck (2/20/2015)
rhythmk (2/19/2015)
anshulparmar (2/19/2015)
Try using OLE DB Command instead and update flag while inserting new records.How will you achieve it ?
Think of the scenario where OP wants to do this exercise for 1 million rows 🙂
It doesn't matter. The OP wants individual records to succeed or to fail, without the data flow crashing or transactions rolling back. This means the batch size will always have to be 1. In other words, incredibly slow.
You could cascade the Errors, so that your core batch is large, then pipe switch patch sizes on the Error output, reducing by a factor or more, eg Main batch 10,000 rows, secondary batch 1,000 rows, final batch 1 row, that way you should maintain a degree of efficiency and wheedle out the offending row(s)
Theres a possible locking issue that could be raised, so you need to ensure you don't lock the table in any of the destinations.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply