Running a SSIS package remotely on the SQL Server

We have a package (which uses SQL Server Destination Task) imported in the SQL Server. Is it possible to invoke this SSIS package from a remote App Server through C# .NET.

We would like to execute the SSIS package in the SQL Server itself and pass on the results of execution to the calling C#.NET application via the output variables declared in the SSIS package.

Thanks,
Loonysan

You can do this with relatively few lines of code from either a remote component or a Web service, by using one of the Load... methods of the Application class, then calling the Execute method of the package.

-Doug

|||

If i have to write a remote component (may be a Windows Service) - then I need to host that in SQL Server. Is there any workaround without hosting any such remote compoenents in the SQL Server.

Also in some other posts - Someone has suggested the use of SQL Jobs to run SSIS packages remotely. I cannot use that approach also because I need to get some output values from the SSIS package variables. Also I am planning to run multiple instances of the same SSIS package in parallel (with different input parameters) - With the help of SQL Jobs - Running multiple instances in parallel isn't possible.

Thanks,
Loonysan

|||

I'm not sure what you mean by hosting a remote component IN SQL Server. If you mean a CLR stored procedure, unfortunately it's not possible to create a CLR stored procedure (or UDF, etc.) that uses the Integration Services API.

If you mean host a component ON the server where SQL Server is running, then you could configure that component as a remote component or a Web service that can be invoked remotely.

Run query with IP address

Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:509038
Hi
I want to run a query that connects to a remote SQL server via an IP
address, something like
SELECT * FROM [10.101.10.10].server.dbo.table
I can't get it to work. I can connect with Enterprise manager to the IP,
what am I missing?
Thanks
TimTim,
Try:
- Using rowset functions
- Creating a linked server
- Assign an alias using Client Network Utility
Example:
use northwind
go
select top 1
*
from
OPENDATASOURCE('SQLOLEDB', 'Network Library=DBMSSOCN;Data
Source=190.162.0.11,1433;Integrated Security=SSPI').pubs.dbo.authors
go
AMB
"Tim Cowan" wrote:

> Hi
> I want to run a query that connects to a remote SQL server via an IP
> address, something like
> SELECT * FROM [10.101.10.10].server.dbo.table
> I can't get it to work. I can connect with Enterprise manager to the IP,
> what am I missing?
> Thanks
> Tim
>
>|||When you use this syntax, SQL-Server will search in the list of linked
servers for a data source named "10.101.10.10". So if you create this
linked server (with this name), then the query could work.
Gert-Jan
Tim Cowan wrote:
> Hi
> I want to run a query that connects to a remote SQL server via an IP
> address, something like
> SELECT * FROM [10.101.10.10].server.dbo.table
> I can't get it to work. I can connect with Enterprise manager to the IP,
> what am I missing?
> Thanks
> Tim