Miscellaneous Ramblings on Hacking

October 30, 2006

Creating the Master Package

Filed under: ETL, SQL Server Integration Services, SSIS — Zack Bethem @ 9:37 pm

It’s come to that point where all of the SSIS packages for the data warehouse are complete. As other searches have pointed out, there are multiple ways to glue everything together. Coming from a DataStage or Informatica background, however, I prefer the creation of a “master package”. This package is strictly a control flow that utilizes several ‘Execute Package’ tasks.

Step 1: Define File Connection

For this implementation, all of our packages will exist as files. This requires that each package have a defined file connection within the master package. Under the Connection Manger, right click to add a new file connection for the following dialog prompt.


Click on Browse to add the package of choice in the following dialog.


After selecting the file, click on the File Name listbox. This will automatically highlight the filename. Copy the filename to the clipboard by pressing Ctrl-C. This will save time in the later steps.

Once the file connection is added, an expression will need to be defined for the ConnectionString property. In my installation, I defined a variable User::ExecDir_DWL that corresponds to the project folder location for all of my packages. This allows me to define the ConnectionString as follows (hint: use Ctrl-V to paste in the package name to save time and reduce errors). Once complete, this will ensure that the file connection is not tied to any specific location. In turn, this allows for the master package to be easily deployed to any folder structure.

Step 2: Add Execute Package Task

The final piece of the pie is to add the Execute Package task to the control flow. Here, I use the file name text copied to the clipboard earlier to save time.

  • First, rename the execute package task to ‘EXEC <package_name>‘.
  • Second, explicitly set the property PackageName using the expression builder. I’ve noticed that the package name isn’t set when using a package template. This should override that issue.

1 Comment »

  1. Bonjour,

    Nice to be visiting your blog again, it has been months for me. Well this article that i’ve been waited for so long.

    One suggestion from my side: insert Decision Tasks after each session; from the Decision Task there are two output links, one leading to the next session, one leading to the point after all those sessions to be skipped.
    This way you can use simpler conditions everywhere, making them easier to read and to understand.

    That is not strictly necessary, but at least it will make it easier for you to debug and test the link conditions.

    Very useful post !everyone should learn and use it during their learning path.



    Comment by Kevin Lee — March 30, 2018 @ 6:24 am

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

Blog at WordPress.com.

%d bloggers like this: