Override SSIS Package Variables Without Opening the Package
Bradley Schacht is a Principal Program Manager on the Microsoft Fabric product team based in Saint Augustine, FL. Bradley is a former consultant, trainer, and has coauthored 6 SQL Server and Power BI books, most recently Learn Microsoft Fabric. As a member of the Microsoft Fabric product team, Bradley works directly with customers to solve some of their most complex data problems and helps shape the future of Microsoft Fabric. Bradley gives back to the community through speaking at events such as the SQLBits, Fabric Community Conference, PASS Community Data Summit, SQL Saturdays, Code Camps, and user groups across the country including locally at the Jacksonville SQL Server User Group (JSSUG). He is a contributor on SQLServerCentral.com and blogs on his personal site, BradleySchacht.com.
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.