For reasons that I'll save until another post, I always deploy my packages as files rather than to SQL Server.
In doing this I have derived a folder structure that I use in order to keep all my deployed packages and other files in the same place:
All my packages are stored in the ...\Root\Packages folder. All checkpoint files, log files, raw files, error files and configuration files are stored in the appropriate folder.
Each of my packages has a variable called "RootFolder". [The way I ensure that all my packages have this variable is to make sure my template package has it in] which points at ...\Root.
Each of my packages uses an indirect configuration to populate the "RootFolder" variable. Again, this indirect configuration is in my template.
I then use an expression to dynamically set the location of packages, raw files, logs and checkpoint files to be based on the "RootFolder" variable.
There are a number of advantages to this approach:
- All of your SSIS items (packages, raw files etc...) exist in a known, consistent place.
- All items of a certain type (e.g. checkpoint files) are stored in the same place
- Allows for consistency across environments
- Make all locations dynamic and therefore ensuring that you keep your packages in the dark
- Using package templates means you don't have to set these things up for each new package
- Indirect configurations allow you to easily move the folder structure to somewhere else - perhaps to a different drive
- Dynamically setting the names of checkpoint files, raw files, log files and error files means that you can easily include the value in the "System::PackageName" variable so you know from which package all of your files were created
- Using expressions to set the name of your log file means that you can include the value in "System::StartTime" or "System::ExecutionInstanceGUID" therefore creating a new log for each execution
- You know where all your packages are when using the Execute Package Task
I highly recommend employing this approach when building and deploying packages.
For more of my suggested Best Practices go here.
UPDATE: 20th July 2006 - Todd Manson has contacted me and queried what I have said abut a common folder structure. He says he can't get BIDS to allow him to put a package into the Packages subfolder in his folder structure - everything is stored in the root of the project..
It made me realise that this blog post needs a bit more clarification. The folder structure that I have suggested here is to be used when you deploy your packages, it is not where you store them during development.
Having said that, Todd's question is still very legitimate. You still have to reference the folder structure at development-time and there will more often than not be a requirement to use the Execute Package Task to call packages that are stored in your common folder structure. Hence, there is a necassary manual step of having to move packages from your project into your folder structure. This is most definately a caveat to the common folder structure approach but I am firmly of the opinion that the advantage of having everything stored in one place far outweighs the disadvantage of this manual deployment step during development.
UPDATE 18th August 2006 - After popular demand I have a placed a package that demos this technique here. Its very simple to set up, contains setup notes in the actual package that are visible when you open it in BIDS, and also contains a sample configuration file.