Welcome to EMC Consulting Blogs Sign in | Join | Help

SSIS Junkie

SSIS: Common folder structure

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:

  • ...\Root\CheckpointFiles
  • ...\Root\Configurations
  • ...\Root\ErrorFiles
  • ...\Root\Logs
  • ...\Root\Packages
  • ...\Root\RawFiles

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:

  1. All of your SSIS items (packages, raw files etc...) exist in a known, consistent place.
  2. All items of a certain type (e.g. checkpoint files) are stored in the same place
  3. Allows for consistency across environments
  4. Make all locations dynamic and therefore ensuring that you keep your packages in the dark
  5. Using package templates means you don't have to set these things up for each new package
  6. Indirect configurations allow you to easily move the folder structure to somewhere else - perhaps to a different drive
  7. 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
  8. 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
  9. 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.

 

-Jamie

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.

-Jamie

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.

 

Comments

 

Jamie Thomson - Life, the universe and SSIS! said:

I thought it would be worth publishing a list of guidelines that I see as SSIS development best practices....
January 5, 2006 3:44 PM
 

Jamie Thomson - Life, the universe and SSIS! said:

I thought it would be worth publishing a list of guidelines that I see as SSIS development best practices....
January 5, 2006 6:52 PM
 

Jamie Thomson - Life, the universe and SSIS! said:

I thought it would be worth publishing a list of guidelines that I see as SSIS development best practices....
January 5, 2006 9:58 PM
 

Jason Harris said:

