Once SSIS packages deployed to production server, there are several way to execute it. Execute it by SQL Job or performing it from SQL script.
For most of case in my project need to execute SSIS package from SQL procedure. There are few challenges come up when executing SSIS package from SQL procedure.
By default start_execution is async function. The SQL procedure move to the following script once SSIS Package is called. But some of case we need to the data which processed in SSIS Package . In this case, asynchronous execution should be used.
The syntax to add SYNCHRONIZED parameter is shown below:
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type = 50, @parameter_name = N’SYNCHRONIZED’, @parameter_value = 1
start_execution will return success message even if SSIS Package is failed. For most of case, the whole store procedure should be stopped once SSIS package execution failed. The workaround solution to solve this issue, we need to get package execution status flag from SSISDB then raise error. Code show as below:
IF 7 <> (SELECT [status] FROM [SSISDB].[catalog].[executions] WHERE execution_id = @execution_id)
RAISERROR(‘Json file import exception, please contact support team’, 16, 1)