I have a package which is deployed using SQL server agent. I am executing the SQL server agent by calling a stored procedure. i have declared some variables inside the ssis package ,i want to use these variables as parameter in stored procedure. any body Please help me. Thanks in advance.
Asked
Active
Viewed 586 times
-1
-
You use a sql script task and pass a parameter to it. – Brad Jun 25 '18 at 13:20
-
But how can i get the values of variable in sql server scheduler job executing time. – junaib Jun 26 '18 at 06:21
1 Answers
0
If you are allowed to use xp_cmdshell you could use dtexec to execute the package from a stored procedure . Passing a parameter can be done like this:
exec xp_cmdshell 'dtexec /f "c:demo\demo.dtsx" set \package.configurations[Configuration1].properties[Variable].value;int-002'
This is not a best practice. I would prefer to use my stored procedure to write my variables to a table first and then read from that table in SSIS with an Execute SQL statement as you can read here Populate an SSIS variable using a SQL statement
analyzethat
- 191
- 1
- 16