Search code examples
ssisforeach-loop-container

SSIS runs a package multiple times in parallel

I have a package that does a job with the configuration from a table.

right now I can create a [foreach loop] and it will work fine and do the process for each row. but since it will run in serial and my processes are independent I want my [foreach loop] runs all of the packages in parallel. does anyone know how to do that?

(image for visualization so don't mind the red errors)

enter image description here

Solution

  • I've been able to get that to work with a Exec SQL statement. As soon as the SQL fires successfully and starts the package then it moves to the next loop.

    Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'Package.dtsx',
    @execution_id=@execution_id OUTPUT,
    @folder_name=N'Deployed Projects',
      @project_name=N'Integration Services Project1',
    @use32bitruntime=False,
      @reference_id=Null
Select @execution_id
DECLARE @var0 smallint = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,
    @object_type=50,
      @parameter_name=N'LOGGING_LEVEL',
      @parameter_value=@var0
EXEC [SSISDB].[catalog].[start_execution] @execution_id
GO

    Here's the Microsoft link:

    https://learn.microsoft.com/en-us/sql/integration-services/ssis-quickstart-run-tsql-ssms?view=sql-server-ver15

    Setting parameters uses a different object type that you need to google.