Problem with the new SSIS task for executing a package in SQL Server 2012

Question

Hi all,

I'll be very grateful if someone could tell me a solution to this or at least a workaround.

I have a package created and stored in the new Integration Service catalog on SQL Server 2012, this package should reference bunch of other packages from an other project (through execute package tasks). As I cannot have those packages added to the existing project I need a way to reference them from my current package/project.

The problem here is the new deployment model only allows to reference a package from the current project, from file and from MSDB. I do not want to have any packages in the MSDB as well, or on the filesystem so I need some help.

Thanks.

Answer 1

you can use Execute SQL Task and then use stored procedures to run specific package in other project

I knew that this is not good way to do it, but as you saw Execute Package Task with Project Reference will work under same project. rational for that is each ETL / Data transfer project should be seprate from other projects and they don't have any inter-relation together. this is best practice, but if you need to do that anyway you can use the Execute SQL Task method.

Read here to know how to run package with stored procedures in SSIS 2012:

Create_Execution :

http://msdn.microsoft.com/en-us/library/ff878034.aspx

Start_Execution:

http://msdn.microsoft.com/en-us/library/ff878160.aspx

---

http://www.rad.pasfu.com

Answer 2

I explained a bit more in details about this method here:

http://rad.pasfu.com/index.php?/archives/70-SSIS-2012-Execute-Package-from-Another-Project.html

---

http://www.rad.pasfu.com

Answer 3

I actually found this work around, but I was hoping there is another way. As this means that I'll have to create stored procedures to run the 32bit packages and the 64bit ones and also give access to developers on the SSISDB to execute the SPs.

Answer 4

Hi Rudy_gd,

I agree with Reza, use stored procedures in Execute SQL Task to call SSIS packages is a workaround for you. Otherwise, you need create a Web service to run packages programmatically in script task, for more details about coding, please refer to: http://msdn.microsoft.com/en-us/library/ms403355.aspx#service 

Thanks,
Eileen