0

We are loading ETL data in two parts, first with an SSIS package and then stored procedure.

In the script below, Part2 stored procedure will run even before SSIS Part1 is complete. How do I get Part2 Stored procedure to run only After completion of Part1 SSIS?

Is there a way to conduct this in T-SQL? Should we use delay function and check every few minutes with SSIS dmv SSISDB.catalog.executions if package is complete? Is there another way?

Background: We can place sproc in a control flow arrow after SSIS. However, our company does not want to conduct this. We are more of a script based company. Additionally trying to refrain from SQL agent jobs. Currently QA department needs a way to run items, without creating multiple jobs. We created jobs for parent level packages, however do not want to create unit jobs for our 500+ SSIS and sprocs.

--Part 1
EXEC [SSISDB].[catalog].[create_execution] 
               @package_name=N'Customer_StageTable_Part1_Insert.dtsx'
               , @project_name=N'Datawarehouse'
               , @folder_name=N'Datawarehousefolder'
               , @use32bitruntime=False
               , @reference_id= 2
               , @execution_id=@execution_id OUTPUT

select @execution_id

--Part 2
execute Customer_StageTable_Part2_Insert

https://www.sqlshack.com/execute-deployed-package-ssis-catalog-various-options/

  • But why? If you put the proc to run after the rest of the packages works correctly, then the proc can run without a job. Perhaps you are not explaining it correctly as there is no risk involved with adding a flow that then runs the proc. Instead of attempting to over complicate a workflow, you call the procedure that is made only for the proc. Put it in its own schema, that would be just fine, too. But asking your proc to constantly be running for the heck of a change that only occurs once seems pretty silly. :/ – clifton_h Nov 23 '18 at 21:57
  • To wait in TSQL for the execution, See ssistalk.com/2012/07/24/… – David Browne - Microsoft Nov 23 '18 at 22:12
0

You can specify that the package execution is SYNCRONIZED to wait in TSQL until it's complete using [SSISDB].[catalog].[set_execution_parameter_value]

So something like:

--Part 1
EXEC SSISDB.catalog.create_execution
               @package_name=N'Customer_StageTable_Part1_Insert.dtsx'
               , @project_name=N'Datawarehouse'
               , @folder_name=N'Datawarehousefolder'
               , @use32bitruntime=False
               , @reference_id= 2
               , @execution_id=@execution_id OUTPUT

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

--Start the package and wait for it to complete
EXEC SSISDB.catalog.start_execution @execution_id;

--Part 2
execute Customer_StageTable_Part2_Insert

see, eg http://www.ssistalk.com/2012/07/24/quick-tip-run-ssis-2012-packages-synchronously-and-other-execution-options/

0

I think that approach will just complicate your task.

What I'm thinking is a validation in a table, when your package starts insert something and then when it's completed insert something else, and then your "part2" when executed will look to the table if it's over execute this other wise wait for 5 minutes and execute this same check again.

  • I think David Browne gave answer above, will review it, thanks- – user162241 Nov 23 '18 at 22:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy