SQL Server SSIS execute External Child Package from another SSIS project

ru9i0ody  于 2023-06-28  发布在  其他
关注(0)|答案(1)|浏览(142)

I have two ssis child packages (for custom logging). Each of them has package variables. I need those child packages to be used by multiple SSIS projects. Normally, I would copy the SSIS package and add it to each of the projects that need it. The problem is that as those child packages gets developed, the changes need to be added to all of the SSIS projects that use this generic package.

I did some researches over the internet and some of the ideas was to use Execute SQL Task instead of Execute Package Task (which I was not able to use with SQL Server as external source).

The problem with the Execute SQL task is that I need to convert the package to Package Deployment Model and all of my variables will be gone because of that + the already created solutions have a lots of it and they will not work If I remove those variables.

Any ideas will be really appreciated. Thanks! For me It is very strange how there is no External option where to choose directly from SSISDB Catalogs. It will "save" lives for sure.

Best regards.

mzmfm0qo

mzmfm0qo1#

i just stumbled across this topic on accident. Coincidentially I had to solve a similar problem last year.

DISCLAIMER:

  1. english is not my native language, so if you find any typos you are free to keep them :-)
  2. This approach feels like an ugly hack. In fact it is. So use it at your own risk. For me though this approach has proven to be stable for over a year now.
  3. I am fully aware of the fact that i am necroing an old thread. Maybe someone will find the information useful anyway.

Scenario:

  • SSIS packages from multiple solutions are deployed to the SQL Server and reside in the "Integration Services Catalogs"
  • There is a need to call specific packages residing in solution "b" from packages that reside in solution "a"
  • There is a need to pass data between those packages.
  • The selection dialog for the SSIS package execution task does not allow us to select packages from an "external" solution

My approach was as follows:

  1. for each variable you need to pass cross-solutions: create a package parameter in the receiving (=called) package
  2. from within the parent (=calling) package do the following steps

a) using EXEC SQL Task: create a new SSIS execution directly in SSISDB. We do this to circumvent those nasty restrictions the "Execute SSIS" task within SSIS tries to impose on us.

EXEC [SSISDB].[catalog].[create_execution]
  @package_name= @SSISPackageName --SSIS package name TABLE:(SELECT * FROM [SSISDB].internal.packages)
, @folder_name=@SSISFolderName --Folder were the package lives TABLE:(SELECT * FROM [SSISDB].internal.folders)
, @project_name=@SSISProjectName --Project name were SSIS package lives TABLE:(SELECT * FROM [SSISDB].internal.projects)
, @use32bitruntime=FALSE
, @reference_id=NULL --Environment reference, if null then no environment configuration is applied.
, @execution_id=@ChildSSISExecutionId OUTPUT --The paramter is outputed and contains the execution_id of your SSIS execution context.

b) Set required parameters for the child package. This is where we send over our data from parent -> child package parameters.

In this exaple we populate two parameters (object_type 30) called "ParentPackageExecutionID" and "ServerName", as well as one Environment setting (object_type 50).

Also note that we need to provide the execution_id (stored in @ChildSSISExecutionId in this example) that was generated in step a) to ensure that our settings are attached to the correct SSIS run.

EXEC [SSISDB].[catalog].[set_execution_parameter_value]
  @ChildSSISExecutionId  -- The execution_id value we received by calling [create_execution]
, @object_type=30  --30 is Package Parameters, you can also use 20 for Project parameters or 50 for Environment
, @parameter_name=N'ParentPackageExecutionID'  --Parameter name
, @parameter_value=@ParentSSISExecutionId

-- set SYNCHRONIZED Flag to wait for Child process end
EXEC [SSISDB].[catalog].[set_execution_parameter_value]
  @ChildSSISExecutionId
, @object_type=50
, @parameter_name=N'SYNCHRONIZED'
, @parameter_value=1

-- set SSDB Server Name
EXEC [SSISDB].[catalog].[set_execution_parameter_value]
  @ChildSSISExecutionId
, @object_type=30
, @parameter_name=N'ServerName'
, @parameter_value=@ServerName

c) After finishing our configuration we need to tell SSIS to actually run our package. Once again we need to supply the execution_id here.

EXEC [SSISDB].[catalog].[start_execution]  @ChildSSISExecutionId

d) optional (but advisable): monitor the execution of our child package and throw an error if needed. This allows the parent process to react in case the child process ran into an issue.

DECLARE @ErrorMessage NVARCHAR(MAX)

SELECT @ChildSSISExecutionStatus = [STATUS]
  FROM [SSISDB].[catalog].[executions] WHERE execution_id = @ChildSSISExecutionId

IF (ISNULL(@ChildSSISExecutionStatus,0) <> 7) 
BEGIN
  SELECT TOP 1 @ErrorMessage = 'Execution path ' + execution_path + ' failed with error: ' + [message] 
    FROM SSISDB.catalog.event_messages 
   WHERE operation_id = @ChildSSISExecutionId 
     AND event_name   = 'OnError';
  THROW 50000, @ErrorMessage, 1
END

Hope this helps.

Kind regards, Chris

相关问题