We are loading ETL data in two parts, first with an SSIS package and then with the stored procedure.
In the following script, the stored procedure of Part 2 will be executed even before SSIS Part 1 is completed. How do I get the stored procedure from Part 2 to run only after completing Part1 SSIS?
Is there a way to accomplish this in T-SQL? Should we use the delay function and verify every few minutes with SSIS dmv SSISDB.catalog.executions if the package is complete? There's another way?
We can place sproc on a control flow arrow after SSIS. However, our company does not want to do this. We are more of a script based company. Also, try to refrain from SQL agent jobs. Currently, the quality control department needs a way to execute elements, without creating multiple jobs. We create jobs for primary level packages, however, we do not want to create unit jobs for our more than 500 SSIS and sprocs.
--Part 1 Exec [SSISDB].[catalog].[create_execution] @ package_name = N & # 39; Customer_StageTable_Part1_Insert.dtsx & # 39; , @ project_name = N & # 39; Datawarehouse & # 39; , @ folder_name = N & # 39; Datawarehousefolder & # 39; , @ use32bitruntime = False , @ reference_id = 2 , @ecution_id = @ecution_id DEPARTURE select @execution_id --Part 2 run Customer_StageTable_Part2_Insert