Override SSIS Package Variables Without Opening the Package

SSIS packages need to have the ability to be dynamic.  To an extent we are able to accomplish this through the use of Configuration Files, Execute SQL tasks with results written to variables and even the use of the script task.  One great way to make an SSIS connection manager dynamic is through the use of Expressions.  Kyle Walker recently posted a blog here on BIDN about setting the location of an Excel file in the connection manager using an expression.  This expression basically uses a location stored inside of a variable which can then be overridden.  While this can be done a number of ways, an execute sql task to pull the location from a table, or a for each loop that will go through all the files in a folder, what happens if you want to have that variable overridden on demand at runtime.  You don’t want to have to go to a table and update a value, or be restricted to a single file system location then there is a solution.  We can do this in a couple of ways, today we will quickly go through the steps for the execute package utility from the file system and the TSQL approach using EXEC…DTEXEC….
The two solutions are very similar in that the code for the actual override of the variable is the same
First up the Execute Package Utility:
When you double click on a package on the file system the Execute Package Utility comes up.  You can override properties of the package, in this case the value of a variable, on the Set Values tab.

While in that section, Fill in the property path with the location of the variable.  This will be in the format of package followed by the task used in the package (if applicable) then “.Variables[User::VariableNameHere].Value  So a variable called Test created in the scope of a task called MyDataFlowTask would be entered as “packageMyDataFlowTask.Variables[User::Test].Value”.  On the other hand, if a variable is scoped to the package level it will be “package.Variables[User::Test].Value”.  Next in the Value column simply enter what you want the new value to be.  You can do this for as many variables as you would like.
Next the T-SQL Aproach:
You can go about this a couple of ways.  While you’re in the execute package utility and after the variable is set up to be overridden in the Set Values section, click on the Command Line tab on the left side.

This will automatically format the command line portion of the exec command we need to run our package using T-SQL.  Copy the text in the box and head on over to Management Studio.  First type in EXEC xp_cmdshell ‘DTEXEC then paste the copied text and add one final single end quote.  You should end up with somethinng like this: EXEC xp_cmdshell ‘DTEXEC /FILE “C:Package1.dtsx” /CHECKPOINTING OFF  /REPORTING EWCDI  /SET “package.Variables[User::Test].Value”;MyValue’  You can of course manually write this if you can remember the syntax, or use the execute package utility to do some of the work for you.
Either way, the result will be the variable Test being overridden.  So now, in the context of the blog Kyle did that I mentioned earlier, we could use either of these methods to override the variable used in the connection manager expression which sets the location of the file.  Now there is one package that can be used to run the Excel file (or any other file assuming your connection manager is set up this same way) no matter where it is located and without having to code a location into a table or using a ForEach Loop.