SQL Server SSIS Master package executing packages from another project

ni65a41a  于 2023-08-02  发布在  其他
关注(0)|答案(3)|浏览(123)

I have multiple SSIS projects, but some of the packages inside them are the same.

I would like to create a project with all the generic packages and keep the others projects with theirs specific packages.

So my question is : is it possible to have a master package that can execute and pass parent variables to packages from another project ?

I'm new to SSIS so sorry if it's an obvious question or if i'm not specific enough

pkln4tw6

pkln4tw61#

When you configure an Integration Services catalog you can execute the packages from another project.

  1. After deploying your all projects in the SSIS Catalog. Go to SSIS Catalog and browse to the package you want to execute.
  2. Right click and select execute
  3. It will pop up a window and ask for required paramter to fill up then
  4. Click on the script menu and select New query editor window
  5. Extra steps - Delete the Select @execution_id (not neccessary)
  6. Extra steps - Delete the DECLARE @var0 smallint = 1 line and replace @var0 with 1 (in the line it will be @parameter_value=1 )
  7. Do not close the sql query window as you need to copy the generated script

Now in your master package:

  1. Add a Execute SQL Task
  2. Add OLE DB connection manage r to SSISDB database
  3. In the Execute SQL Task Editor --> SQLstatement : paste here the generated package script

An example of the script:

Declare @execution_id bigint  
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'testpackage.dtsx', @execution_id=@execution_id OUTPUT, @folder_name=N'TestFolder', @project_name=N'TestProject', @use32bitruntime=False, @reference_id=Null  
Select @execution_id --delete this line  
DECLARE @var0 smallint = 1 `-- delete this line`   
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50, @parameter_name=N'LOGGING_LEVEL', @parameter_value=@var0 `--(replace this @var0 with 1)`  
EXEC [SSISDB].[catalog].[start_execution] @execution_id  
GO

Passing parameter: Add the following two line to add pass a paremeter

DECLARE @ReportDate nvarchar(100) = `?`  
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=30, @parameter_name=N'ReportDate', @parameter_value=@ReportDate

Now from "Parameter Mapping" tab on the Execute SQL Task Editor add your variable you want to pass as a parameter.

Remember that variable data type has to be same as the parameter data type.

jhkqcmku

jhkqcmku2#

If you are leaving the Logging level as Basic (1), which is the default, you do not need to set this every time. Also, there is a bug in the SSISDB.[catalog].[set_execution_parameter_value] sp if you run a number of these steps in parallel. This can cause a DEADLOCK on the execution_parameter_values . I've raised this issue with MS.

I would have added as a comment but I don't have enough rep.

k5ifujac

k5ifujac3#

I followed the steps in the recommended answer and it worked great. One thing to add, we were using an Environment and Environment Variables, so when I clicked to create the script, there was an @References = 1. This defines which environment will be used when the package is executed. To find the correct number for your environments, right-click on the environment, and then look at the Properties. The ID will be there.

相关问题