June 22, 2011 at 2:54 pm
Ok. Have this configuration:
SSIS 2005
-Parent package calls 3 packages sequentially with success constraints between each one. The parent package is launched from SQL Agent Job.
-Event Handlers in the child packages: OnInformation, OnError, and OnPostExecute event handlers are configured with script tasks to perform custom error logging and email notification. The OnError writes the error info to a log table if there is an error. The OnInformation script task checks for configuration info in a log config table and writes all info for each task. Note: It simply checks for existence of the package in the config table. The OnPostExecute tasks check for presence of an error in the log table and emails such to predetermined individuals.
-Package Configuration is set on the child packages.
Problem:
The parent package job completes successfully but the package does not run the child packages UNLESS the log config table contains the child package name.
Has anyone heard anything like this? What additional info do I need to give you?
June 24, 2011 at 2:39 am
Can you explain in more detail why you have an Oninformation event handler with a script task that goes looking into a log table?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 24, 2011 at 6:12 am
Thanks for your reply. The reason is to be able to customize the logging on the fly. I can completely control the log entry and when it fires on the fly. I normally don't need OnInformation items, but I might need to turn it on for troubleshooting or step validation/verification.
The OnInformation script task checks the logging config table to see if the package is listed there, if not the script ends, if so it logs the information text into the log table.
June 24, 2011 at 6:31 am
The logging config table is what exactly?
A config table that contains a package configuration that sets the connectionstring for the log provider?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 24, 2011 at 6:39 am
No. Much simpler than that. It is a custom table that just lists the rowid, package name, type of logging and datelastset. The connection information for the log table is in the script.
June 25, 2011 at 5:39 am
So if I understand it correctly, an event handler just checks a table, but for some reason child packages are not executed?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 27, 2011 at 6:46 am
That is correct. However, here is an update:
Rebuilt parent package from scratch. 2 out of the 3 packages now launch as expected. The 3rd does not launch and an error is produced in SQL Agent Job history. Typically, the error is not complete. Removed the 2 packages that work from the parent package and enable logging to a text file in the parent package. SQL Agent Job history now shows what appears to be a coherent message, but I am not able to find specifc info about the error number. The SSIS log file just shows 'package end'. There is no error in the log. Here is the message from SSIS Agent Job history:
The return value was unknown. The process exit code was -1073741571. The step Failed.
I suppose the next logical step is rebuilding the offending package, but I would like to know if the issue is a corrupt package and/or a possible needed hotfix or some other known issue with Integration Services. Any ideas are appreciated.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply