SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Batch SSIS pkg execution from Business Intelligence Development Studio

By Stan Kulp,

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).

 
Total article views: 776 | Views in the last 30 days: 6
 
Related Articles
FORUM

SSIS Script component, Script Task and project References

How to reference a project in ssis Script component ?

FORUM

Reusable component in SSIS package

Reusable component in SSIS package

BLOG

Execute All Packages Within SSIS Project Catalog

The SSIS Project Deployment Model is great! It really is the best thing since sliced bread! I specif...

FORUM

SSIS Projects vs Packages

Is there a "best practice" for organizing packages and projects?

FORUM

Execute Package Task

Execute Package Task

 
Contribute