In my last post I explained how to start a SSIS 2012 package that is located in a different SSIS project.
However, this solution is not very flexible, because the code is always only valid for one package and you have to repeat all the steps again and again. In case of errors this could mean a lot of work to correct all Execute SQL Tasks. And because of a fixed environment reference it is nearly impossible to deploy the packages from e.g. the development server to production server.
The solution is to create a user-defined stored procedure in the SSISDB that contains all the code and provides the flexibility that you need. Add the following parameters to the procedure
– PackageName = The name of the package that you want to execute
– FolderName = The name of the folder where your project is stored
– ProjectName = The name of the SSIS project the package belongs to
– EnvironmentName = The name of the SSIS environment that you want to use
– ExecuteSync = A flag to control if the package is executed synchrnously (=1, my recommendation) or not (=0)
– LoggingLevel = To set the SSIS logging level (default should be 1)
The procedure first has to select the proper enviroment reference from the SSISDB tables internal.folders, internal.projects and internal.environment_references using the parameters FolderName, ProjectName, EnvironmentName.
Next use SSMS to create the T-SQL code to start a SSIS package on the server (as described in part 1), copy the code to the stored procedure and modify the code to use the parameters of the stored procedure.
Last you should check the execution status of the SSIS execution. If the SSIS package did not run successfully and you chose synchronous execution you can raise an error to stop the workflow package.