Flexible File System Deployment

This blog post has been sitting in my drafts folder since the day I created this blog, and has been kicking around in my mind for much, much longer. The real world just keeps getting in the way of my best intentions to blog… Anyway, a thread on the MSDN SSIS Forums today inspired me to get off my hard-working butt and actually get it finished.

So, you want to deploy your SSIS packages to the file system, but also want to be able to deploy them to any path on your test and production servers, not just in the folder that you used in development? You've come to the right place.

The two primary tools that make this possible are SSIS Package Configurations and SSIS Expressions. [1] Here's what you need to do:

1. Create a string variable named DeploymentFolderAbsolutePath. [2]
   
2. Assign the DeploymentFolderAbsolutePath variable a value to reference the absolute path of the root folder of your SSIS Visual Studio [3] solution directory, such as "C:\Projects\ProjectName\SSIS\". Remember to include the trailing backslash character.
   
3. Create a string variable named ProjectFolderRelativePath.
   
4. Assign the ProjectFolderRelativePath variable a value to reference the name of the current project SSIS Visual Studio project directory, such as "ERP.Extract\".Remember to include the trailing backslash character.
   
5. Create a string variable named ProjectFolderCalculatedPath. Set the EvaluateAsExpression property of the ProjectFolderCalculatedPath variable to true and set the Expression property of the ProjectFolderCalculatedPath variable to "@[User::DeploymentFolderAbsolutePath] + @[User::ProjectFolderRelativePath]". This will evaluate to the full path to your project directory, including the trailing backslash – something like "C:\Projects\ProjectName\SSIS\ ERP.Extract\".
   
6. Create a Package Configuration that configures the value of the DeploymentFolderAbsolutePath variable. I personally prefer using an environment variable configuration for this variable – you can script it with a REG file that looks like this:
   

   REGEDIT4
   

   [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Environment]
   

   "SSIS_Root_Deployment_Path"="C:\\Projects\\ProjectName\\SSIS\\"
   

7. At this point, you now have a variable that contains the fully qualified path to the folder that contains your SSIS package. But why did we have to jump through so many hoops? Because we're going to use this over and over and over again. Save this package for use as a template for other packages.
   
8. Now, whenever you need to access any file system-based resources, such as flat files or file connection managers used to locate child packages for use by Execute Package tasks, you have a starting point on the file system that moves whenever your deployment folder moves. For example, you can use an expression for the ConnectionString property of a file connection manager to identify the child package, like "@[User::ProjectFolderCalculatedPath] + "ERP_Extract_Stuff.dtsx"".
   

If you're like me, you design your SSIS projects to have a single "master" package that drives the execution specific to a business process, and a set of "child" or "worker" packages that are called by the master. In this scenario, having this flexible file system deployment framework makes it trivial to manage.

But what do you need to do to make this work?

First and foremost, test and play in your own environment to find out what works best for you. Once you've done this, save your package for use as a template, and for every package that you create from this point on, you must create it from that template. Once this is done, there are a few simple things to do:

• Whenever you create a new project, change the value of the ProjectFolderRelativePath variable to reflect the new project folder name.
  
• Whenever you access file system resources, always use an expression based on the ProjectFolderCalculatedPath variable to identify the path of the resource.
  

It's the second item here that will trip you up, especially if you have many developers on your ETL team. Sometimes people forget. Because of this, it's a best practice to perform regular test executions of your packages from folders other than the default development project folders – AND rename the default folder while you run the tests. If you forget to do this, it is possible for the packages to be built incorrectly with hard-coded paths, and your test will not catch the problem, as the resources in the default location will be used without your knowing it. One of these days I'll write a little test harness using the SSIS object model to look for this problem [4], but it won't be today…

[1] I have another blog post extolling the virtues of Expressions that has been kicking around even longer, but it's taking even longer to finish.

[2] Obviously, you can name the variables whatever you want – this is just what I like to call them.

[3] I love the term "Visual Studio" and for some irrational reason hate the term "BIDS."

[4] And when I do, I'll post the code here.