Monday, December 7, 2009

SSIS : Execute SQL Task for Dynamic Update Statements

Using the SSIS Execute SQL Task for updates

1) Create a Variable to hold the SQL command
2) Set EvaluateAsExpression to TRUE
3) Set the Expression (example below)

"UPDATE  [Audit].[ImportProgress] SET LastProcessedID = " +(DT_STR, 12,1252) @[User::LastProcessedID]  + " ,  LatestDate ='"  +(DT_WSTR, 20)(DT_DBTIMESTAMP) @[User::LastDateTime]  +  "'  WHERE ETLProcess = 1"

4) Add an 'Execute SQL Task' task to the control flow.
5) Set the SQLSourceType property to Variable
6) Set the SourceVariable property to the variable name specified in step 1

No comments: