Pages

January 31, 2012

SSIS 2012: Better Execute Package Task

Execute Package Task had some rough edges in previous versions of SSIS. to name a few, you had to create a connection manager to be used by each task/package, and there was not a simply way to parameterize the connection managers all at once. Passing values from parent to children packages was something that did not have a good story either.
image
A plethora of connection managers in the old days...

What has changed in SSIS 2012?

With SSIS 2012 when you use the new project deployment model, the Execute package task is now easier to setup and configure:
For one, we just need to select the name of the package – any package within the current project – from a drop down list, as we will save quite a few clicks a we don’t need to create a configuration manager each time.
image
Point and click

Second, we have the ability to bind the parameters of the child package to variable or parameters in the parent package, and once again, just by selecting the appropriate values from the dropdown lists.

image
Nice: The execute package task 'sees' the parameters defined in the child package
Third, there are no connection managers to maintain and configure.

Now, remember these benefits are only available when using the new project deployment model.

Posted by

8 comments:

  1. Pedro12/3/12 2:54 PM

    Rafael,
    In the old days as you refer "A plethora of connection managers in the old days..." normally those connections to packages are stored inside a table and called through a ForEachLoop Task in a master package.
    But I agree... now is more simple using the new approach!
    Regards,
    Pedro

    ReplyDelete
    Replies
    1. Rafa Salas12/3/12 9:42 PM

      Pedro, Good call. Yes, some people store children package metadata in a table and use a for each loop conatiner to execute them. I don't use that approach that often as I don't like to give away the ability to execute multiple packages concurrently.

      Delete
    2. Pedro13/3/12 8:20 AM

      Dear Rafael,
      I completly understand you.
      regards,
      Pedro

      PS: Also thanks for helping me and reza on the book!!:-)

      Delete
    3. paeren2/5/13 8:40 AM

      but the child packages is not logged in [SSISDB].[catalog].[executions]
      kind regards Paeren

      Delete
    4. Rafa Salas6/5/13 9:38 PM

      Paeren,
      Thanks for your comment. That's correct. To get that information you need to query [catalog].[executable_statistics] and [catalog].[executables]

      Delete
    5. Unknown26/2/15 6:20 AM

      Hey Rafael,
      is there any smart way to execute sub packages in the main package?

      Delete
    6. Unknown26/2/15 6:22 AM

      i have around 60 sub packages so can you share any idea for this?

      Delete
  2. Unknown10/7/14 4:02 PM

    Dear Rafael,

    Thanks for this valuable information. I wanted to ask you that if we pass a variable from parent package to a child package, then is it possible to capture the value of that variable in SSISDB after the process has been executed ?

    Basically I am passing a batch_number value through to different packages contained in a sequence container and I am not able to find this variable's logged information after the process has been executed.

    Please let me know.

    Thanks

    Asad

    ReplyDelete

I will love to hear your feedback and comments. Thanks.

Subscribe to: Post Comments (Atom)