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
3条答案
按热度按时间pkln4tw61#
When you configure an Integration Services catalog you can execute the packages from another project.
script
menu and selectNew query editor window
@execution_id
(not neccessary)DECLARE @var0 smallint = 1
line and replace@var0
with 1 (in the line it will be @parameter_value=1 )Now in your master package:
Execute SQL Task
OLE DB connection manage
r toSSISDB
databaseExecute SQL Task Editor
-->SQLstatement
: paste here the generated package scriptAn example of the script:
Passing parameter: Add the following two line to add pass a paremeter
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.
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 theexecution_parameter_values
. I've raised this issue with MS.I would have added as a comment but I don't have enough rep.
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.