For Loop in Script Task - SSIS - SQL Server 2005 SP2

[ReportDr00]

Hello

I have to add or update records in the database, but using business tier of an application. I have a dataflow task that reads the data and stores it in record set destination and on control flow task i added for each loop to loop the recordset and with in for each loop i have script task that references the business tier of an application and opens the connection add or update the record and close the connection and does the same for rest of the records.

How could i open the connection only once and loop the records with in script task and add/update them in database and close the connection?


Thanks

 

[MDXer]

you would be better served doing this in a Dataflow. Build a dataflow in such that you split your records to add and your records to update. You can use a destination directly to your table to insert records. For the update send them to a stage table and then in the control flow execute a update statement.

This is the only way to avoif opening and closing the connection.