November 1, 2010 at 9:38 am
Hi,
Does anyone know if it is possible to create an Integration Services Project with multiple SSIS Packages and have a master package within the project that executes the child packages in a specific sequence? If there's a posting or tutorial on how to do this that would be helpful.
Thanks in advance!
November 1, 2010 at 9:40 am
http://sqlblog.com/blogs/eric_johnson/archive/2010/10/28/calling-child-packages-in-ssis.aspx
---------------------------------------------------------------------------------------
It begins by taking the first step.
November 1, 2010 at 9:44 am
Hi and thanks for the response, but that's not what I'm after. What I want to do is create a container of multiple packages within my Integration Services Project, and have that container run the packages sequentially. I know I can use the solution you provided, it just wasn't what I was looking for. Anyone else have ideas how to accomplish this, or does the functionality not exist?
November 1, 2010 at 10:13 am
sackmeister (11/1/2010)
Hi,Does anyone know if it is possible to create an Integration Services Project with multiple SSIS Packages and have a master package within the project that executes the child packages in a specific sequence? If there's a posting or tutorial on how to do this that would be helpful.
Thanks in advance!
When you create a project with multiple packages, there is no such thing as a master package.
If you want a master package to do what you're asking then you have to design that package yourself. BIDS will not do this for you.
In one of your packages, call it master if you want, include the required tasks to execute the other packages. Set up the precedence contraints to execute the other packages in the order you want.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
November 3, 2010 at 9:27 am
I use master packages in virtually all of my projects. You need to create your master package and then use the Execute Package task that will call of your child packages. You'll need to setup a connection manager for each of the child package that it calls. Then it's just as simple as ordering how they are called in the order that you want. You can even pass parameters from Parent(Master) to Child.
November 3, 2010 at 3:17 pm
Can you post an example? I'd like to see how you are implementing it.
December 7, 2010 at 6:31 am
You'll need to setup a connection manager for each of the child package that it calls. ....... You can even pass parameters from Parent(Master) to Child.
This would be very useful to me, too. In particular, 1) the specifics of how the Execute Package Task is configured to point to another package within the same project, and 2) how parameters are passed within that context.
Does the project need to be built before Child Package exists to be referenced? I'm confused because the options I see only provide for a child package in SQL Server, or on the file system. I've coded my Child Package, but I haven't built the project yet.
December 7, 2010 at 8:14 am
DaRube (12/7/2010)
You'll need to setup a connection manager for each of the child package that it calls. ....... You can even pass parameters from Parent(Master) to Child.
This would be very useful to me, too. In particular, 1) the specifics of how the Execute Package Task is configured to point to another package within the same project, and 2) how parameters are passed within that context.
Does the project need to be built before Child Package exists to be referenced? I'm confused because the options I see only provide for a child package in SQL Server, or on the file system. I've coded my Child Package, but I haven't built the project yet.
The child package needs to exist before you can configure the execute package task.
I the typical setup, you do not pass parameters from parent to child. The child read the value from the parent.
In the child package, you set a configuration using a parent package variable that will pull a parameter (variable) from the parent.
If you need more information, try Google. There's lot of information out there.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
December 13, 2010 at 7:40 am
sackmeister / DaRube
When I first looked at calling packages - it's pretty straight forward. specify if it's stored in SQL or on the file system.
That simplistic view has it's issues though!
If like me you are developing on machine and need to deploy on machine B, you can end up in all sorts of a mess - having to make sure you've deployed the child packages before you deploy the parent or making sure the packages are stored in the same file path etc etc.
I hate to say it, but this can be more involve than at first it seems. What I ended up doing was using the approach of package variables, one of which was is a ssis-package-store file path, using the file system of course. (i won't go into the detail of where you store this variable as there are also a number of options there..... xml, SQL server, parent package variable, enviroment variable etc)
When I define an Execute Package Task, I use an expression in the connection manager for the Execute Package Task. That means if for example, I store the package file path in an enviroment variable on Machine A and machine B, I can then use that variable valueto define where the path is.
The benefit of this is that on machine A my path could be 'C:\SSIS_Packages' and on Machine B it could be 'F:\something\Somewhere\', but when you deploy the packages, you would not have to change the paths for each connection manager.
Equally, if you decide at a later date 'F:\something\Somewhere\' is not suitbale, then you just change the enviroment variable on machine B to point to the new location and more the packages to the new location. - no rebuilding of packages or redeployment would be required.
That said - you'll need to see what is best for you in terms of where you store that variable. Personally, I'm converting a data warehouse to SQL 2005 and using a similiar process to that explained in MS Project Real, so I use an enviroment variable to connect to SQL server then pick up my file path from an admin.config table that stores the file path and other package related parameters.
Hope that helps you. - good luck !
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
December 13, 2010 at 8:15 am
Expressions, combined with configurations, are a good way to deal with the changes in directory structure when going from one machine to another.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply