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.
1条答案
按热度按时间mzmfm0qo1#
i just stumbled across this topic on accident. Coincidentially I had to solve a similar problem last year.
DISCLAIMER:
Scenario:
My approach was as follows:
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.
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.
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.
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.
Hope this helps.
Kind regards, Chris