SQLServerCentral Article

Batch SSIS pkg execution from Business Intelligence Development Studio

,

SSIS packages can be executed from Business Intelligence Development Studio (BIDS) one at a time, but this can be cumbersome when there are a lot of packages to run and data to import or process. SSIS package execution can be automated with the DTEXEC.exe command or the SSISDB catalog, but both these methods require the installation of Integration Services and the requisite license.

The Execute Package Task can be used to emulate a batch file in an SSIS package executed in BIDS, running a number of packages in a project in sequence. While the batch-execution package is running you can open another instance of BIDS and work on other projects as long as you don't open the project containing your running batch-execution package. It is doubtful that Microsoft supports running two instances of BIDS simultaneously, but it seems to work adequately as long as you don't open the same project in both instances.

NOTE: If you do inadvertently open the same project in both sessions BIDS will behave erratically, in which case you should reboot your workstation.

1. Create a new project

Open a new SSIS project in BIDS and change the name of the default package...

...to "BatchExecution.dtsx."

2. Add existing packages

Right-click on "SSIS Packages" in Solution Explorer and select "Add - Existing Package," then browse to the desired package. 

...and add it to the project.

Repeat for all the packages that you wish to include in the BatchExecution package.

3. Add and configure Execute Package Task components

Drag-and-drop an "Execute Package Task" component from the SSIS Toolbox to the Control Flow panel.

Double-click the "Execute Package Task" component to bring up the Execute Package Task Editor.

Change the name to "Execute LoadSalesData."

Select the Package node.

Select "LoadSalesData.dtsx" from the PackageNameFromProjectReference list and click the OK button.

The first Execute Packate Task components has been added to the BatchExecution package.

Add and configure Execute Package Task components for the remaining SSIS packages and connect them with appropriate precedence constraints.

Run the BatchExecution package to execute all the packages. 

4. Open another instance of Business Intelligence Development Studio

While the BatchExecution package runs you can open another BIDS session and continue working as long as you don't open the same project (it is doubtful that running multiple instances of BIDS is supported by Microsoft, but it seems to work adequately as long as you don't open the same project in both sessions).

Rate

4.5 (4)

You rated this post out of 5. Change rating

Share

Share

Rate

4.5 (4)

You rated this post out of 5. Change rating