CODEWITHSUNDEEP
ssissql-server-2012ssis-2012
Melanie
Melanie

Reputation: 3111

Access multiple Connection Managers in SSIS 2012 Execute SQL Task

I have an SSIS 2012 project which consists of one Execute SQL Task. The package must be deployed to a SQL Server so that it can run independent of user intervention. The T-SQL accesses two different SQL servers, like this:

INSERT INTO Server1.MyDatabase1.dbo.MyTable1
SELECT * FROM Server2.MyDatabase2.dbo.MyTable2 T2
WHERE T2.MyField2 NOT IN (SELECT MyField1 FROM Server1.MyDatabase1.dbo.MyTable1)

This works fine, but is obviously not very portable. I'd like to create two Connection Managers for Server1 and Server2 and refer to the values of the Connection Managers in the T-SQL. The Connection Managers are also contained in the Project.params, so I could use the Project.params instead. At any rate, is it possible to refer to Connection Managers or the Project.params in an Execute SQL Task? Or can I create variables from them and reference the variables in the Execute SQL Task? Or do I need to go about this in a completely different way, such as pulling both data sets in via Data Flow Tasks and then processing them?

Upvotes: 0

Views: 1420

Answers (1)

Tab Alleman
Tab Alleman

Reputation: 31775

One way to do this is to use variables:

  • Variable1: holds the name of Server1
  • Variable2: holds the name of Server2
  • Variable3: holds your entire SQL Statement and is constructed using Variables 1 & 2.

Then your execute SQL task uses Variable 3 as its source.

However, the statement will only be run on one ConnectionManager, so it will have to be one that can run the statement using the 4-part naming convention as shown in your question.

Upvotes: 1

Related Questions