Pass dynamic parameter values to SQL Server Integration Services

By:   |   Comments (16)   |   Related: > Integration Services Configuration Options


When using SQL Server Integration Services (SSIS) the ideal situation is to make the code as re-useable as possible, so the same code-set can be used to handle multiple situations instead of having a hard-coded solution.  In a previous tip, "Dynamic Flat File Connections in SQL Server Integration Services" we looked at how to create a dynamic file source based on some variable settings within the SSIS package.  This solution was great, but how do I take this further and pass in a dynamic value into an SSIS package?


As with Data Transformation Services (DTS) in SQL Server 2000, SSIS also gives you the ability to pass in parameter values directly to the SSIS package at run time.  With SSIS the syntax is different than with DTS, but the options that you have are much greater than what was available in DTS.

To show you some simple examples we are going to create a flat file source that will import data into a SQL Server table.  The first solution will be a hardcoded option and then we will call the SSIS package from the command line and pass in new parameter values for multiple parts of the package.

We first create a new SSIS package and use a "Data Flow Task".

data flow task

Then we add a "Flat File Source" and an "OLE DB Destination".  The flat file source is a CSV file that has these columns ID, Name, Address, City, State and Zip. The SQL Server table has these exact same columns.

flat file source to ole db

With the values hardcoded the package executes without a problem.

To take advantage of the dynamic aspects of an SSIS package we create a new variable called "fileName' which will take the path and the name of the file we are passing into the SSIS package.  For more information on setting up a dynamic flat file source take a look at this tip "Dynamic Flat File Connections in SQL Server Integration Services".

First we create a new variable called "fileName".

ssis variables

After the variable has been created we create an Expression for the flat file connection, which is shown below.

variable settings

At this point our filename that we are importing is called "c:\temp\test2.csv" (which is a non-existent file) this is based on the variable that we just set .  If we execute the package now the package fails with the following message.

data flow task failed
failure message

The reason the variable was set to this non-existent file was just to illustrate that the package will fail, but will run when we pass in a good file name. The following is syntax to execute the SSIS package along with passing in the variable value for the fileName variable.

This syntax creates the command to run the SSIS package, but uses the 'C:\temp\test.csv" filename instead of "C:\temp\test2.csv.

This code can be pasted into a query window and executed. (note: you must have xp_cmdshell enabled to run the following code.  Take a look at this tip on how to enable xp_cmshell in SQL Server.)

declare @cmd varchar(1000)
declare @ssispath varchar(1000)
declare @fileName varchar(1000)

set @ssispath = 'C:\temp\Package.dtsx'
set @fileName = 'C:\temp\test.csv' 

select @cmd = 'dtexec /F "' + @ssispath + '"'
select @cmd = @cmd + ' /SET \Package.Variables[User::fileName].Properties[Value];"' + @fileName + '"' 

exec master..xp_cmdshell @cmd

When this code is run the end of the query output will look something like this, to show the package ran successfully.

success message

To take this a step further, let's say you want to pass two variables to the package.  You need to do pretty much the same thing. 

Here we now have a variable for fileName and another one for filePath.

ssis variables

If we look at the expression, it still works the same way, but we have both variables making up the expression now.

ssis variable expressions

The code to pass the two variables to the package is shown below.  For the most part the syntax is identical.  We now have two /SET commands to pass in each variable.

