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