SSIS Logging
-
All,
I'm looking for a bit of advice as to best practices with Logging across multiple environments. I have a Parent/Child package set up (1 Parent 2 Children) whose connections are set using values from a config file. Also note that within one of the child packages I have set the 'Propogate' variable on a specific task to False within the Event Handler. This is to allow the step to complete without marking the child package as a failure - I've done the same with the parent package when calling the child package. I have a log table where the event handlers write to in the event of failures. Upon completion of the child packages, the parent package checks the log table for failures and emails notifications to end users.
However, the packages are ran as part of a SQL job, when steps fail within the package, this is no longer noted in the job execution itself (which is expected). Although the notification of the failure via the process noted above will be sent, we'll have no details of the failure itself.
Therefore I want to turn on logging using the SQL Server provider. However, due to the connections being handled by config file, logging doesn't work using the connection in the connection manager. Apparently this is not possible https://weblogs.sqlteam.com/dmauri/2006/03/27/9438/
I'd appreciate some pointers as to what my best options are here with a logging solution.
Is it possible to use logging with a configurable connection or can logging only be used for dedicated connection values?
If not, would I be better off creating a bespoke powershell / c# script to record logging data?
If I used an alternative way of setting the connection config values i.e. scarp the config file and deploy the package to Integration Services Catalog instead, using project params as the source of the connection config values, will logging work there?
Thanks in advance
-
I find the logging in SSISDB to be incredibly verbose; at times I feel like it's logging too much much it's one of those things where you are really grateful at how much it logs when something goes wrong. What is it that you're saying isn't getting logged in the logs by your SSIS packages? If it's Script Task items, then you'll need to ensure you use the Information, Warning, etc, message provide details to the logs. perhaps you just need to add some information messages?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Its more a case that I can't turn logging on where my connections are set using config files.
I can worry about tailoring the logging options later, I just want to get it up and running first with the current connections (if thats possible).
I'm not doing any Data Flow Tasks within my packages so another option would be to write exception handling within the stored procs (I know I should have done this anyway) and get them to write the errors into another database table. But, with the option of logging available in SSIS, it would have made sense to just turn this on. Seems strange that you can't use it when your connections are not hardcoded
-
Do you mean that in the Advanced pane in the Job Steps properties that you can't change the Logging level from None? So you can't select other options as seen here?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
I haven't tried that, I'll take a look.
The setting I'm trying to use is within the package itself. But the connection won't work as its values are set from a config file
-
Tried the logging option in the job step but got the following error:
The log provider type "{2E5B5C92-2968-4520-95A8-3A33D55E4507}, {2E5B5C92-2968-4520-95A8-3A33D55E4507}" specified for log provider "{D5145C67-8C71-4CD3-9652-0792EC434AD8}" is not recognized as a valid log provider type. This error occurs when an attempt is made to create a log provider for unknown log provider type
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply