November 20, 2007 at 3:50 am
Hi there,
I'm fairly new in the SSIS building and currently need to make an flow in which 3 different actions need to be done. I have not found an efficient way to that with SSIS yet.
Basically I have a recordset, which contain records triggering an updateaction and records triggering an insert action. The update action consists of 2 different updateactions, based on the records indicating an update.
How can I best do this in SSIS?
Greetz,
Hans Brouwer
November 20, 2007 at 5:47 am
You can first write a SQL command to execute whatever updates you want in {Execute SQL task}. Drag and drop this in the design section and connect to its database. Then, drag and drop {Data Flow Task} and double click that. Now, you will be in dataflow tab. Drag and drop {OLE DB Source} (if your source is from database table) Connect that and do another query to do what ever updates you want now by simply using the {OLE DB Command} Task. Then, simply drag and drop your {OLE DB Destination}
This is how you do multiple queries. You can actually drag and drop more {Execute SQL Task} and also in Data Flow tab add more {OLE DB Command} to add more SQL queries. All it matters after that is how you connect the presedent contraint(the tiny arrows) depending on which statement should run first.
Good Luck! 🙂
[font="TimesNewRoman"] “I haven't failed, I've found 10,000 ways that don't work”........Thomas Alva Edison[/font]
November 20, 2007 at 5:58 am
If I am following, you have a data flow that contains records with a few indicators. The indicators determine if there should be some insert and updates made.
If each record can only cause one action, use a conditional split to put them all into individual data flows. This will allow you to run all of your inserts and updates in parallel.
If the records could cause an insert and an update, you could use multi-casts to make enough copies of the records to support all of the actions. This may be efficient if you have a limited number of actions and your records can only cause a small number of them.
Or, you can use the OLEDB Command transformation. This transformation object will allow you to wire up a command to your data flow and it has an output so you can string multiple commands to run sequentially. Your command will be something like "UPDATE T SET MyCol = ? WHERE MyID = ?" and then you wire it into your data flow with the parameters. Again, if you want to speed things up, you can use a multi-cast to allow some of your updates to run in parallel trading memory use for performance.
November 20, 2007 at 7:41 am
That's an interesting article with some good tips. What is actually being done in the article (seeing if a record exists and then if it does, if the record has changed) is handled by the Slowly Changing Dimension transformation component also.
This component (which is really a component and a wizard) will determine if a record has been changed and creates the necessary insert and update components.
November 20, 2007 at 12:12 pm
Glad you liked it. Hope you read ther other article, to which was referred in this article as well. Same functionality, different approach.
Greetz,
Hans Brouwer
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply