CODEWITHSUNDEEP
ssissql-server-2012
Echo
Echo

Reputation: 1137

SSIS: how to create connection manager to SQL Server Package Store

In current SSIS solution, I need a connection manager to SQL Package Store (MSDB) where my packages deployed to.

I could connect to MSDB in SSMS by choosing "integration service". But I do not find the same connection type when establishing connection manager in visual studio. The only thing I could use is "Ole DB" connection, which seems could not identify integration service.

P.S. Version of SQL-Sever integration service is 2012. It's using default server name (localhost) and no instance name is set.

Upvotes: 0

Views: 1241

Answers (1)

Kyle Hale
Kyle Hale

Reputation: 8120

There is currently no way in SSIS 2012 to execute a package that is part of SSIS 2012 Project deployment model using an Execute Package task unless both the master and child package are in the same project.

So my first question would be: why do you have a master package that is not part of its child package's project (or vice versa)? Clearly they're connected in some non-arbitrary way, so they should probably be part of the same project.

That having been said, the only way to accomplish this is to use an Execute SQL task and invoke SSISDB's internal stored procedures to kick off an execution of your remote package. Please see this excellent article by Reza Rad on the particulars of those stored procedures.

Upvotes: 0

Related Questions