Okay, so I've implimented this and I like it, a lot! I am having an issue that I'm hoping anybody can help me resolve. I am a consultant and the client Im at doesn't have a standard UAT Integration Services environment (dont ask ;). As a result, we deploy the packages to the Dev environment with Package Config enabled, but I override a couple values using the Data Values tab in the SQL Job. It works great for the database connections (data source tab) and for 2 INT variables, but one of the variables that needs to be set for the UAT environment is a folder directory where some files are located.
It appears the SQL Job isn't passing in my string value correctly. Its a basic UNC path to a folder, but the path does have some spaces it a couple of the directory names and I think this is where its blowing up. In the SSIS pkg, I have a script task that uses this path to do stuff with the files but I keep getting errors with everything I've tried thus far. The simplest solution is to quote this entire string in the SQL Job, the error I get is "Invalid characters in path". The errors I get are from inside the package and not from the job. Furthermore, I can execute using dtexec by just quoting my string and using dbl back slashes and that works, so this says to me the SQL Job isnt passing the string into the package properly.
This is only a problem when I try overriding these values through the SQL Job. Using the Config normally or in Debug mode everything works fine with the string value I'm trying to use.
Examples of what I've tried:
"\\server\dir1\dir 2\" - error "invalid characters in path"
\\\\server\\dir1\\dir 2\\ (with and w/o quotes around the whole string). This error shows me the correct path, but says it cant find the file.
\/\/server\/dir1\/dir 2\/ (with and w/o quotes around the whole string). My VB script task doesnt error but doesnt do anything. The next step in the package (to open the file) gives the error: No destination flat file name was provided.
//server/dir1/dir 2/ generated an error in the next step in the package. Doesnt generate an error, but the package doesnt do what its suppose to.
\\server\dir1\dir%202\ (with and w/o quotes around the whole string). Illegal characters in path.
Any feedback is greatly appreaciated!