THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

The new Execute Package Task in SSIS in Denali

In my blog post of 10th November Introduction to SSIS projects in Denali I spoke of how, in Denali, SSIS packages can collectively be distributed as a single unit called a Project. I want to start digging into the advantages that Projects bring and the first such example is the new Execute Package Task. If we compare the old and new then the change should be apparent. First the old (from SSIS2008):

image

and now the new:

image

We have a new property called ReferenceType. Selecting “External Reference” for that property will switch you back into the old SSIS2008 style of working so let’s ignore that, “Project Reference” is where the new stuff lies. Selecting “Project Reference” allows us to choose another package from the same project:

image

Notice how the dropdown list of available packages is the same as the list of packages in the project. The is a tangible benefit of the move to the Project deployment model; I don’t have to concern myself with setting up a connection string for the package that I want to execute because its distributed in the same Project and I can trust that it will be there. Anyone that is used to fiddling with configurations and connection strings in SSIS today should immediately recognise this benefit that Projects bring. It does raise a question though – how do I execute a package that is in another project? Right now I don’t think there’s a good answer to that and it does raise a question mark over reusability – how do I reuse a package in multiple places should I need to? I guess with one hand they give us much, with the other they take a little away – it remains to be seen whether this actually becomes problematic or not!

 

For those reading this and my previous two posts you could be forgiven for thinking that moving to Denali means you’re going to have to change all of your packages to use this new-fangled Project deployment model. Fear not, the old model of deploying packages on a file by file basis (its called the Legacy Deployment Model) is still totally supported and you can still develop this way too. For folks that wish to do that the new Execute Package Task has something for you too and it involves using the new Package Parameters feature; in a nutshell you can now push values into a package when you execute it rather than rather than having the executed package reach out into the calling package using Parent Package Configurations. This is immeasurably better than the stupid old method where the called package had to know about the Variables in the package that was calling it. Here’s the Parameter bindings tab that enables this:

image

The task has interrogated the package that was selected in the Package tab to find that package’s list of parameters {ChildPackageCustomerId,NewParameterName} and it lists them in the Child package parameter dropdown; thereafter you can assign any value to it from any variable or parameter in the calling package and as you’d expect a dropdown list is available for you to pick from. To reiterate what’s going on here, we’re pushing a value into the package – true parameterization.

 

Other than those two features everything is pretty much the same as it was before and I say that to make the point that the core features of SSIS (tasks, dataflows, components) haven’t really changed other than leveraging these new projects and parameters. I think that’s important because if you’re already familiar with SSIS there isn’t much to learn here – its just new ways of managing a SSIS implementation rather than new ways of building packages.

More to come soon!

@Jamiet

Published Friday, November 12, 2010 7:41 PM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Devin Knight said:

This is great stuff.  Keep it coming, especially with the side by side comparisons.  

November 12, 2010 2:20 PM
 

jamiet said:

No problem Devin, good to get the feedback too, thanks.

November 12, 2010 2:38 PM
 

SSIS Junkie said:

In the last few days I have written 3 blog posts covering some new features in SQL Server Integration

November 13, 2010 1:50 PM
 

Skytte said:

This is good stuff Jamie and I agree with Devin. Side by Side Comps FTW.

November 24, 2010 6:29 PM
 

SSIS Junkie said:

Earlier today I stumbled upon a blog post that I wrote back in 2008 entitled Absolute and relative paths

March 25, 2011 8:01 AM
 

Geoff Speare said:

As you mention, executing a package from a separate project could be problematic. For example, I've used ETL frameworks where all ETL packages are executed through a single "wrapper" package which handles logging, DQ, etc. This single package lives in its own project (as it shared by numerous ETL projects)...how would this work in Denali?

May 3, 2011 10:58 AM
 

jamiet said:

Geoff,

My understanding is that you will not be able to do this. If you feel that this is going to be a problem for you then you should feed that back at https://connect.microsoft.com/sqlserver

Regards

Jamiet

May 3, 2011 11:03 AM
 

Guido Ritzen said:

An option can be to make a deployment project where you put all your developed and tested packages and then built your Master/Wrapper package. Not the ideal option, but a good workaround I think.

Regards,

Guido Ritzen

June 14, 2011 3:01 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement