CODEWITHSUNDEEP
sqlssissql-server-2008-r2
Raj More
Raj More

Reputation: 48024

Logging SSIS variable expressions

I am loading about 40 files to Oracle from my SSIS ETL package. At the end of each files load process, I run a SQL query to to perform a Type-2 update for old data expiration.

The SQL Query is stored in a variable called ExpireOldRecordsQuery which is built at runtime so the EvaluateAsExpression property is set to TRUE and the Expression goes something like this

"Update MyTable Set ExpiredOn = SYSDATE Where ExpiredOn IS NULL AND DownloadID <> " + @User::CurrentDownloadId

I want to log the actual query from the ExpireOldRecordsQuery variable.

How do I make SSIS log what the expression is evaluated to?

Upvotes: 4

Views: 1788

Answers (1)

bobs
bobs

Reputation: 22194

Any time your variable is referenced it will evaluate to the current value. This means, the value of ExpireOldRecordsQuery will always equal the string plus the current value of @User::CurrentDownLoadId.

The ExpireOldRecordsQuery variable can be used like any other variable, so you can log it's value. If you're using SSIS logging, you can use the FireInformation() function in a Script Task to send the variable information to the SSIS log.

Upvotes: 1

Related Questions