Posts Tagged ‘SSIS Best Practices’

SSIS Post #95 – Where should my configuration file(s) go?

April 14, 2011 Leave a comment

I haven’t blogged for more than a week now. That’s a little unusual for me. It was not because I had too much to do at work, it’s because I took on too much responsibilities (trust me there is difference between these two statements). I say yes to almost everything that business users asked me, co-workers asked me, and my boss asked me.

Anyway, now I am back. I checked some stats on my blog, and see that I blogged the most in these two categories: ETL Best Practices and SSIS Best Practices. I guess there is no accident here. ETL and SSIS are the areas that I always felt strongly and wanted to articulate my ideas or understandings or mistakes. 

Putting the ETL and SSIS blogs together, I have over 150 posts in these two categories. To make it easier for me to refer back to my own posts, and for my regular blog fans to find them, I’ve decided to include a sequential number for each blog post. So here it is, the number 95 in the SSIS category.

This blog post is about the infamous configuration file again in SSIS. I had privilege recently communicating with David about the trouble he had with his configuration file. David is a very experienced database professional and has done everything he knew about not hard-coding any user id, password, file path/name, or any other configurable parameters that might change in the future. David is a consultant, so this is a very important decision to make. When he is no longer with the team, other developers will know how to change those parameters without breaking the process.

Scenario (from David)

I am developing a SSIS package or three (on my local machine)  for a client and I wanted it to be as dynamic as possible so when I leave they will only have to modify the configuration file and all will be well.  So I set up checkpoints and logging and email and expressions on most everything.   So I set up the expression on the checkpoint file and it evaluates fine; the expression is  “@[User::RootLocation] +  @[User::CheckPointsFile] +  @[User::CheckPointExtension]”.

But when I try to save the package the error message below comes. (Note: All worked fine on David’s local machine. The error came up only when trying to deploy to the server.)

Here is the message when I try to save package:

TITLE: Microsoft Visual Studio


Nonfatal errors occurred while saving the package:

Error at DEBNetPlusEmailProcess: The file name is not valid. The file name is a device or contains invalid characters.

Error at DEBNetPlusEmailProcess: The result of the expression "@[User::RootLocation] +  @[User::CheckPointsFile] +  @[User::CheckPointExtension]" on property "CheckpointFileName" cannot be written to the property. The expression was evaluated, but cannot be set on the property.

Solution (Also from David)

Ok…OK… I found the answer I was looking for and it came to me by way of a developer who was asking questions about the variables inside the package.  His questions started me thinking and what I discovered was this:

When you deploy the package the configuration file must have the EXACT same fully qualified PATH as when you created it on the development environment.


C:\Folder1\Configuration\myConfigFile.Configdts  (Development Environment) 

C:\Folder1\Configuration\myConfigFile.Configdts (Production/Test Environment)

I had this earlier:

Y:\Folder1\Configuration\myConfigFile.Configdts  (Development Environment)   ON deployment

The server could not reach “Y” path so validation failed….

Now, ALL of this might have been intuitive to many but I can/could found NO reference to it anywhere.

Another more “dynamic” solution (this one is from me)

Congratulations to David! He has just solved another mystery about using configuration files in SSIS. But here is some additional conversation between me and David. The point of this conversation is that, we could use an Environment Variable to point to a configuration file, instead of hard-coding the name and path of the configuration file.


Your solution would work by using a fully qualified path, which must be the same in development as in production.
In a previous blog, I recommended that instead of using a fully qualified path for your configuration file (essentially you are hard-coding the configuration file path and name), you will use an environment variable to point to the configuration file on a particular machine.

Here is the link.

Thanks for letting me know about your resolution.

From David:

Yes, I agree completely that a environment variable on the production machine would be the ideal place to store the path to the configuration file.  I have read your blog post and will implement that in our move to prod on our next project.

Your time and help have been most gratifying and I hope to communicate with you in the near future.



I hope this is not a long and boring post to read. Have fun with configuration files!

%d bloggers like this: