Run DTEXEC from SSMS to run SSIS Catalog Package

Question

I would like to execute SSIS package using Catalog in a stored procedure using DTEXEC:

DECLARE @cmd varchar(8000)
DECLARE @params varchar(4000) SELECT @params = '/SET \Package.Variables[User::VarBigInt].Properties[Value];"' + CONVERT(char(10), @VarBigInt) + '" ' SELECT @params = @params + '/SET \Package.Variables[User::VarDate].Properties[Value];"' + CONVERT(NVARCHAR(20), @VarDate,101) + ' " '

SELECT @params = @params + '/SET \Package.Variables[User::VarDestination].Properties[Value];"' + @VarDestination+ '" '
SELECT @params = @params + '/SET \Package.Variables[User::VarBoolean].Properties[Value];"' + CONVERT(CHAR(1), @VarBoolean) + '" ' SELECT @params = @params + '/SET \Package.Variables[User::VarString].Properties[Value];"' + @VarString+ '" ' SELECT @params = @params + '/SET \Package.Variables[User::VarList].Properties[Value];"' + @VarList+ '" '

SELECT @cmd = 'dtexec ' +N'/ISSERVER ""\SSISDB\test\test'+ @PackageName +'" /SERVER ""'+ @DestServerName +'"" /ENVREFERENCE 1 ' + @params + '"$ServerOption::SYNCHRONIZED(Boolean)";True'

This is failing at

SELECT @params = @params + '/SET \Package.Variables[User::VarDate].Properties[Value]";' + CONVERT(NVARCHAR(20), @VarDate,101) + '''"'

upon running the stored procedure to deploy this to catalog: Option " /SET \Package.Variables[User::VarDate].Properties[Value];01/15/2019" is not valid.

VarDate is a datetime variable in the SSIS package.

vvvv4d vvvv4d · Accepted Answer · 2020-08-25T18:29:51

You are missing some " around both sets of SET

SELECT @params = @params + '/SET "\Package.Variables[User::VarDate].Properties[Value]";"' + CONVERT(NVARCHAR(20), @VarDate,101) + '"'

Run DTEXEC from SSMS to run SSIS Catalog Package

1 Answers