Jamie, I stumbed on your blog today because I was stumped by trying to launch the expression editor within a variable (which you've already addressed in another post).

I've really enjoyed your best-practice posts. Thanks for sharing with us.
January 6, 2006 2:43 AM
 

Jamie Thomson - Life, the universe and SSIS! said:

I thought it would be worth publishing a list of guidelines that I see as SSIS development best practices....
January 7, 2006 7:45 PM
 

Jamie Thomson - Life, the universe and SSIS! said:

I thought it would be worth publishing a list of guidelines that I see as SSIS development best practices....
January 7, 2006 8:01 PM
 

Jamie Thomson - Life, the universe and SSIS! said:

I thought it would be worth publishing a list of guidelines that I see as SSIS development best practices....
January 9, 2006 1:43 PM
 

Gerry Aue said:

Can you give other advantages of deploying as file structure rather than SQL Server. Is there any performance issues between one and the other?
January 18, 2006 3:25 PM
 

jamie.thomson said:

Gerry,
There is a reason here (http://blogs.conchango.com/jamiethomson/archive/2005/05/05/1382.aspx) but there are also more fundamental reasons. I'll try and document later.

-Jamie

January 19, 2006 1:30 PM
 

Jamie Thomson - Life, the universe and SSIS! said:

I thought it would be worth publishing a list of guidelines that I see as SSIS development best practices....
January 23, 2006 6:44 PM
 

Jamie Thomson - Life, the universe and SSIS! said:

I thought it would be worth publishing a list of guidelines that I see as SSIS development best practices....
February 17, 2006 11:46 PM
 

Jamie Thomson - Life, the universe and SSIS! said:

I thought it would be worth publishing a list of guidelines that I see as SSIS development best practices....
February 21, 2006 10:18 PM
 

David Norman said:

I'd be interested to know your thoughts about configurations when production and test environments reside on the same server. Environment variables won't cut it since both instances will go for the same variable.
February 22, 2006 5:12 AM
 

Keith Barrows - StarPilot said:

I've been searching around for info on SSIS and it is scarce! I've emailed quite a few...

November 8, 2006 4:43 PM
 

SSIS Junkie said:

I thought it would be worth publishing a list of guidelines that I see as SSIS development best practices.

November 8, 2006 9:52 PM
 

Gerald Aichholzer said:

Hi Jamie,

great blog entry (as always), but I don't get how I can use the variable [User::ConfigurationFileDirectory] (in your sample package) to access configuration files. The only options I can see are to specify an indirect configuration via an environment variable or to hard code the file name in the configuration dialog.

What I'd like to do is having an environment variable pointing to a general configuration file which is required by all dtsx-files in a project (e.g. storing all directories). So far no problem, but a dtsx-file can have some additional configuration options which should be saved in a configuration file called [User::ConfigurationFileDirectory]\(dtsx-file-name).dtsx.config. Is this possible?

If not, do you have some other ideas?

thanx a lot,

Gerald

January 26, 2007 11:02 AM
 

SSIS Junkie said:

Someone recently left a comment on my blog about Package Template Locations asking if I could share my

March 12, 2007 9:45 AM
 

Professional Association for SQL Server (PASS) SIG said:

April 4, 2007 3:00 PM
 

Antonio said:

Hi Jamie,

I have a question:

is possible to deploy a project following the structure you suggested?

I explain myself better: I have followed your advice, but when I am deploying the project all packages and config files go to the bin\Deployment folder; then when I execute the manifest it asks me one folder where putting all files (both packages and config files).

But my objective is to put the pkgs into \Pakages directory and the config files into \Configurations directory.

Is a manual operation of file movement necessary?

Thank you

August 6, 2007 3:23 PM
 

jamie.thomson said:

Antonio,

We have a different deployment strategy that allows us to deploy the folder structure. We use WIX (http://wix.sourceforge.net) to build installers that create the folder structure for us. I plan to expand on this in a blog entry sometime but it'll be a sizeable blog entry so I'm not committing to getting anything out soon I'm afraid.

-Jamie

August 6, 2007 5:54 PM
 

SSIS Junkie said:

Recently on this blog I mentioned in passing that I have been working for some considerable time now

August 6, 2007 8:57 PM
 

KM said:

hi Jamie,

it will be conveninent for me if u provide some more details about creating a package template and using it for all the dimension packages. (i.e)The link which you posted regarding template package with RootFolder variable (http://blogs.conchango.com/jamiethomson/archive/2005/11/12/2380.aspx ) is

not providing me ample details. so kindly provide me an example of this type with the location and accessing those common templates .

Thank you Waiting for your timely reply

September 11, 2007 10:44 AM
 

KM said:

hi Jamie.

I mean to say that it will be helpful for me, if u provide an example template with some variables stored in it. since i don't know in which format the variables are stored in what type of file whetherits a package or notepad file etc.,

Thank you

September 11, 2007 11:04 AM
 

jamie.thomson said:

KM,

There's a link in this blog entry (above) to an example package template.

-Jamie

September 11, 2007 4:22 PM
 

KM said:

Hi Jamie

Thank your for your kind reply

but after surfing the above link regarding package template,

only i send my doubts yesterday.

Yeah. In that i understood about where to save the template but i couldn't get what file format do i need to save there.

so kindly provide some details about creating template package and making it as basis for all packages.

It will be very helpful, if u provide some example projects which involves template packages

Thank you

September 12, 2007 5:38 AM
 

grossk said:

The common folder structure seems like a great idea, and I'm able to get it working with the help of your template.

What I'm still confused on is how to make use of the ..\root\configurations folder.  How do I make use of the RootFolder variable to reference the configuration files in ..\root\configurations

Thanks.

September 25, 2007 3:29 PM
 

jamie.thomson said:

grossk,

You can't use RootFolder variable to reference configurations. If you think about it this makes sense. RootFolder is set using a configuration so you can't set a configuration using RootFolder.

Configurations always happen first. In your scenario I recommend the use of indirect configurations (http://blogs.conchango.com/jamiethomson/archive/2005/11/02/SSIS_3A00_-Indirect-configurations-ROCK_2100_.aspx) - that's what I always use.

-Jamie

September 25, 2007 4:09 PM
 

grossk said:

Jamie,

I really want to use indirect configurations, but I'm struggling with the mechanics.

What I want to do is have a central network location for dev/test/prod configurations and then have the machine be smart enough to point at the right ones depending on what kind of server it is (dev/test/prod).

So, say I have a \\corpfileshare\dev\config folder and a corresponding one for test and prod.  Then say I have two hundred packages with maybe 30 or so different databases referenced which I'll need connection strings for.  If I want 1 xml file per connection, do I need 30 environment variables on each IS server?

Thanks again.

September 25, 2007 4:26 PM
 

jamie.thomson said:

grossk,

Yes you need 1 env var per config file per environment. You could of course put all configurations into a single file.

-Jamie

September 25, 2007 6:38 PM
 

SSIS Junkie said:

Someone recently asked me if it was possible to dynamically set the location of a checkpoint file. Happily

November 15, 2007 8:46 PM
 

Cristof said:

Hi. This is very helpful indeed at 2 locations so far. I did have a question. Using the root folder env var, is there a way to make the configurations dynamic? With checkpoints it is easy because you have access to expressions to concatenate the variable holding the env var with static text or the package name variable or whatever. But configs don't seem to have the concept of this. I thought maybe you could do it in code, but I thought that configurations are loaded before a package is evaluated.

Thanks!

December 15, 2007 2:51 PM
 

Cristof said:

Please ignore my last request; I see earlier in the page that it cannot be done.

December 15, 2007 3:38 PM
 

Nishi Modgil said:

How can we get the path for the package

and how to rerun it again after setting the path for config file  from..\ROOT\CONFIGURATIONS folder

January 14, 2008 9:08 AM
 

jamie.thomson said:

Nishi,

Sorry, I don't understand the questions. Can you rephrase?

-Jamie

January 14, 2008 2:05 PM
 

FrenchToast said:

Jamie, I went through many posts and I'm still having a question. I hope you can answer: How do you dynamically set the location of a package configuration xml file?

I have 10 packages with 10 Package Config XML files. The config files are all located in ONE folder.

I have an extra Package Config File called master.tdsconfig which contains the "ConfigFolder" variable set to the PATH of my 10 Config XML files.

Each of my packages uses an indirect configuration to populate the "ConfigFolder" variable.

In each of my 10 packages how do I dynamically set the location of its respective config files?  I was hoping to use something like: @[User::ConfigFolder] + "\\package1.dtsconfig"

Thanks and keep on!

April 10, 2008 5:26 PM
 

jamie.thomson said:

FrenchToast,

Simple answer, the location of a configuration file cannot be set dynamically using an expression.

There are other options available to you, such as using the /CONF option of dtexec to pass in config file locations.

-Jamie

April 10, 2008 5:33 PM
 

SSIS Junkie said:

SSIS uses absolute paths to point to objects on the file system. What does that mean? In short it means

May 4, 2008 8:19 PM
New Comments to this post are disabled

This Blog

Syndication

Powered by Community Server (Personal Edition), by Telligent Systems