Execute SSIS packages remotely
Hi How to run SSIS package stored in MSDB or file system using ADO command or using a program remotely. If ssis package is not scheduled as a job as I have some security issues with scheduling SSIS packages in SQL Server Agent. I was looking at the article below but it calls a web service and then that is called in a console application is there a way of doing it in one go? http://msdn.microsoft.com/en-us/library/ms403355.aspx#agent Thanks in advance.......
July 11th, 2012 10:17pm
I would advice to resolve security issues rather than deploying custom code to server running SSIS. If you don't want to use SQL Agent, there are many ways to execute package, including building web service, windows task scheduler, RPC, SQL Server Broker... Non of the is perfect and each of these solution will fit some business requirements so please elaborate on the limitation you are facing.
July 11th, 2012 10:27pm
that solution is based on running SSIS package on remote server. Do you want to store SSIS package on remote server but run it from client machine? in this case you need to have client SSIS tools installed on client machine. or if you want to run the package on same machine that it is stored, you can use this way: http://msdn.microsoft.com/en-us/library/ms136090.aspx http://www.rad.pasfu.com
July 12th, 2012 12:15am
You can try a batch script like tgis, ECHO EXECUTING DWH MASTER ECHO ********************************* DTEXEC /DTS "\MSDB\\%DestinationFolder%\MasterDWH" /SERVER %Server% /CHECKPOINTING OFF /REPORTING V ECHO **********************************
July 12th, 2012 12:18am
Thanks all for your response. This is where I am stumped, we would like to give our business users capability of pulling data into flat files from different systems via SSIS packages and I created a batch file as Kingxxx1 suggested on the SSIS server and gave users access but they are not being able to run, few members from the IT team can run, not sure why?, as a result I am looking for a fix to that as well as other different potential options. Regards................
July 12th, 2012 12:54am
For this scenario I would advice different approach: 1. Create network share on SSIS server. 2. Create SSIS package and run it on SSIS server: a) on schedule every few minutes and check for new files; or b) once, constantly and use WMI watcher to wait for new files 3. let end users just upload files to the share
July 12th, 2012 1:49am
this question may be suited best for the asp.net forum. if you want to use windows form application in C#, you can give users a form with uploadfile control(you can find numerous example for ti in net). then you can create a sql job which will execute the ssis package deployed to the server. from the form application, you can execute the sql job. http://blog.the-dargans.co.uk/2007/02/executing-job-on-sql-2005-server-in-c.html regards joon
July 12th, 2012 5:02am
Thanks all for your inputs I finally was able to create a web service to run ssis packages. I implemented sourcetype: file system. Refer to this msdn article for more details: http://msdn.microsoft.com/en-us/library/ms403355.aspx#agent Regards..........
July 17th, 2012 12:01pm