September 16, 2013 at 6:33 am
Hi All,
I have a SSIS package which dumps Data from a SQL table to, say, a Oracle table. Once the Data is dumped, the SSIS is also expected to Update a flag column (from 'N' to 'P') in the table back in SQL Server. This Update is done by a SQL command in Control Flow.
This approach of using mass update in control flow needs to be replaced by row-by-row update using Oledb Command Task in Data Flow. Several reasons for this change.
The point of discussion simply is - How to use OLEDB Command task ? I have never really used this.
I am also attaching 3 screenshots which will help to understand the structure of my package and its very straight forward. Request you to help me in this regard with reference to the screenshots.
So, e.g. I know that a Oledb command task would needs to be configured in Data Flow tab, I would need to know how, w.r.t my screenshots.
Thanks for helping....
Regards,
Athar
Regards,
Athar Iqbal
September 16, 2013 at 6:44 am
Here's a video on how to implement the OLE DB Command:
SSIS OLE DB Command Transformation
Basically you are issuing an UPDATE statement for every row. In the editor, you map columns of your data flow buffer to columns in your UPDATE statement.
Let me warn you that if you are transferring a lot of rows to Oracle. the performance of your package will plummet. It can get easily 100 times slower.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 16, 2013 at 6:56 am
...the performance of your package will plummet
Nice alliteration!
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 16, 2013 at 6:59 am
Phil Parkin (9/16/2013)
...the performance of your package will plummet
Nice alliteration!
Yes ... that was totally my intention ...
😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 16, 2013 at 7:04 am
..back to SSIS.
If you want to update a row in SQL Server for every row that is transferred to Oracle, I'm not sure about this method. Are you trapping error rows somehow? I'm guessing that you may want to handle rows which did not transfer successfully differently from those which did?
Can you explain why you have opted for the row-by-row method? Just curious.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 16, 2013 at 7:43 am
Hi Phil,
Glad that you asked.
Quickly answering your Question on why row-by-row....
This SSIS package was taking in excess of 40 min to run. The Issue here was that SSIS would update a column in table with Flag - say P, suggesting that all records have been successfully loaded to oracle. This assumption by the Package was not true, not all the time.
So, if there were instances when there was some connection lost ( and thats why I mentioned the time this package took to completion) and the SSIS was NOT able to successfully load ALL the records, still the Execute SQL Task would execute and Update ALL the records to - P ( update flag = P where Flag = N), incorrectly suggesting that ALL records have been loaded.
Then I thought to use some row-by-row update for this requirement. OLedb, was the obvious answer.
Performance impact is something that I am worried about.
Phil, Please enlightn me if there are some simpler options to do this.
September 16, 2013 at 7:54 am
Oh boy, if the package already takes 40 minutes to run, the OLE DB will slow it down to a couple of hours.
Furthermore, regarding your statement "... incorrectly suggesting that ALL records have been loaded.".
This should be incorrect. If the data flow cannot load all records, it would fail. If you use an OnSuccess precedence constraint between the data flow and the Execute SQL Task, the update would not run at all. This would however suggest that no records at all have been loaded, which might not be true.
Also, using an OLE DB command to do the update would only reflect that a row has been succesfully read from the source, but not necessarily written to the destination.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 16, 2013 at 3:40 pm
If it is important that all rows are transferred, then put all the relevant control flow tasks in a sequence container and turn on transactions. You may also need to start the Distributed Transaction Coordinator on the server running the package.
September 17, 2013 at 12:26 am
Hi Phil,
when you say - "I'm not sure about this method", Can you please suggest an alternate to this ?
Can Transactions and Containers solve the problem here ?
I am sure this would be a common requirement and would have more than one solutions to it. Loading Data to a separate Database and knowing which of the records loaded and which not by updating a column back in source table as Flag, is something I have heard of a lot, not implemented it personally and thats the issue
Request you to refer the screenshots as well which I have attached with my question.
Awaiting response.
September 17, 2013 at 10:48 am
OK,
Looking at the screenshots, I think your safest bet would be to have another data flow task
2 data sources: SQL and Oracle - select the original dataset from SQL and the new dataset from Oracle
Sort both data sets on their Unique Key (do it in the SQL if you can, it can be slow and use a lot of memory in SSIS)
Merge Join on the Unique Key: This will drop records which are not matched
1 data destination: A temp table in the SQL database (make sure you have cleaned it out first)
Call a sproc that updates records in the source SQL table based on a match in the temp table. This is MUCH MUCH faster than calling the update command one record at a time as SQL will use the query optimiser.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply