how do i run package from web service

Question

Post reply

how do i run package from web service

sshimshon

SSC Rookie

Points: 32
More actions
December 22, 2008 at 6:17 am

#125293

hi all.
my package is hosted on an ssis server called: MyServer
Name of package : MyPackage
in my c# web service i dont know how to connect to this package, and how to get the resaults that the DataReader inside returns.
can you please give me a simple example of the connection string to the ssis server?
and after connection - how do i get the resaults to a simple datareader

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Erik Kutzler SSCarpal Tunnel Points: 4287 More actions · Answer 1

http://msdn.microsoft.com/en-us/library/ms403355(SQL.90).aspx#service

sshimshon SSC Rookie Points: 32 More actions · Answer 2

the article does not say how do i get the actual datareader that was returned, and not the PackageResault which meens nothing.

and also - i didnt understand where should i host the web service? on the sqlserver? on my iis server?

Paul_J Hall of Fame Points: 3698 More actions · Answer 3

I don't quite understand what you're trying to do. If you want to execute a query and get the datareader back in the web service, why do you need an SSIS package?

As for your other issue, hosting a web service can be done in several ways. A ASMX web service in IIS is one. Another option is to write a WCF service and host it in a Windows Service. But before considering these, please make sure an SSIS is actually needed.

sshimshon SSC Rookie Points: 32 More actions · Answer 4

1) i have a simple ssis package:

inside the package i have a join between 2 tables from 2 diffrent Db.

and the package returns a datareader.

this is why i need an ssis package.

2) we have a sql2005 server and an integration server also.

my package is hosted on the integration server.

3) my application client is a winform application.

4) my B.L is represented by an asmx webservice, and i want it to execute the package from the server and return all the rows from the datareader.

thats it.

i wrote this code:

------------------------------------------------------------

using Microsoft.SqlServer.Dts.Runtime;

Application app=new using Microsoft.SqlServer.Dts.Runtime.Application();

Package pkg=new Packgae();

pkg=app.LoadFromSqlServer("\\MyPkgName","MySqlServerName",Null,Null,Null);

-----------------------------------------------------

SO, now - how do i get the rows from the datareader?

jdgonzalez@jdandrachel.com Hall of Fame Points: 3161 More actions · Answer 5

I'm also interested to see if this can be done as I've been tasked to do something similar. The only two solutions I could come up with were to created a linked server to your other data and just query using the linked server. Or have a step in your SSIS package that inserts into a table where you can have your webservice return data from.

I just haven't found a way of getting visibility to the datareader in SSIS.

J.D.

Piotr.Rodak SSCrazy Eights Points: 9376 More actions · Answer 6

I don't think you can return a data reader from a pack. You could probably store the merge output in an ADO dataset and store it in variable which you would then access after the pack has finished. This may be messy.

If you don't have too much data you can store it as xml and open reader on the xml data source.

Yet another option is to store output of the merge into a CSV file and have it read from it using CSV data provider.

Regards

Piotr

...and your only reply is slàinte mhath

Paul_J Hall of Fame Points: 3698 More actions · Answer 7

A package cannot really "return" a datareader. As has been suggested, perhaps you can try storing the results in a variable and access it that way after the package has executed. I don't know how successful you will be as I've never tried it.

The linked server solution may be the way to go. Having an ssis package just to join two tables from diff servers seems like a clumsy solution.

Best of luck.