Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Can SSIS tell a batch file to move on before the SSIS package completes? Expand / Collapse
Author
Message
Posted Thursday, August 1, 2013 10:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 21, 2014 5:36 AM
Points: 34, Visits: 146
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
Post #1480033
Posted Thursday, August 1, 2013 2:14 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 2:54 PM
Points: 248, Visits: 686
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.
Post #1480131
Posted Thursday, August 1, 2013 2:41 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 21, 2014 5:36 AM
Points: 34, Visits: 146
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
Post #1480146
Posted Thursday, August 1, 2013 2:52 PM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Today @ 12:20 PM
Points: 1,452, Visits: 8,249
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.




Alvin Ramard
Memphis PASS Chapter

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.
Post #1480152
Posted Thursday, August 1, 2013 2:57 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 21, 2014 5:36 AM
Points: 34, Visits: 146
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
Post #1480158
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse