CODEWITHSUNDEEP
c#ssis
Isaiah Nelson
Isaiah Nelson

Reputation: 2490

Local or remote programmatic execution of an SSIS package?

I have an SSIS package on my desktop, which I registered to Integration Services on a remote box.

The service I am going to use to execute the SSIS package I have registered on that box will be hosted on an app server else where in the network and will be making a call to execute the package.

Before I begin writing the final code to make this happen, I have come to a fork in the road on how to proceed:

MSDN has two articles on how to programatically access an SSIS package.

One article is titled: Running an SSIS Package Programmatically on a Remote Computer

The other article is titled: Running an SSIS Package Programmatically on a Local Computer

I have read both, but its confusing to me how they are using the terms Local and Remote. \

It seems the Local Article says that even if you have a package on another computer, with this implementation it gets run on your local machine.

With the Remote Article it seems like the package will only execute if the service that calls it is running on the same machine. To me, this seems like a true "local" implementation.

Which article / implementation would I need to follow in order execute a package as I described above?

Again, I am running a Windows Service on my workstation and trying to execute a package that is registered on another. To me, this seems to be the Remote route, but I don't know.

Help! Thanks.

Upvotes: 1

Views: 2914

Answers (1)

luviktor
luviktor

Reputation: 2270

Yes, you are right, it's a little bit confusing. You should read this blog entry as well. Michael Entin posted a whole bunch of opportunities. He is focusing on the local/remote execution ways of running packages.

However, there is another way, through PowerShell.

In your case, I think it will not be good if you implement a web service on the application server. If you use SMO in this web service, the package will executed on the same server, which don't have SSIS installed, I guess. So this will fail. If you install your web service on database server, it'll be ok. But this way, you should open a specific port on this server. (Ok, I know if you implement the PowerShell way, there two ports have to be opened.)

Update

I try to explain on a little example. Say you developed a WinForms application in which you have a task that will launch an SSIS package. You installed this app on your application server. There is another server with SQL Server (and SSIS also) installed. This is your database server. When we talk about local/remote package execution we watch the system from the point of view of the WinForms app. Because the SSIS component is installed on the remote server (i.e. database server), you can only use the remote execution. So your windows service should be implemented on the database server. Another question might be how to reach this windows service. In the remote execution article you previously mentioned are two different ways of implementing remote execution: through SQL Server Agent job, and through a web service. The third option would be PowerShell as I mentioned above.

Of course from the point of view of the job or web service it is a true local implementation, as you wrote. But the MSDN articles focuses on your application on your application server (WinForms app for instance).

Upvotes: 3

Related Questions