Hi All, I have a problem and i would like to get some help on it. I have detailed my problem down to steps. 1) Need to get a SQL table record set 2) Then loop through the records. 3) Get the value from a column from the recordset and then perform number of If ,ELse IF, ELse if conditions. 4) Then according to that condition satisfy update the destination columns. How can i achieve this? Upto step 2 i can do using a SQL task and a For each loop container. But for steps 3 and 4 what can i do? IF i use a script task (if this is the solution) how can i output the columns to the destination? Any idea guys? how can i do this. Plz help me on this with some tutorial, or some references. Thanks Lucki

you can do one of these solutions: 1- do it all in data flow task; add an oledb source for source table, then add an OLE DB Command and run a stored procedure for each records of data, in the stored procedure you can do any condition check like if then else.... 2- do it in control flow; first load all data from source table into RecordSet destination into an object type variable, then in control flow inside a foreach loop container with enumerator as ADO enumerator fetch each item and inside the foreach loop add a script task to check any condition you want and then use a execute sql task to update the table as you wish. I recommend to use data flow task method ( first way ), and go to second way if you have no other choice, If these doesn't make sense to you tell us more brief about what you want to do exactly? what is conditions? and what is updates for example?http://www.rad.pasfu.com

I would like to propose one more solution: 1. ensure that all tasks are using the same database connection. 2. Create temp table (it should contains PK of destination table and values to be updated) 3. Use SSIS data flow to populate temp table (use expression or script task for the logic) 4. Write an UPDATE statement that will update all rows in single SQL command based on the data from temp table. In this scenario you will not call database from SSIS for each row, so for large number of rows it will be faster.

Hi Reza, Thanks for the reply. I will have to go with the second option u mentioned. Coz i am unable create sps in client server. In the second method i understand up until putting the script component to a for each loop container. Then my conditions are from the recordset that i get from the sql table there are values i need to check and if that value is say 90 i need to assign a value from the source recordset to the destination column. My problem lies here. How can i assign values to variables in script component and output them and map it to the destination table values? Can anyone please help me in this regard. I am bit stuck with this script component. Any code would be ideal. Thanks Lucki

Hi All, Any help on my problem? Now i am a bit stuck with how to do my conditions inside the for each loop container and output the columns. Then i need to map this columns to the destination. I tried using a script task inside the foreach loop container to add my logic but i am a bit confused of how i can pass the output variables to a SQL destination table. Any help much appreciated. Thanks Lucki

so you just need an empty script TASK in foreach loop container, after the script task put an execute sql task, and connect green arrow ( precedence constraint ) from script task to execute sql task. then right click on green arrow and select edit, there you can write an expression like: @[User::MyVariable]>90 I wrote a sample of IF condition in control flow here: http://www.rad.pasfu.com/index.php?/archives/11-Implement-If-condition-in-SSIS-package.html let me know if you need more details on how to configure the foreach loop or condition in precedence constraint.http://www.rad.pasfu.com

so you just need an empty script TASK in foreach loop container, after the script task put an execute sql task, and connect green arrow ( precedence constraint ) from script task to execute sql task. then right click on green arrow and select edit, there you can write an expression like: @[User::MyVariable]>90 I wrote a sample of IF condition in control flow here: http://www.rad.pasfu.com/index.php?/archives/11-Implement-If-condition-in-SSIS-package.html let me know if you need more details on how to configure the foreach loop or condition in precedence constraint.http://www.rad.pasfu.com

so you just need an empty script TASK in foreach loop container, after the script task put an execute sql task, and connect green arrow ( precedence constraint ) from script task to execute sql task. then right click on green arrow and select edit, there you can write an expression like: @[User::MyVariable]>90 I wrote a sample of IF condition in control flow here: http://www.rad.pasfu.com/index.php?/archives/11-Implement-If-condition-in-SSIS-package.html let me know if you need more details on how to configure the foreach loop or condition in precedence constraint.http://www.rad.pasfu.com