Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Execute Package Task Expand / Collapse
Author
Message
Posted Wednesday, November 26, 2008 9:17 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 7:50 AM
Points: 404, Visits: 1,124
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??

----- 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)
Post #609227
Posted Wednesday, April 01, 2009 6:04 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 7:50 AM
Points: 404, Visits: 1,124
any ideas welcome..........

_____________________________________________________________________________
MCITP: Business Intelligence Developer (2005)
Post #687804
Posted Thursday, April 02, 2009 8:05 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 22, 2012 8:02 AM
Points: 242, 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. ;)
Post #688927
Posted Thursday, April 02, 2009 9:37 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 7:50 AM
Points: 404, Visits: 1,124
thanks for the reply Tim.

I quite like the sound of defining a variable and setting that at run time. 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)
Post #689068
Posted Thursday, April 16, 2009 9:14 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 7:50 AM
Points: 404, Visits: 1,124
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)
Post #698574
Posted Thursday, April 16, 2009 10:37 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, March 16, 2014 10:26 AM
Points: 536, Visits: 516
have u tried using BIDS Helper

http://www.codeplex.com/bidshelper/Release/ProjectReleases.aspx?ReleaseId=9557
Post #698673
Posted Friday, April 17, 2009 1:53 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 7:50 AM
Points: 404, Visits: 1,124
no, i'll check it.

Thanks


_____________________________________________________________________________
MCITP: Business Intelligence Developer (2005)
Post #699172
Posted Friday, April 17, 2009 3:16 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 7:50 AM
Points: 404, Visits: 1,124
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)
Post #699221
Posted Friday, April 17, 2009 4:26 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, April 17, 2014 8:20 AM
Points: 547, Visits: 1,126
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.
Post #699262
Posted Friday, April 17, 2009 4:49 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 7:50 AM
Points: 404, Visits: 1,124
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)
Post #699272
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse