Passing Values into an SSIS Package at Runtime from
Outside the Package
SSIS provide several methods for passing values into your packages at
runtime. The benefit of doing so allows
you to change the results of the package without having to even open the
package in the development environment (BIDS).
The typical way of doing this is to create variables inside your package
that other SSIS tools can interact with from outside of a package. The tools described in this post will be
Configurations, SQL Agent Job, and DTEXEC.
There are other methods as well but these are the simplest. So the first step is you must create a
variable that will store whatever value you wish to pass into the package.
Now let’s look at the methods for passing in values to that variable.
Configurations
There are many debates which type of configuration should be used, but
whether you use Config files, Config Tables, or even Environment Variables it
will give you the ability to make changes from outside of the package. Configurations can be added to a package by:
1.
Right-clicking in the Control Flow of a package and selecting Package
Configurations.
2.
Next, click the checkbox to enable package configurations and then hit Add.
3.
Click Next past the Welcome screen of the wizard.
4.
Select the type of configuration you wish to use and a location for it then
click Next(I’m not going to focus on each on in the post).
5.
Select the object to configure. This
can be anything inside of the package but for our example let’s say it is a
variable value as shown below.
6.
Click Next and name the configuration before you click Finish.
This will allow you to manage the value of this variable from a
configuration file, table, or Environment Variables. You simply edit the configuration to modify
how the package will run.
SQL Agent Job
A variable value change can also occur inside a SQL Agent job. If you create a SQL Agent job that runs your
SSIS package then you are able to pass values into variables by using the Set
Value tab of the job. This is visible
when you have selected the SQL Server Integration Services Package for the step
type. Below I show how you can replace
the default value of a variable during the run of the package from this SQL
Agent job.
The recognized format for the Property Path must be similar to this:
\package.variables[variablename].Value
Command Line
The last method I’ll mention in this post is using the command line prompt
called DTEXEC. By opening cmd.exe you
can run package and even pass values into a package variable. This is very similar to how the SQL Agent job
performed the same task. If you look
below you will see the script uses dtexec to call the command line prompt for
running SSIS package. The /f indicates
that the package is stored in a file system.
If the package is stored on the server then you indicates that with a
/SQL. Next is the location of the
package that should be run. In this care
that location is C:\SSIS\Troubleshoot.dtsx.
Then finally the /set command
will allow you to change values that exist in the package. In this example
\package.variables[variablename].Value;"mynewvalue" is replacing the
value in the variablename variable with the value mynewvalue. One last thing I’ll note about the command
line is that the V in Value but be capitalized for it to work.
dtexec /f C:\SSIS \Troubleshoot.dtsx /set \package.variables[variablename].Value;"mynewvalue"
I know there are other avenues for performing these same tasks so feel free
to share your methods of changing package values from outside of the package
with me!
Hello Devin,
Firstly, thanks for sharing this post and also I want to
add something regarding passing values dynamically to SSIS package. In one of
my package, I used the following script and execute package along with passing
variable dynamically or outside the SSIS from SSMS.
DECLARE @ssis_cmd VARCHAR(4000)
DECLARE @Packagepath VARCHAR(50)
DECLARE @FileName VARCHAR(50)
SET @Packagepath = 'C:\Test_Xp_cmdshell\Package.dtsx'
-- SSIS package location
SET @FileName = 'D:\SSIS\File.txt' --Passing dynamic variable i.e 'file location' into ssis
package.
SELECT @ssis_cmd = 'dtexec /F "' + @Packagepath + '"'
SELECT @ssis_cmd =
@ssis_cmd
+ ' /SET
\Package.Variables[User::FileName].Properties[Value];"' + @FileName + '"'
EXEC master..xp_cmdshell @ssis_cmd
Can you have the
sql agent job run via an xml configuration file but also override a particular
variable value within that xml config file with a value I've set in Set Values?
I have a package that runs with an end date variable that's pulled from the xml
config file (along with 20 or so other variable settings). Within a certain SQL
Agent Job that runs this package though, I want that it to run setting End Date
equal to the time and date the job actually runs according to its job schedule.
10/24/2011
·
DevinKnight said:
You could. If you
add the config to the sql agent job it can apply it's value to your variable.
10/27/2011
·
mattfloyd said:
I am using SQL
Server for my package configurations. Can I dynamically pass the configuration
filter in my dtexec command line. Why? Then we could have one package to sync
our cubes and we would pass the configuration filter to that package which
would tell it which cube to sync, etc. I know this is possible to do by using
separate XML files. Just trying to do it using SQL Server Configuration. I am
using SQL 2008.
2/1/2012
·
user711557 said:
I am using sql
server 2008.my package configuration is sql server, I am passing value from sql
job agent but it is not updating default value
CREATE TABLE [tempdb].dbo.##temptable
(
date
datetime,
companyname
nvarchar(50),
price
decimal(10,0),
PortfolioId
int,
stype
nvarchar(50)
)
Insert into [tempdb].dbo.##temptable
(date,companyname,price,PortfolioId,stype)
SELECT
date,companyname,price,PortfolioId,stype
FROM ProgressNAV
WHERE (Date = '2011-09-30') AND (PortfolioId = 5) AND (stype in ('Index'))
ORDER
BY CompanyName
Now
in above query I need to pass (Date = '2011-09-30') AND (PortfolioId = 5) AND (stype in
('Index'))
these 3 parameter using variable name I have created variables in package so
that i become dynamic . please help
In your Execute SQL Task, make sure SQLSourceType is set to Direct
Input, then your SQL Statement is the name of the stored proc, with questionmarks
for each paramter of the proc, like so:
Click the parameter mapping in the left column and add each
paramter from your stored proc and map it to your SSIS variable:
Now
when this task runs it will pass the SSIS variables to the stored proc.
No comments:
Post a Comment