(Note: notice that we had to use a \\ at the end of the filePath.  If you only use one such as "C:\temp\" the package will give you an execution error.

At this point you can execute the package and the two variables we be passed into the SSIS package.

declare @cmd varchar(1000)
declare @ssispath varchar(1000)
declare @filePath varchar(1000)
declare @fileName varchar(1000) 

set @ssispath = 'C:\temp\Package2.dtsx'
set @filePath = 'C:\temp\\'
set @fileName = 'test.csv' 

select @cmd = 'dtexec /F "' + @ssispath + '"'
select @cmd = @cmd + ' /SET \Package.Variables[User::filePath].Properties[Value];"' + @filePath + '"'
select @cmd = @cmd + ' /SET \Package.Variables[User::fileName].Properties[Value];"' + @fileName + '"' 

exec master..xp_cmdshell @cmd

We only hit upon passing user defined variables to a package, but you can pass all sorts of different values dynamically to control how your SSIS package behaves.

Next Steps
  • Start making your SSIS packages as reusable and portable as possible by passing in dynamic variable values
  • Take a look at these other SSIS tips

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Wednesday, July 15, 2015 - 1:06:25 AM - Shreya K. Back To Top (38209)


I am in similar position as Saqib. I am new to SSIS. My earlier ETL experience is on Datastage but currently I am working on SSIS 2012. Our project is using the project deployment model. We have parameterized the connection managers, created environment variables and configured the references accordingly. However, we run into issues when we need to execute the package, solution or task directly from Visual Studio. SSIS throws an error saying "Login failed", even if we manually change the properties of the connection manager we still get this error. We don't mind entering the values manually but is there any way that instead of opening the connection manager explicitly the SSIS prompts us for the connection details when we execute the package, task or solution from Visual Studio?


Shreya K

Tuesday, July 22, 2014 - 8:01:19 AM - Saqib Khan Back To Top (32821)
Thank you for sharing this wonderful information. I am new to SSIS. In the past I have mostly worked on IBM DataStage (which is another ETL tool like SSIS). What I am curious to know is whether I can pass a new value to a variable every time I run a SSIS package. Like if I were to implement your example of loading file data to a database table, in DataStage, I would have created a variable (called parameter in DataStage) at package level (job level). Now when I run my DataStage job, I will get a pop up window which shows all the defined variables at package/job level along with their default values. I can change these values at run-time. So I can pass different file names every time I run my package/job. Is this way of changing variable value possible in SSIS (without having to write/update piece of code every time to execute package) ?

Friday, July 18, 2014 - 6:25:04 AM - Yogesh Back To Top (32768)

Please help us whether we can call DTS directly from Java.

Solution which we got as below:-

1. Create dts

2. Create job in which we have to call dts

3. Call job from stored procedure


It pretty lengthy process and we are not sure how error handling can be done in this scenario.


Request all to help with proper guidance.




Wednesday, October 10, 2012 - 5:42:40 PM - Greg Robidoux Back To Top (19859)

@Manish - Take a look at these articles:

How to: Loop through Excel Files and Tables by Using a Foreach Loop Container

Working with Excel Files with the Script Task

Wednesday, October 10, 2012 - 4:40:16 PM - Manish Back To Top (19858)

Hi Greg,

Nice post! I could use some help in this context.

I'm using SQL Server 2008, and wish to load Excel files into a SQL table. The files are dynamic, i.e. they will have a different name each time but same format, and will be placed in a pre-defined folder. My SSIS will pick them up.

The load Excel files are one per day, in the format Name_2012-10-01, Name_2012-10-02 and so on. The idea is to load each file as it comes in, and then move it to an Archive Folder.

In SSIS, I created an Excel Connection Manager, and specified the file name Name_2012-10-01 there. I have a For Each Loop, and two variables, CurrentFileName and BackUpLocation. I have the above two files in the source folder. When I debug the package, both the files get loaded fine, and then get moved to the Archive folder. When I place a 3rd file called Name_2012-10-03, it fails.

I think I know the reason but don;t know how to fix it. The reason could be that my Excel Connection Manager is still pointing to Name_2012-10-01, but that files does not exist since it was moved to the Archive folder. This is going to be an automated process, so I can't change the Excel Connection Manager before each load. I cannot let the 10-01 file sit in the source folder either, since after loading it should move to the Archive.
What can I do? Please advise.

Thanks in advance!


Wednesday, August 29, 2012 - 6:59:09 AM - campbell foster Back To Top (19271)

Not sure whether the last comment was posted correctly. Here it is again.

I am having a problem passing in a \\server\folder$\filename.txt variable into the set value. When I run the Package from IIS it runs properly but will not run in SQL Job Agent, giving the error message:- Started: 11:51:03 DTExec: Could not set \Package.Variables[User::Scorecard].Properties[Value] value to \\VM000001106\Basel$\Filename.txt. Started: 11:51:03 Finished: 11:51:04 Elapsed: 1.422 seconds. The package execution failed. The step failed.

The variable "Scorecard" evaluates correctly from within my package.


Thursday, July 12, 2012 - 2:06:29 PM - Cathy S. Back To Top (18480)

Can't thank you enough - I was just explaining to my boss yesterday that I couldn't wrap my brain around this concept and then got home and found this in my personal e-mail.  Whoever is up there watching out for me - thank you, thank you, thank you!


Wednesday, July 11, 2012 - 8:59:42 AM - Glenn Stanton Back To Top (18441)

another option is to set a table full of variables per application function and a generic SP to compile them into a list of values passed. this should then be useful in SQL queries but a vairation which outputs the content as a script for SSIS to SSIS calls should be readily created.

Tuesday, June 5, 2012 - 4:51:17 AM - naveenkumarkavuri Back To Top (17796)

This Article gives lot of informaiton.Thank u

Wednesday, May 30, 2012 - 12:10:09 PM - ragoli Back To Top (17728)

This article helps alot, Thanks for providing this information.

Thursday, March 8, 2012 - 9:11:44 AM - Jason You Back To Top (16296)

Ok, Found the solution. same with the other source, when define the table, using 'Table name variable or View Name variable' instead of 'Table Name or View Name' in Destination definition.

Wednesday, March 7, 2012 - 4:01:58 PM - Jason You Back To Top (16282)

That's very Good. Thx.

Is there any way we can make destination talbe name dynamic?

Saturday, March 3, 2012 - 1:35:36 PM - Shweta Back To Top (16243)

This helped a lot

Thursday, July 14, 2011 - 8:05:11 AM - Sarah Khan Back To Top (14169)

Thank you!!! This helped immensely!

Just a quick tip for other who may experience what I did - make sure wherever the package is built in the same version as the SQL server you are planning on running the package against.

Thursday, November 4, 2010 - 7:16:18 AM - Vinod Andani Back To Top (10333)

This should work fine if variable fileName is set to path having two backward slashes... like this : c:\\temp\\test2.csv

Tuesday, January 29, 2008 - 9:22:37 AM - Rex Back To Top (253)

Thanks for this article.  Do I take it that this cannot be done when calling an SSIS package from another - at least not with the "Execute Package" task?

get free sql tips
agree to terms