Hi, I have a situation where I need to transfer about 3 million records from a table. The destination server has a stored procedure that I will need to insert the data into the destination table. In other words the stored procedure has to be executed repeatedly 3 million times to insert the data. What would be the best approach for this situation and are there special task that I can use to handle this. Any advice is much appreciated. ThanksVj

That sounds pretty ugly. Why do you need to use the stored procedure? Is it a security item? If you absolutely need to do it this way, one method would be to use the ole db command to call the stored procedure with the correct parameters. Note that this will execute one record at a time and will be very slow. My suggestion would be to look at the logic being performed by the stored procedure and see if you can duplicate the logic within the data flow of your package. If it is possible, and you are allowed to do so, then you should probably recreate the logic. If this is not possible, you will probably have a very long running package on your hands...Please mark answered posts. Thanks for your time.

Hi Eric,I understand your point about the time it takes. The reason to have it via a storedprocedure is to keep the logic of inserting to the database at a single location (i.e with in the stored proc) . Is there a task that can be used to call the ole db command until the end of records. Like a while or a for loop with in SSIS. Thanks.VJ

For the solution I proposed above, you would use the data flow task: An ole db source - whatever transformations are necessary - the ole db command component (calls the stored procedure for every row). This will call the stored procedure for every record returned by your source. You could potentially use an execute sql task to return your recordset, followed by a for each loop container and another execute sql task to call your stored procedure. I don't know that this would give you any benefit over a data flow task though... (i would think it would hurt performance, but as always, test both cases and use the one that makes sense for your environment)Please mark answered posts. Thanks for your time.

Hi,That is very helpful and thanks for the quick reply. I'll test both. VJ

DECLARE @value INT DECLARE cur CURSOR FOR SELECT value FROM YourTable open cur FETCH NEXT FROM cur INTO @value WHILE @@FETCH_STATUS = 0 BEGIN exec YourProcedure @value exec AnotherProcedure @value FETCH NEXT FROM cur INTO @value END CLOSE cur DEALLOCATE Cur Take a look at the following. I guess it will ask you how many times you want to execute a particular Stored procedure.

Hi Like Above Showri suggested you need to insert the date into a temp table first from the date flow, after that you can execute your stored procedure in another stored procedure like above suggested or you can change your stored procedure corresponding to the current change. Let me know if I am wrong. RAVIKIRAN.M