May 18, 2011 at 12:54 pm
I have a DTS package I'm trying desperately to fix. Package A, the parent, which gets counts from tables updated by the child and sends an email message and executes a Package task(sub package), the child, which runs 3 stored procedures. When the Parent package is run, it completes successfully with no errors, but for whatever reason, it's NOT calling the child package. Doing a SQL Trace, verifies that the Parent has run, but not the child.
If I run the child separately it works fine! If I run the stored procedures manually they work fine. All the permissions are in place. Not sure what else to check?? Any suggestions would be appreciated.
Thanks, Don
May 18, 2011 at 1:46 pm
Have you checked the properties on the Execute Package Task?
I assume that you have a precedent constraint define as on success?
When you execute the package in Design mode does it show that the child package completed?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 18, 2011 at 1:50 pm
Yes it shows the child has completed and the stored procedure, Execute SQL Task, has completed successfully, yet it does not do the update to table within that stored procedure. A select of the table after the package has finished, shows no update and it should be updating 47,304 rows.
May 18, 2011 at 1:54 pm
Scratch that last post. If run from SQL Agent it runs successfully without updating the table needed. When run in design mode, it fails on an active script task, but think that is do to permissions, which are different for the SQL Agent.
May 18, 2011 at 2:18 pm
ddnikon (5/18/2011)
Scratch that last post. If run from SQL Agent it runs successfully without updating the table needed. When run in design mode, it fails on an active script task, but think that is do to permissions, which are different for the SQL Agent.
You are correct it is a permissions issue.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 19, 2011 at 3:58 am
Still doesn't fix the issue when run from SQL Agent, which has the correct permissions, log files say everything ran OK, parent and child, yet a table is not being updated?? Could that be a permission issue as well? The update step ran for approx. 12 seconds, but could it be trying to authenticate the permission for the update??
Thanks
May 19, 2011 at 9:16 am
It sounds like you do not have all of the permissions needed that you package is trying to reference.
What is in the Job History, Package Log, etc?
Can you run Profiler and Filter?
You mentioned earlier that when you run as SQL Server Agent Account interactively it does not update a table?
If you could log on using the SQL Server Agent Account and run the package in Design Mode that would the quickest and easiest way to identify the problem. THat is what I would do.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply