September 10, 2008 at 3:11 am
HI All,
I'm new to this so I appologise for the simplicity of the question.
I have a Package called "Master".
This Package contains multiple "Execute Package Tasks" which lead from one to another.
I want to make sure that they are all part of the same transactions, so if the last package fails then all the packages are rolled back including the first one.
Is this possible?
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
September 10, 2008 at 6:54 am
Not in my experience.
Usually what happens is that the SSIS package considers the "Execute Pkg" task as just a command to start the next package rather than waiting for the package to finish before it moves on to the next step. This is what defines success or failure for it. If the package starts, the parent package moves on to the next "success" step. If the package fails to start, the parent package either fails or moves on to the next "failure" step.
You can try setting transactions in the parent package as Required and then set the same settings on the child Execute pkg tasks, but I don't think it'll work. I think we had to script something out to do this properly. Let me get with the developer who worked on ours and see how he got around the issue. He won't be in till later, though.
September 10, 2008 at 7:23 am
I just checked with the developer and he agreed with my comments. There is no way to role child packages back from a parent package.
Here's what we did.
1) Set up a job that takes a snapshot of the database in the first step.
2) Set up steps that run each individual package in order (this ensures that the next package won't start until the previous package has finished running)
3) Upon failure of any package step, the DBA team is immediately paged & emailed so we can figure out why it failed. You could set up a auto-restore of the snapshot, but that makes troubleshooting a little harder.
Anyway, once you've resolved the problem, restore the db to the snapshot and re-run the job.
September 10, 2008 at 7:27 am
Hi Brandi,
I wanted to thank you a lot for going out of your way to help me on this 🙂
It was a great help 🙂
Thanks again
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
September 10, 2008 at 7:38 am
NP. Sorry I didn't have better news.
September 10, 2008 at 7:49 am
Thats no problem at all, just adds a challenge 🙂
About the steps that you mentioned is that done with a sequence container?
If so does each package call need to be in a seperate sequence container
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
September 10, 2008 at 8:03 am
Job steps don't have sequence containers. Look under SQL Server Agent -> Jobs. Right click jobs (or in the white space of the Summary window in SSMS) and choose "New Job". Then fill in everything you need to.
BOL should have the details on creating new jobs. I usually use Operating System job steps to run my SQL Packages rather than the Integration Services job steps because I can log the OS job step to my job log file (including step output) which usually gives me more details on successes & errors than the SSIS logging does.
You can get the necessary command line info by going to IS in SSMS, right clicking your package and choosing "Run Package". Chose all your options, including config files, then flip over to the command line tab. Cut N Paste into your OS job step. You'll have to do minor edits to make sure you're pointing to the full folder path where the package is located, plus adding "dtexec.exe " to the front of the command line. But that should set you up just fine.
September 10, 2008 at 8:26 am
Thanks for that Brandi,
In BIBS when I run my Package execusion task to run one of my packages it gives this error
[Execute SQL Task] Error: Failed to acquire connection "DestinationConnectionOLEDB". Connection may not be configured correctly or you may not have the right permissions on this connection.
However when I run the package manually in BIBS it runs fine this is confusion me terribly 🙁
thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
September 10, 2008 at 8:35 am
Sounds like the connection manager for that Execute Package task is set up wrong. Look it over and make sure you don't have any typos and that the location is listed correct. There is a difference between SQL Server & File System.
Also, do you need a pwd to run that package?
September 12, 2008 at 12:38 pm
I don't know if this will work (because we can't get checkpoint files working properly on our cluster right now), but you might look into setting up checkpoint files on your parent & child packages and see if that helps you role back previous child packages if a later one fails.
BOL has information on this. Also, you can google several good articles on it.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply