Logging SSIS Variable

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?

note: cross posted here

more ▼

asked Nov 18, 2010 at 07:05 AM in Default

avatar image

Raj More
1.8k 83 89 90

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

You can eg configure internal SSIS logging feature.

  1. In the SSIS/Logging... configure logs you want to use.

  2. If you are using the Execute SQL Task for you command, enable logging for the "Execute SQL Task for your command.

  3. In the details of configuration of Execute SQL Task enable logging the "ExecuteSQLExecutionQuery"

And you have logging implemented. You will see in the log what query was executed even it is build dynamicaly or read from variable etc.

You can also implement youw own loggin eg. by implementing EventHandler for PreExecute, PostEvecute, PostValidate etc.. depending when you want the logging to occure. Put a script task into the event handler and write your own logging iside the script.

There are many ways of how to accomplish this, but I think, the internal logging is the optimal one.

more ▼

answered Nov 18, 2010 at 09:31 AM

avatar image

Pavel Pawlowski
22.7k 10 15 26

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Nov 18, 2010 at 07:05 AM

Seen: 1574 times

Last Updated: Nov 18, 2010 at 07:06 AM

Copyright 2018 Redgate Software. Privacy Policy