March 14, 2012 at 4:53 am
Hi
I need help with SSIS in sql server 2005. How can I update rows in a table based on data selected from another db server using the foreach loop. e.g.
SELECT field1,field2,field3 FROM tableA,
then foreach row must be used to update the same row from TableB on another db server
UPDATE tableB SET field3 = @field3
WHERE field1=@field1 AND field2=@field2
in another DB server.
I really need help,because I've already developed a C# program but it takes days just update 3million records and its not helping at all.
Regards
Sello
March 14, 2012 at 5:08 am
You could transfer the data from Server1 into a staging database on Server2, then write a stored proc which joins to the staging database and does the update.
March 14, 2012 at 5:25 am
Hi Anthony
Maybe I'm not explaining exactly what i really want to achieve. You see on my c# program, I have 2 connections pointing to two different DB servers. From one DB server I call a stored procedure that selects * From DBServer1.table then returns the results in a dataset, then i've created a foreach loop that passes parameters to executes another stored procedure that updates records in DBserver2.
It works fine, but performance is poor, so i thought i can use sql integration services.
Regards
Sello
March 14, 2012 at 5:37 am
different ways to do it
you could as I said, copy the data from server 1 into server 2 and then do a update on server 2
or
you could use a data flow task to export the data into a dataset of a variable, then use a for each loop to update server 2 passing in variables for the different fields
March 14, 2012 at 5:52 am
Hi
Thank you! Can you show me how to do that, I only have limited sql knowledge especially coming to ETL processes using SSIS. Do you have website links where I can find similar examples?
Regards
Sello
March 14, 2012 at 6:03 am
lots of good links on google, do a search for ssis 101 or ssis basics and take a play in BIDS, knock up a couple of packages and test them on your local instance or dev machine to ensure they do that they should do
for the first method, you will need 2 connection managers, 1 data flow task (with a sql source and sql destination) and 1 execute sql task, 1 new database created on server2 and a table to insert the data, a procedure to do the update of the proper data in Server2, use the DFT to transfer data from server1 to server2 then execute the procedure which does the update
second option, you will need 2 connection managers, 4 variables (1 object, then 3 of the types which field1,2,3 are), 1 date flow task (with a sql source and a recordset destination giving the variable which is of the object type as the record set holding area), a for each loop with the for each from variable itteration, with a execute sql task which does the update passing in variables for field 1,2,3 as needed.
March 14, 2012 at 6:31 am
sellonster (3/14/2012)
Hi AnthonyMaybe I'm not explaining exactly what i really want to achieve. You see on my c# program, I have 2 connections pointing to two different DB servers. From one DB server I call a stored procedure that selects * From DBServer1.table then returns the results in a dataset, then i've created a foreach loop that passes parameters to executes another stored procedure that updates records in DBserver2.
It works fine, but performance is poor, so i thought i can use sql integration services.
Regards
Sello
Anthony has already suggested this, but I thought I'd chime in to reinforce.
Run the SP to select from server 1, but put the results of the select directly into a table on server 2. When you're using SQL, always try to avoid doing things one row at a time.
Then run an UPDATE on server 2 which uses the newly populated table to perform the update. This will avoid row-by-row updates which are killing performance for you currently.
You probably do not want to use SSIS for this, unless you are going to throw away what you have already written.
Having said that, it might be worth doing that if there is, or will be, lots of data to transfer.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy