3

Here's my situation:

I have two servers, Server A has the Database Server installed and the SQL Agent (SQL Server 2008 Enterprise Edition, 64-bit). Server B has a vendor specific application that needs SSIS to reside in the same server for some reason. As per MSDN licensing I can't have more than 1 instance of SSIS per license of SQL Server. So we went ahead and installed SSIS only to Server B, and applied the configuration instructions found in http://msdn.microsoft.com/en-us/library/ms137789.aspx as to tell it that the database is in Server A.

I am able to run the package manually using DTEXEC on Server B and it ran without errors. However, I am unable to configure an SQL Agent job on Server A that will execute the package against Server B's SSIS install. I get an error about needing to have SSIS installed in Server A.

The question is, how do I tell the Agent in A to execute the package using SSIS on B?

enriquein
  • 252
  • 1
  • 3
  • 10

1 Answers1

2

It seems that this is just not possible. After wasting about 10 hours or so of our "Microsoft Premier" support hours, the only answer that keeps coming up is "execute dtexec remotely on the machine that has SSIS".

So I'm going to post this as the answer in hopes that some SQL MVP will show up and prove me wrong.

As far as how to do that, I'll probably take advantage of our existing Control-M agents or make a small .Net app that invokes the command via WMI.

The thing that bothers me the most is that I have found licensing documents from Microsoft that state that you can have SSIS living in a separate server from the Database Engine and that it should just work.

enriquein
  • 252
  • 1
  • 3
  • 10
  • I also haven't been able to get my SSIS instance to use a remote msdb instance to store packages. Oh well - take documentation with a grain of salt I guess :( – Sam Aug 10 '10 at 17:03
  • Well configuring SSIS to use a remote msdb is covered by http://msdn.microsoft.com/en-us/library/ms137789.aspx We did this at work and it worked ok (both launching the package through dtexec and within SSMS). – enriquein Aug 12 '10 at 19:17