SSIS Variable Overrides In SQL Agent Jobs: Think About Those Trailing Slashes

This is mostly a note for myself…

When creating a SQL Server 2008 Agent job and using an SSIS 2008 package, a trailing slash (of the \ variety) may interfere with your results.  For example, suppose we have two variables:  FTPSFolderLocation and ArchiveFolderLocation.  These are set in your SSIS package as variables, so that when you run the agent job, you can override the package variable to be what it really should be:  a network share, local directory, or whatnot.  When overriding the variable, you’ll have the variable name and value set to look like:

\Package.Variables[FTPSFolderLocation].Value –> C:\FTPS\

\Package.Variables[ArchiveFolderLocation].Value –> C:\Archive\

When actually running the package, though, the FTPSFolderLocation value will not be “C:\FTPS\” at all;  it will be C:\FTPS” /SET \Package.Variables[ArchiveFolderLocation].Value;C:\Archive instead.

The way I got around this personally was to remove the trailing \ and in the script block that I needed these variables, append them.  Perhaps you could also double-slash (\\) the last one to show that it is not a special character, but I did not try that way.