DataBaser.Net: xp_cmdshell+Dtexec

declare
    @cmd nvarchar(4000)
,   @bdt char(8)
,   @edt char(8)
,   @path nvarchar(1000)
,   @pkg nvarchar(1000)
,   @return_code int
 
set @bdt = '20190801'
set @edt = '20190801'
set @path = '\SSISDB\Project01\DW\'
set @pkg = 'dim.test.dtsx'
 
set @cmd = N'""C:\Program Files\Microsoft SQL Server\130\DTS\Binn\DTExec.exe" /Server localhost /ISServer "@path@pkg" /SET "\Package.Variables[User::input_bdt].Properties[Value]";"@bdt" /SET "\Package.Variables[User::input_edt].Properties[Value]";"@edt" /Par "$ServerOption::SYNCHRONIZED(Boolean)";True'
set @cmd = replace(@cmd, '@bdt', @bdt)
set @cmd = replace(@cmd, '@edt', @edt)
set @cmd = replace(@cmd, '@path', @path)
set @cmd = replace(@cmd, '@pkg', @pkg)
print @cmd
 
exec @return_code = xp_cmdshell @cmd
print @return_code --0이면 성공

"주의"

/Par "$ServerOption::SYNCHRONIZED(Boolean)";True 를 붙여야 return_code가 제대로 들어온다.

dtexec /package MyPackage.dtsx /project MyProject.ispac