1 SSISDB.catalog.start_execution

2 비동기 처리

3 File System to Sql Server

4 File System to File System

5 Sql Server to Sql Server

6 Sql Server to File System

7 Delete from File System

8 Delete from Sql Server

--https://stackoverflow.com/questions/15882037/get-return-exit-code-from-a-package-executed-from-a-ssis-catalogue

EXEC [SSISDB].[catalog].[create_execution]
@package_name = N'FixProductType.dtsx', 
@execution_id = @execution_id OUTPUT, 
@folder_name = N'BI', 
@project_name = N'DataCleaning',
@use32bitruntime = False;

EXEC [SSISDB].[catalog].[set_execution_parameter_value] 
@execution_id, 
@object_type = 50, 
@parameter_name = N'LOGGING_LEVEL', 
@parameter_value = 1;

EXEC [SSISDB].[catalog].[start_execution] @execution_id;

DECLARE @status AS BIGINT = 1;
WHILE(@status = 1 OR @status = 2 OR @status = 5 OR @status= 8)
BEGIN
PRINT @status
PRINT 'waiting 5 seconds for Package to finish'
WAITFOR DELAY '00:00:5';

SET @status = (SELECT [Status] FROM SSISDB.[catalog].[executions]
        WHERE execution_id = @execution_id);
END

set @sql = 'start cmd.exe /C ""C:\Program Files\Microsoft SQL Server\130\DTS\Binn\DTExec.exe" /Server @server /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'
exec xp_cmdshell @sql

--source: https://rajendersehgal.wordpress.com/2014/07/24/ssis-package-deployment-using-dtutil-command/

dtutil /FILE D:\Sample.dtsx /DestServer DEL1-LDL-40215\MSSQLSERVER2012 /Copy SQL;Raj\Sample /DestU sa /DestP Global@123 /Q

dtutil /FILE D:\Sample.dtsx  /COPY FILE;D:\NewSample.dtsx /Q

dtutil /SQL “Raj\Sample” /SourceServer DEL1-LDL-40215\MSSQLSERVER2012 /SourceU sa /SourceP Global@123 /DestServer DEL1-LDL-40215\MSSQLSERVER2012 /Copy SQL;Raj\NewSample /DestU sa /DestP Global@123 /Q

dtutil /SQL “Raj\Sample” /SourceServer DEL1-LDL-40215\MSSQLSERVER2012 /SourceU sa /SourceP Global@123 /COPY FILE;D:\NewSample.dtsx /Q

DTUTIL command can also be used to delete deployed SSIS package.

DTUTIL /FILE D:\Sample.dtsx /DELETE

DTUTIL /SQL “\Raj\H1” /DELETE /SourceServer DEL1-LDL-40215\MSSQLSERVER2012 /SourceU sa /SourceP Global@123