April 23, 2014 at 11:11 am
Dear all,
I am new to SSIS 2012 and I really like the default logging features.
As many, I use a mixture of Packages and Stored Procedures and I would like to be able to log into the SSIS logging framework directly from within my stored procedures.
Since it looks like the SSISDB Catalog has a stored proc interface, I suppose it should just be a matter of firing the appropriate stored proc with the relevant identifier...
Has someone done it who could give me some tips?
Thanks
Eric
April 24, 2014 at 12:34 am
What exactly do you want to log withing the stored procedures?
Maybe you can execute them with Execute SQL Tasks?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 24, 2014 at 12:45 am
Yes, I would most probably start the procedure with a Execute SQL Task, passing something identifying the execution of the package (for instance the SSIS Db execution_id if I could get access to it).
The sort of information could be intermediate results within my stored procedure: how many this, how many that, what was some condition at execution time, etc...
Anything that would add (business) value to my logging
April 24, 2014 at 1:08 am
You can start the executions of packages also through a stored procedure. You can dump the value of the SSIS catalog executionID to a parameter in the package and use that one in your stored procedure.
I would log to a table outside the catalog though and join it to the regular logging when you need it. You have control over the schema, while the SSISDB can change in a new version without a warning, possibly breaking your code. And it saves you all the effort of finding out where to log in the catalog 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 24, 2014 at 1:37 am
I must admit, I am tempted to have my own logging beside that of SSIS DB, precisely for the reasons you mention.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply