August 1, 2013 at 10:47 am
Hi,
Question:
Can I run a series of SSIS packages from a batch file where each package has 10 steps, and after a certain step (specified within each package) the batch file starts the next package up so that more than one package is running at once? Like, is there a task that basically says "send completion signal?" Or is there some other mechanism by which I can run a series of SSIS packages, setting a marker within each package that tells when to start the next one running asynchronously?
Background:
I have several SSIS packages that I run in a row. Most of them extract a bunch of data via Excel Business Warehouse add-ins, then load that data to SQL tables and massage the data. The Excel part of each package needs to run by itself as it runs on the local machine and I only want one Excel session to be open. As soon as the Excel part of each package is done (or in cases where there is no Excel then immediately), I would like the batch file to move on to the next package so that it starts while the previous one is finishing up.
Here is a sample of what my batch file looks like:
set dtexec="C:\Program Files\Microsoft SQL Server\100\DTS\Binn\dtexec"
%dtexec% /F "C:\DATA\SQL Packages\My 1st Package\My 1st Package.dtsx" /REPORTING EWCDI >>"C:\DATA\SQL Packages\My 1st Package_error.log"
---------------------------------------------------
set dtexec="C:\Program Files\Microsoft SQL Server\100\DTS\Binn\dtexec"
%dtexec% /F "C:\DATA\SQL Packages\My 2nd Package\My 2nd Package.dtsx" /REPORTING EWCDI >>"C:\DATA\SQL Packages\My 2nd Package_error.log"
---------------------------------------------------
set dtexec="C:\Program Files\Microsoft SQL Server\100\DTS\Binn\dtexec"
%dtexec% /F "C:\DATA\SQL Packages\My 3rd Package\My 3rd Package.dtsx" /REPORTING EWCDI >>"C:\DATA\SQL Packages\My 3rd Package_error.log"
Thanks for the help!
Tai
August 1, 2013 at 2:14 pm
You can create a master package that contains all of the other packages, and use multiple precedance constraints to set up the workflow among packages.
August 1, 2013 at 2:41 pm
I don't understand how that would help me... unless I am missing something, I would need to pre-determine the exact order in which my packages will run, and put an execute package task in each one pointing to the next, in order to start one package executing before the next is complete.
What I would like to do is define a point within each package at which it is OK to commence running other packages asynchronously.
Thanks.
Tai
August 1, 2013 at 2:52 pm
If I understand correctly what you're doing, you could have a table which acts as a log of what has been done or what needs to be done. Each package would check that table to see what needs to be done and then update the table as it gets done.
I've used something like this before and it worked great for me.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 1, 2013 at 2:57 pm
Maybe I should have posted in the 'newbie' forum for this, as my grasp of most things SQL-related is weak I don't understand how that would be implemented.
For clarity, though, my packages are not inter-related. My main objective here is to have more than one package run at once and not have them fighting over control of Excel as many of them use it on the local machine close to the beginning of their process. If I did not need to keep them from sharing Excel, I would just put all the packages into one package with no precedence constraints at all...
Thanks for the input and patience!
Tai
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply