If you decide to upgrade from SSIS 2008 to SSIS 2012 you might decide to use the Project Deployment Model and deploy your SSIS packages to the SSISDB instead of using the Package Deployment Model and deploy your packages to the File System. The Project Deployment Model brings a lot of advantages, but also some issues that you have to solve. One of these issues is how to call packages that are part of a different SSIS project. For example you have several SSIS projects, e.g. one project for the packages that load the dimensions, one project for fact packages, one project for workflow packages. 
In SSIS 2008 Package Deployment Model to File System you can use Execute Package Tasks to control the execution order of your packages. If you want to do the same in SSIS 2012 you get invalid tasks. 
In SSIS 2012 Project Deployment Model it is not possible to start e.g. the dimension packages from the workflow package with the Execute Package Task, because they belong to different projects. 
To solve this issue you can use an Execute SQL Task, because when you execute packages on the server the execution information is inserted in the SSIS internal tables and then stored procedures are executed that run the SSIS package. To get the relevant code open SSMS, navigate to the folder in the SSIS catalog where your SSIS package is located, right-click on the package name and choose execute. 
In the pop-up screen press the button „Script“. to get the code. 
This generates the following code:
-
Declare @execution_id bigint EXEC [SSISDB].[catalog].[create_execution] @package_name=N’Load_DimProduct.dtsx‘, @execution_id=@execution_id OUTPUT, @folder_name=N’_TRDemo‘, @project_name=N’Dimension‘, @use32bitruntime=False, @reference_id=Null Select @execution_id DECLARE @var0 smallint = 1 EXEC &SQUARE_BRACKETS_OPEN;SSISDB].&SQUARE_BRACKETS_OPEN;catalog].&SQUARE_BRACKETS_OPEN;set_execution_parameter_value] @execution_id, @object_type=50, @parameter_name=N’LOGGING_LEVEL‘, @parameter_value=@var0 EXEC [SSISDB].[catalog].[start_execution] @execution_id
Copy this code to the Execute SQL Task that replaces the Execute Package Task. 
The Execute SQL Task needs to connect to the SSISDB and execute the generated code. 
If a Connection Manager to the SSISDB is not yet available then create it as a project connection. Repeat this for all packages that you want to execute. 
Execute the workflow package in the SSISDB and check the results in the execution reports of the workflow package and the packages that you call in the workflow package. You will see that the workflow package started the other packages and that the other packages were executed successfully. 
In my next post I’ll explain how to make this solution more flexible. The current solution still has some disadvantages like redundant T-SQL code in every Execute SQL Task and it only works for exactly one environment reference.
Kommentare (0)