Thread: MS SQL Server General Questions/Passing Values into an SSIS Package at Runtime from Outside the Package

    
05 March 2012

15:00:51
sergey


Posts: 0

Passing Values into an SSIS Package at Runtime from Outside the Package

www.bidn.com/blogs/DevinKnight/ssis/1655/passing-values-into-an-ssis-package-at-runtime-from-outside-the-package


 


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"
    
05 March 2012

15:16:54
sergey


Posts: 0

Re: Passing Values into an SSIS Package at Runtime from Outside the Package

msdn.microsoft.com/en-us/library/cc895212.aspx
    
05 March 2012

17:50:53
sergey


Posts: 0

Re: Passing Values into an SSIS Package at Runtime from Outside the Package

 


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