May 4, 2011 at 9:03 am
Hi
Based on my searches here I am not optimistic but here goes...
We deploy packages to SQL Server (msdb) using the deployment manifests. This has worked fine for years.
For the first time we have created a project containing Execute Package tasks. These tasks reference packages within the project by filename.
We want to deploy the project and all packages to SQL Server. This is incompatible with the references to filesystem locations.
In effect, Execute Package tasks require a particular physical set up at design and development time that is incompatible with one of the two deployment options available.
My question is therefore what options are now available to us to deploy a project containing Execute Package tasks to SQL Server?
Thanks
Dan
May 4, 2011 at 9:31 am
Whoops!
SQL Server 2005
BIDS 2005
May 5, 2011 at 12:03 am
This is how I do it:
design your child packages and deploy them to the server.
Design your parent packages, set up the Execute Package Tasks and point to the child packages on the server.
Deploy the parent packages to the server.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 5, 2011 at 1:13 am
Thanks Koen
We've considered that option and it's on the Plan B List.
Principle problems are:
* It is not possible to debug the project as a whole in that configuration.
* Each tier of nesting requires a further deployment.
May 5, 2011 at 2:43 am
Regarding the debugging as a whole problem:
SSIS doesn't really support debugging/building a project as a whole. It is just an illusion 🙂
In SSIS 2005 and 2008, the basic unit of work is a package. A project is a placeholder in Visual Studio, but it has no actual meaning in SSIS.
So I would develop a child package, test it, debug it, deploy it and then start on the parent package.
You can deploy the child packages to a test server, not necessarily to the production server.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 5, 2011 at 3:10 am
By debug I mean watch the little boxes turn green or red in BIDS 😉
My point is that if execution of a parent package highlights a problem with a child package then the whole process is a lot more involved if the child is in MSDB. Although deployment is to a dev\ test\ pre-prod environment, the child package still needs to be deployed for the development phase of the parent. If everything is in a project as a whole, simultaneous development is possible (however illusionary!).
SSIS is not my natural environment so please feel free to correct me if I'm getting the wrong end of any stick.
FWIW, we have just tested a POC of using a variable, expression evaluation in control of flow and TWO Execute Package tasks for each child. In BIDS, the filesystem Execute Package is executed. When deployed, the MSDB Execute Package is executed. The variable value determines which Execute Package task to run. Our developer proposed this and to my eyes it is the least fudgey fudge available.
May 5, 2011 at 3:19 am
Ah yes, property configurations. I forgot about them 😀
In the next version of SQL Server (Denali), this issue will go away. Handling Execute Package Tasks becomes a lot easier.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 5, 2011 at 6:03 am
Always the next version eh? The consumerist dream!
Given our deadline and success of the POC we have decided to push on. Thanks for the input though - it's very useful to get other peoples' experiences and thoughts 🙂
May 6, 2011 at 7:25 am
You can watch the progress of the child packages in BIDS if you set your Execute Package task to point to a local copy, which will open in BIDS during local execution. Once you've tested/confirmed everything is working in the child package, you can deploy it out to MSDB and change your PE task back to the server location (though you'll lose the ability to view its progress when the parent is executed in BIDS).
May 5, 2012 at 8:55 am
Koen Verbeeck (5/5/2011)
This is how I do it:design your child packages and deploy them to the server.
Design your parent packages, set up the Execute Package Tasks and point to the child packages on the server.
Deploy the parent packages to the server.
I have never used an Execute SSIS Package but I have used the Execute DTS Task in DTS quite frequently but it has changes quite a bit.
If I have deployed the child packages to MSDB what fo I specify in the connection in the Execute SSIS Task?
I need to store the start time that the parent package executed and reference it in derived column task to populate a modified_Date column for an incremental load.
I vaguely recall the syntax to reference a variable in the parent object.
Any ideas, suggestions or thoughts would be greatly apreciated.
Tienen un gusto quinto de Mayo."El DÃa de la Batalla de Puebla" 🙂
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 6, 2012 at 3:37 pm
Use package variables to control execution against a test or live server child packages
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 6, 2012 at 3:57 pm
I don't understand what I'm supposed to connect to.
I have a connection to SQL Server as the Destination, AS400 as the source and another SQL Server Database to for package loging.
The Execute DTS Package was a joke but I don't have a clue on EXEC SSIS Package.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply