What to do when DTExec will not read your XML Configuration file

It turns out that this advice, whilst appearing to work, is actually incorrect. The correct solution, and the actual problem, will be forthcoming in a new blog post by the 14th of September 2012.

When a package or a set of packages have been deployed to a location that differs from your development environment i.e. pretty much in almost every case you can imagine, DTExec will refuse to read the accompanying configuration file, causing a veritable cornucopia of frustration.

One of the useful features of SSIS is the ability to specify values for variables in various configuration sources e.g. SQL Server, the Command Line, Environment Variables, and last, but by no means least, an XML Configuration file.

With the XML Configuration file, though, you need some extra help. By default DTExec will use the variable values from the design time settings i.e. those that you put in when you were designing the SSIS package. To force DTExec to use the settings in the XML Configuration file you specified in your command line, use the following tip:

1. Just prior to saving the package, disable the package configuration. Don’t delete it, just disable it.

2. Regenerate a new GUID for the package. Package properties, ID (dropdown) “<Generate New Id>”.

3. Save the package. When the package is executed using DTExec it will read your variable values from the XML file, not from the design time configuration stored in the package.

I picked up this piece of advice from Jayaraj Sivadasan on the forums at SQL Server Central finding it after tearing my hair out for the better part of two weeks attempting, with other Google searches, to determine why my configuration file was being ignored and finding lots of information to suggest that ignoring the file was the default behaviour specified by Microsoft!

I never cease to be amazed by the “genius” that flows out of Redmond.

Further to this tip, there is a caveat that applies:

• Don’t execute any tests on configuration changes in the development folder. The package will read the values from the development configuration file and will initially appear to work correctly, until you change the values in the configuration file and discover that the values you have just entered do not appear in your output. Again, do not trust the man behind the Red(mond) curtain.

Basically, as it stands right now, getting XML Configuration to work, on a machine other than the development server, is a matter of applying magic. Microsoft, if you’re reading this, this is not good enough. None of the available documentation references this type of technique to correct the problem, nor should it. It smells awfully like a design flaw, and a bad one at that. it should have been picked up in QA.

If someone can point out to me how this is supposed to work without having to resort to the magic outlined above, I'd be very interested in hearing about it.

SSIS and the File System Task

This post describes an issue I encountered when making my way through using the File System Task for the first time in SSIS.

In this case I needed to import data from a batch of files into the database, then move the files to a processed folder one the import was complete. It took me a good couple of days to get the FST to do as I wanted.

Here’s how I got it going:
• To make things easier for me to comprehend, I firstly created two variables to hold the Source File and Destination paths. Note the Source File is the fully qualified path to the file i.e. it contains the file name, and the Destination path is just the path i.e. it contains no file name. I originally made the mistake of having both the source and destination paths contain the file name and that led to a puzzling error of “Could not find a part of the path.” Much googling later, I figured out the answer myself. Hopefully this post will help you avoid too much googling.

• Using two specific variables meant that I could abstract away the need for an expression in the FST, although in the end it would have worked equally as well, but when debugging, it was simpler to have separated the combining of the part parts away from the use of the paths to move the file.

The two variables were made up of a server part, a root path part, a source/destination folder and the file name. I set the variables up to be based on expresssions meaning that I could check the result of the expression prior to having to use it in the FST. If I had discovered this was possible earlier the job may have been easier to get working.

The errors I encountered in using this task were:

Error	Cause
“Could not find a part of the path.”	The destination path contained the file name. The Destination Connection should represent a path only. No file name is required.

Hopefully this helps someone out there beginning SSIS some angst when they’re using SSIS to move files around their systems, during or post processing.