I have a SSIS package that I plan to deploy on my SQL Server 2008 machine.
I need to call this SSIS package remotely and synchronously from .NET. Obviously I need to pass parameters to this package as well.
How could I do that?
Thanks, Alex
A:
If Your SQL server is in safety environment You can try make SSIS Package runnable from Stred Procedure, Then I think that is your code can easy call stored procedure on SQL server.
This considering that you must enable xp_cmdshell (at least as I know for SQL2005) which is always mentioned as dangerous task according to server security.
adopilot
2010-05-20 21:33:45
+2
A:
You can make a job and then start the job from straight SQL using sp_start_job. This would be asynchronous.
Running on the server, it will need to have access to any files you might be reading in or writing out (it would also need to have the package - so if you are code-generating a package from a template or something, getting it onto the server might be an issue).
You can also use psexec to run a process on any remote server. In this case, it would be to run dtexec. This would get around having to enable xp_cmdshell. (same issue with needing to see the package and see any files)
Cade Roux
2010-05-20 21:46:22
This is similar to what I personally have used when faced this task. I also had to write a routing to poll if the job is still running - to make it synchronous - it ended up being a simple stored procedure.I tried both approaches the sp_start_job and psexec (and windows task scheduler with schtask command ) and they all work depending on your particular scenario, requirements and infrastructure
zespri
2010-06-14 10:35:16
A:
Another way is to write simple application exposing a web service that allows executing packages - SQL Books Online has a nice sample.
Michael
2010-05-22 07:13:32