Hi, I am using a cursor inside the stored procedure to loop through rows and place values into variables i.e. @dsss, etc. Is there such a fuunctionality in SSIS or should I just use the execute sql task to execute this sql with cursor in it? Thanks

it is possible using a for each loop on the dataset returned by the execute sql task and then the logic with in the loop for the variable assignment-------------------------------------------------------- Surender Singh Bhadauria

Hi, First of all, using cursor is not a good idea in SQL server as it is a resource intensive process. Please use temp table or table variable instead in the stored procedure. There is no direct transformation to execute the SQL exists inside the cursor in SSIS. But you can "for each" component with cursor SQL being used as condition in the tranformation. Best regards, Sengwa Best regards, Sengwa

Probably there is an easier way, for example you can sift through all the records obtained from an OLEDB source using a Script Transformation like here: http://whiteknighttechnology.com/cs/blogs/brian_knight/archive/2006/01/12/80.aspx My understanding you only need to loop through each row just once to populate a variable based on a certain (obscure to me) condition, if so you merely substitute in the body of the Script Transformation the code in the example above to process a row to what is shown here (how to populate a variable) - http://www.programmersedge.com/?p=341, but you still need to be doing that inside the Input0_ProcessInputRow routine. Arthur My Blog

1) Performance wise, cursors are not recommended. 2) Alternatively, a temp table can be used to populate the actual data(from the first table) with an idenity column, then a while loop can be used to loop through the table rows. Please check this : http://deepaksqlmsbusinessintelligence.blogspot.com/2011/07/using-while-loop-to-loop-through.html 3) As mentioned already by many other, for each loop is the call if there is no alternative to SSIS.Happy to help! Thanks. Regards and good Wishes, Deepak.