Learn more about SQL Server tools

solving sql server problems for millions of dbas and developers since 2006
join MSSQLTips for free SQL Server tips

































Top SQL Server Tools






















Pass dynamic parameter values to SQL Server Integration Services

MSSQLTips author Greg Robidoux By:   |   Read Comments (13)   |   Related Tips: More > Integration Services Configuration Options

Problem
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?

Solution
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".

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.

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".

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

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.

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 2005.)

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.


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.

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

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

 



Last Update: 12/17/2007


About the author
MSSQLTips author Greg Robidoux
Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips


print tip Print  
Become a paid author




Recommended For You








Learn more about SQL Server tools
Comments and Feedback:
Tuesday, January 29, 2008 - 9:22:37 AM - Rex Read The Tip

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?


Thursday, November 04, 2010 - 7:16:18 AM - Vinod Andani Read The Tip

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


Thursday, July 14, 2011 - 8:05:11 AM - Sarah Khan Read The Tip

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.


Saturday, March 03, 2012 - 1:35:36 PM - Shweta Read The Tip

This helped a lot


Wednesday, March 07, 2012 - 4:01:58 PM - Jason You Read The Tip

That's very Good. Thx.

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


Thursday, March 08, 2012 - 9:11:44 AM - Jason You Read The Tip

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, May 30, 2012 - 12:10:09 PM - ragoli Read The Tip

This article helps alot, Thanks for providing this information.


Tuesday, June 05, 2012 - 4:51:17 AM - naveenkumarkavuri Read The Tip

This Article gives lot of informaiton.Thank u


Wednesday, July 11, 2012 - 8:59:42 AM - Glenn Stanton Read The Tip

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.


Thursday, July 12, 2012 - 2:06:29 PM - Cathy S. Read The Tip

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, August 29, 2012 - 6:59:09 AM - campbell foster Read The Tip

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.

 


Wednesday, October 10, 2012 - 4:40:16 PM - Manish Read The Tip

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, October 10, 2012 - 5:42:40 PM - Greg Robidoux Read The Tip

@Manish - Take a look at these articles:

How to: Loop through Excel Files and Tables by Using a Foreach Loop Container
http://msdn.microsoft.com/en-us/library/ms345182.aspx

Working with Excel Files with the Script Task
http://msdn.microsoft.com/en-us/library/ms403358.aspx



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates

Signup for our newsletter


Comments
*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.