This is how one table/view would look like in an SSIS package:
Yes, that means (as far as I know) that you have to create four objects for every table you want to transfer data from/to.The Source object is, of course, connected to the Source database's table/view. The other objects beneath it (the Lookup, Destination and Update objects) are all connected to the Destination database's table/view.
The Update object is the only tricky element in this package. I specify the update script manually, which looks like:
UPDATE dbo.MyTableSET Column1 = ?,Column2 = ?,Column3 = ?WHERE ID= ?
(see: Generate SSIS Update script which I use to generate the update script for each table that I have in my SSIS package)
Each "?" will be automatically mapped by the system based on the order you defined it. All you have to do next is to specify the Column Mappings. Make sure that you do it in the right order like the image below:
Note how the ID is specified last -- that's because its corresponding "?" was specified last in the Update statement.
Posts
8 comments:
Ack!
UPDATE dbo.MyTable SET
Column1 = ?
,Column2 = ?
,Column3 = ?
WHERE ID= ?
How can I put names to the parameters?
HI AFTER LOOKUP I HAVE USED OLEDB DESTINATION AND DID MAPPING LIKE BELOW http://social.microsoft.com/Forums/getfile/19170/
IS THERE ANY NEED OF UPDATE QUERY ???
Hi, ashuthinks,
You only need the update query if you want to update existing data. If all you want is to insert missing data, then you don't need it at all.
I can't find the way to mapp input cols to parameters. From the lookup object I get 2 columns so for the match output I set the OLE DB Command with "UPDATE dbo.MyTable SET MyCol2 = ? WHERE MyCol1 = ?". In this case MyCol1 is my ID but there isn't any mapping where it should so UPDATE statement is not working at all.
Very useful post. This is my first time i visit here. I found so many interesting stuff in your blog especially its discussion. Really its great article. Keep it up. cpa network
Hello all,can anyone please tell me from this post that how can I simultaneously create and change the table without disturbing the current process in the image titled 'Bulk Update in SSIS using Upsert Destination.Is that possible by any means?Thanks.
Post a Comment