 |
|
Pass dynamic parameter values to SQL Server Integration Services
|
By: Greg Robidoux | 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' select @cmd = 'dtexec /F "' + @ssispath + '"' 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' select @cmd = 'dtexec /F "' + @ssispath + '"' 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
Print
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.
|
|
| 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 Working with Excel Files with the Script Task |
|
|
privacy | disclaimer | copyright | advertise | about authors | contribute | feedback | giveaways | user groups Some names and products listed are the registered trademarks of their respective owners. Edgewood Solutions LLC | MSSharePointTips.com | MSSQLTips.com |