November 26, 2008 at 9:17 am
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)
April 1, 2009 at 6:04 am
any ideas welcome..........
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
April 2, 2009 at 8:05 am
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. 😉
April 2, 2009 at 9:37 am
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)
April 16, 2009 at 9:14 am
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)
April 16, 2009 at 10:37 am
have u tried using BIDS Helper
http://www.codeplex.com/bidshelper/Release/ProjectReleases.aspx?ReleaseId=9557
April 17, 2009 at 1:53 am
no, i'll check it.
Thanks
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
April 17, 2009 at 3:16 am
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)
April 17, 2009 at 4:26 am
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.
April 17, 2009 at 4:49 am
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)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply