SQL University– SSIS 2012: Deploying Projects –Lesson One

Lesson One, where I introduce tons of prerequisite new concepts in preparation for Deploying and Executing in Lesson Two.  The main concepts and terms covered in this lesson are:

  1. Projects
  2. Project Deployment Model
  3. Legacy Deployment Model
  4. Project Reference reference type –Execute Package Task
  5. Ispac files
  6. Parameters, subdivided into the two following subtypes:
  • Project  Parameters
  • Package Parameters

It will help if you are familiar with the following concepts inside SSIS (pre 2012 versions) before attempting this course 😉

  1. Execute SQL Task
  2. Execute Package Task
  3. Connection Managers
  4. Variables
  5. Parent/Child packages

SSIS in 2012 brings a great number of significant changes.  It is the first version since its initial release with SQL Server 2005 to look significantly different.  There is a more modern look and feel to be sure.  However, the really big change to SSIS in 2012 is in the way in which it is deployed.  There is a new method of deployment called “Project Deployment”, which has as much to do with how and where a package is deployed as it does with how a package is bundled during development.  Let me go back in time a bit and explain.

Too far back. –Don’t ever do that again.

Ok.  Recall that for pre- 2012 versions of SSIS, no matter how nicely grouped in solutions or projects your packages are, when it came time to deploy a package, that package was atomic.  Its connection managers went with it and it had no dependencies on other elements inside the project (other than parent package variables)?  Well prepare yourself for a paradigm shift–that all changes with the Project Deployment model.    The Project is now the main unit in SSIS development.   SSIS Projects now get built.  When they are built they create a new file type called an ispac file and this is what is now deployed.

Ice Pack?  No, ispac 

(just a note/disclaimer here to the gnarled, grizzly “old school” SSIS Developers who have templates, frameworks, and methodologies all built around the “old” way of doing things– you can still develop in SSIS the old fashioned way!  Just convert your projects to the Legacy Deployment model by right clicking on the project in Solution Explorer and selecting “convert to legacy deployment model).  In fact in large team environments with staggered development and staggered deployment, I think the legacy model is still the way to go.

Stay Calm! You can still design in Legacy Deployment Mode!  All is Well!

The shift to projects introduces some new objects and some changes to familiar objects.  Connection Managers can now be created as Shared Connection Managers at the Project level.  However, the truly paradigm shifting new object s are Parameters.

Fun Facts about Parameters

  1. They behave like variables, differing in scope and exposure to change.
  2. They are located in tabs in the SSIS designer.
  3. They are the Project Deployment replacement for Configurations in Legacy Deployment Mode.
  4. They come in two flavors– Project parameters are viewed by right clicking on the project and select Project Parameters and Package parameters are made visible from the SSIS menu.
  5. Their values cannot change during the execution of a package.
  6. Their values can be changed IN BETWEEN Package executions,   as in –when a Parent Package calls a Child Package.
  7. Their Values can be set from the SSIS Catalog, inside Executions–(more on both of those terms in Lesson 2.)
  8. Inside Expression Builder, they are preceded with a dollar sign $ and then Project or Package, as the case may be.

Project Parameters, Shared Connection Managers

A look inside Expression Builder with Parameters added to the mix.

I know what you’re probably thinking–  Why aren’t you working on your Reporting Services Chapter Julie?  — No?  Just me wondering that? — Ok, what you are probably really asking is:  what is the difference between a package parameter and a project parameter?  The difference is in scope of course,  and it is great to have Project Parameters which can be consumed by all the packages inside a project, but that still didn’t give me a grasp on why Microsoft bothered with Package Parameters. Why not just use a variable, since it too can be scoped to the Package.   Then I started playing with the new Execute Package task, and the reason for Package Parameters became clear.

The new Execute Package task, its role in the Project Deployment Model,  and how it consumes Package Parameters

Now that the basic unit of SSIS is the Project, there are a few onerous tasks we as ETL developers will no longer have to perform.  For instance, it used to be that the only way to configure an Execute Package task with the location of a child package was by supplying it through a connection manager.  Not so anymore.  Now by virtue of the fact that a package resides within the same project, an Execute Package task will be able to execute it without supplying a connection manager.  The Execute Package task will use the Project Reference reference type and just know about all the packages which the parent could call.  No connection manager required!

Look Ma!  No Connection Managers.

Execute Package tasks in the new model are used to assign values to Package Parameters, usually directly from the value of a Variable (but it can be from a variable or a parameter).   The familiar design pattern I used to learn how this works and why there are Package Parameters was this:

Let’s say I use an auditing framework and record the execution of every parent package, along with all of the child packages that parent package calls.  I would have a table which would give me a key value column for an ExecutionID and each row would have a column for that id and a ParentExecutionID.  Parent Packages would have NULL as the value for ParentExecutionID.

As a row gets added for each new parent package being executed, I would use an Execute SQL task to stuff the value of the Parent’s ExecutionID into a variable.  Then when the Parent Package got to an Execute Package Task, I would take the value of that variable and pass it to a Package Parameter inside the Child Package.  This is done on the Parameter Bindings tab of the Execute Package task.   That way when the child package wrote its auditing details, it would not only have its ExecutionID, but the value of its Parent Package’s ExecutionID as well.  Only Package Parameters are able to receive a value during an Execute Package task, and that would be the Major Difference between Project and Package Parameters.

This shot shows the Parameter bindings page of an Execute Package task.  The value of the Child.dtsx package’s Package Parameter named ParentExecutionID is being supplied by the value of the Parent.dtsx package’s variable named ExecutionID.

(The typical way to do this with Pre-2012 versions of SSIS was to use Parent Package Variables inside Configurations.)

Ok kids.  Great work today.  See you Thursday when we’ll take all that we learned and use it to execute packages!  Be sure to check out other lessons at SQL University

For the rest of this series please enjoy:

6 thoughts on “SQL University– SSIS 2012: Deploying Projects –Lesson One

  1. So, this all makes perfect sense and works beautifully except…package configurations also allowed us to update the parent variables. Not so with package parameters. So it begs a massive question – is there a new way to update parent variables from a child or are we still stuck with package configurations? This is urgent question for me at this moment and I can’t find anyone talking about it yet. Web is full of the “old way” of doing parent variables using package configurations.

    • Hi Gary, I didn’t know the answer to your question so I asked around. I don’t think what you’re asking can be done in 2012 with the project deployment model, so I think yes you would be stuck with using legacy configurations. If I find that I’m wrong in that answer, I’ll post it.

      • I have the answer – all parent variables are automatically available in child packages without package configurations! You merely reference them the way you always did but simply no longer need package configurations. Just manually type them into task parameters and refer to them just as before. Of course if you only need to read them, the clearest way is to use project or package parameters but you can’t update those so you’re still stuck referencing parent variables if you want to update them from a child package. Just remember to name the parent variables differently than the child ones (I use “parent_…” and “child…” and then “parent_child_…” ones in the parent for ones that get updated from the child packages.

Leave a Reply