Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Execute Package Task


Execute Package Task

Author
Message
dave-dj
dave-dj
SSChasing Mays
SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)

Group: General Forum Members
Points: 610 Visits: 1149
Could someone give me some pointers please

I'm developing a data warehouse project.

What is the best practice for calling subsequent packages? I know that it's best to compartmentalise the process into logical work - ie. update a dimension etc, but I'm a little lost as how this is best done, when it comes to deployment.

I've developed a LoadDimensions package that executes other packages - such as LoadCustDim, LoadProdDim etc etc.


what I have found when defining the package to execute though is

1) if I use File System, and refer to my local project copy, then when it's deployed, it will refer to that source package copy, not the deployed copy - which is what I want it to do.

2) if I want to reference the packages in the deployed environment, stored in the msdb, then I have to first deploy the LoadCustDim, LoadProdDim etc etc, and then I can deploy the LoadDimension package.

My question is, can I configure the package reference to be relative, to the local calling package??

-----Crazy I'm going to be moving across to SQL2005 and SSIS very soon now, so I would really appreciate some help and advice on how this is tackled with a live and development environment. I'd like to hear how other people handle this as to me, it all seems a little disjointed and clumsy.

_____________________________________________________________________________
MCITP: Business Intelligence Developer (2005)
dave-dj
dave-dj
SSChasing Mays
SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)

Group: General Forum Members
Points: 610 Visits: 1149
any ideas welcome..........

_____________________________________________________________________________
MCITP: Business Intelligence Developer (2005)
Timothy J Hartford
Timothy J Hartford
SSC Veteran
SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)

Group: General Forum Members
Points: 288 Visits: 502
I'll give it a try... though I am by no means an expert in SSIS.

I believe that the easiest way to use relative paths is to wrap the path in a variable. Not ideal, as you will have to fill this in when you call the package, but it would work. However, I prefer to stored B and C in msdb and then develop A. Yes, it makes development a hassle, but it makes it simpler to make changes. I would also suggest adding some sort of a variable for the DB server instance so that you can call from production or dev, and pass this variable to any child packages. Or, as an alternative, you can code everything into a single SSIS package that calls instances of itself. I have done this with a dependency based data load that has more than 100 unique objects built from it, and while complicated it seems to work well.

Anyone else, please feel free to correct me if I am leading him astray. He did ask for "best practices", and all I can offer is my practices. Wink
dave-dj
dave-dj
SSChasing Mays
SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)

Group: General Forum Members
Points: 610 Visits: 1149
thanks for the reply Tim.

I quite like the sound of defining a variable and setting that at run time. w00t I think I will investigate using a variable further. I can see that working quite well be it with package stored in an msdb on a local dev machine and then deployed to a server, or with packages reference by files paths.

The main aim of what I am trying to acheive is to be able to develop packages A,B & C locally all at once and then deploy to the server using the deployment manifest, without the need for deploying packages B & C and then developing and deploying package A.

By the sounds of it I'm not too far off. It's not something that is really covered in the MCTS training kit! I just want to start off down the correct path, especially as I'm likely to start doing a lot of work in this area and I don't want to develop something that is fundementally wrong in it's process.

Thanks Tim, I really appreciate the response

- any other ideas would also be useful to hear.......

_____________________________________________________________________________
MCITP: Business Intelligence Developer (2005)
dave-dj
dave-dj
SSChasing Mays
SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)

Group: General Forum Members
Points: 610 Visits: 1149
Ok.

I tried taking the approach of setting a variable string to try and dynamically set the path and file name (I could see a variable for the name), the I used something like
@[PackageFileLocation] + "\\PackageA.dtsx" 

, but BIDS just kept throwing errors.

So i've taken a step back I am looking at using the package configuration option.

This would work in terms of setting the path in a live and development enviroment, but main issue I can see here is that probably have to change the file locations in the configuration table (i'll be using an sql configuration table) when new packages are published to a live enviroment. of course this will mean amending all new package connections strings in the live enviroment, after the first run, or copy from dev to set them up and then amend?

Is this the best way of doing this ??? This just all seems way more complicated than it should be in this day and age?

_____________________________________________________________________________
MCITP: Business Intelligence Developer (2005)
vishal.gamji
vishal.gamji
SSChasing Mays
SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)SSChasing Mays (618 reputation)

Group: General Forum Members
Points: 618 Visits: 532
have u tried using BIDS Helper

http://www.codeplex.com/bidshelper/Release/ProjectReleases.aspx?ReleaseId=9557
dave-dj
dave-dj
SSChasing Mays
SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)

Group: General Forum Members
Points: 610 Visits: 1149
no, i'll check it.

Thanks

_____________________________________________________________________________
MCITP: Business Intelligence Developer (2005)
dave-dj
dave-dj
SSChasing Mays
SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)

Group: General Forum Members
Points: 610 Visits: 1149
Hi, thanks for that. It looks like a useful tool and i've also taken a look at a publish from Artis Consulting (http://www.artis consulting.com/blogs/greggalloway/Lists/Posts/Post.aspx?ID=15) on how they publish packages, which seems to predominately use xml configuration files.

It all seems to be a case of what way you want to bodge it.

It's quite disappointing really from a product that in other areas, performs so well and yet in areas of deployment it lets itself down so badly, with something that really isn't new functionality.

I'm starting to understand why so few people have a made comment here........


Personally, I think I might stick the using SQL configuration tables to set the package path in the live environment, when using File Systems Execute Package Tasks.

If using an SQL MSDB as your PROD package store though with the original scenario of Package C calling Packages A & B, then it would seem that you have no alternative but to develop and publish A & B, deploy these and then develop and deploy package C - albeit the BI Helper looks like it would help in the process as you could singularly deploy a package as opposed to just the whole project.

_____________________________________________________________________________
MCITP: Business Intelligence Developer (2005)
P Jones
P Jones
SSC Eights!
SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)

Group: General Forum Members
Points: 814 Visits: 1510
As a different tack, is there any reason they have to be seperate packages? I have found that the superior structure of SSIS with sequence containers allows multiple sql 2000 dts packages to be successfully combined into one sql 2005 SSIS package.
dave-dj
dave-dj
SSChasing Mays
SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)SSChasing Mays (610 reputation)

Group: General Forum Members
Points: 610 Visits: 1149
Well basically I agree with the methodology breaking a project into logical work units,

such as build Customer Dimension, build CustomerSales fact table.

Some of our DW project on SQL2000 are now quite sizable, and I hate the idea of putting everything into 1 package and then having to debug it. Additionally, packages can be worked on seperately etc

_____________________________________________________________________________
MCITP: Business Intelligence Developer (2005)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search