November 2, 2011 at 1:39 pm
Hi,
I have some posts about the issue I'm having but I was not able to implement yet. My issue is simple (not to me though :)).
This is what I'm trying to do. My data flow connects to a DB, retrieve the data I need, run a script to do some work with the data, use a data conversion component to map the data correctly and then inserts the data into an OLD DB Destination.
The issue is, I need to either insert or update the data if already exists. I was not able to find in the OLE DB Destination component an option to do so.
Here's my data flow. What am I missing ?
Thanks in advance.
November 2, 2011 at 2:04 pm
Assuming that the source and destination tables have a common unique key, you would do a lookup in the destion table to see if the record already exists. If it does, your success condition would go to an update. If not, the failure condition would go to an insert.
November 2, 2011 at 2:19 pm
Thanks for the reply.
So if the records doesn't exist it will do to a OLD DB Destination, right ?
But if it does exist, what is the next component after the Lookup ? (that's where I'm stuck)
November 2, 2011 at 2:55 pm
An alternative that would work well would be to stage that data into a "temp" table and then use the merge statement.
Otherwise, what Lynn provided should work.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 2, 2011 at 3:17 pm
elkucho (11/2/2011)
Thanks for the reply.So if the records doesn't exist it will do to a OLD DB Destination, right ?
But if it does exist, what is the next component after the Lookup ? (that's where I'm stuck)
Can't help from here, don't have BIDS available. Will follow up from home.
November 2, 2011 at 3:31 pm
elkucho (11/2/2011)
Thanks for the reply.So if the records doesn't exist it will do to a OLD DB Destination, right ?
But if it does exist, what is the next component after the Lookup ? (that's where I'm stuck)
Your next component would be either a script, or you would need to dump the data into a holding table (an ole db destination)
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 2, 2011 at 3:32 pm
elkucho (11/2/2011)
Thanks for the reply.So if the records doesn't exist it will do to a OLD DB Destination, right ?
But if it does exist, what is the next component after the Lookup ? (that's where I'm stuck)
You would need to send the rows that need to be updated to an OLE DB Command Transformation, which can run a SQL statement for each row in the input. You'll need to set up parameters to pass to it; details on configuring the OLE DB Command Transformation can be found here: http://msdn.microsoft.com/en-us/library/ms141773.aspx. Note that this is a very inefficient method for doing updates if you have a non-trivial number of rows to work with, since it executes the OLE DB Command once for each row. It's generally much more efficient to stage the data to a table, then issue a single UPDATE that joins your destination table with the staging table.
November 3, 2011 at 11:23 am
Works fine now!
Thanks a lot for all the answers!
November 3, 2011 at 4:35 pm
elkucho (11/3/2011)
Works fine now!Thanks a lot for all the answers!
Congrats - that is good.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply