Home » C# Programming

installation requirements to run SSIS or DTS packages in a job

Hi, my requirement is as follows. the SSIS and DTS packages are stored on the webserver (not in database) and I need to execute them on click of a button on an ASP.NET web page and be able to abort them (again by user action) if they hang for some reason. user doesn't have access to sql server management studio or enterprise manager. if I choose the option of executing the package in a job from this link http://msdn.microsoft.com/en-us/library/ms403355.aspx, then do I need to install anything else on the webserver apart from SSIS or DTS runtime libraries? where does the sql server agent windows service run? on the webserver? how does this whole thing work? Please let me know. thanks in advance.

6 Answers Found


Answer 1

Interesting link.

SQL Agent runs on the database  server.  You would use Agent to call the console app the article describes.

Not sure exactly what you'd have to install  on a system to make up an SSIS runtime, at least most of the appropriate version of the dotnet framework, which I suppose it might already have, but watch out for rev mismatches.

I believe there is a way to get Visual Studio to package  up an SSIS solution with all runtimes for deployment, but I've never actually looked for it or tried to run  it.

Actually, I will need to know this myself at some point, we have similar plans for eventual production deployment - not on the web  server, but on a non-database "ETL" server.  Though, whether that is actually a good idea or not, is debatable, a matter of whether you want to run SSIS on the database server  where it is close to the data but will steal memory and cycles from SQL Server, or off somewhere on a machine of its own but another network hop (or six) away from the database.


ps - the kill switch will be a challenge as well!



Answer 2


The following may help you:

1) Install dtexec utility on the webserver  or wherever your SSIS package  resides. Regarding the min requirements  please check this page: http://msdn.microsoft.com/en-us/library/ms162810.aspx ;

2) Create a .cmd/bat file that calls dtexec including all the needed parameters. Your webpage 's button  will trigger this .cmd file;

3) Now, to stop a package in this setup, one can use a precedence constraint for example a file, you can check if the file exists before executing  each next step in your SSIS package, this approach is discussed here: http://www.dotnetspider.com/forum/250123-Stop-SSIS-package-at-runtime.aspx. By the way to stop the package I thus suggest creating a second button.


Answer 3

This link   http://blogs.msdn.com/b/michen/archive/2007/03/22/running-ssis-package-programmatically.aspx says, installation  of SQL server  engine is required to run  the package  in a job. what does it mean by SQL Server engine? where should it be installed? according to the original MSDN link, the job  runs on the webserver  and executes the package locally on the server. I am not sure if just installing ssis  and DTS is enough for this thing  to work. is there a article that walks through the whole process of how to execute the package in a job along with what needs to be installed on the server for it to work? right now I am using SSIS and DTS object model to execute  the package, because I am not clear about running it in a job, but that is causing issues for me in cancelling DTS packages. there is no API to cancel a running DTS package. so I am thinking if I run it in a job, then I can stop the job by issuing stop command on the database  server.

Answer 4

Hi rkrishna:

SQL Server engine is basically an installation  of an SQL Server. But in your case it will probably not end with just an installation of the SQL Server engine itself, you will need also client tools as SSIS. You better install  it on another machine (best practice), then the SQL Server Agent needs to be set to run  your SSIS or DTS as a job. By using the Agent job  you will be able to stop it using a command you probably know. There is a difference between SSIS and DTS pckages. Do you have both?


Answer 5

thanks. yes, I have both SSIS and DTS (SQL server  2000) packages. are you saying just installing SSIS and DTS runtime  is enough on the webserver? and then we can configure the sql  Server agent  in database  server to execute  it in a job? I thought some additional software may be required on the webserver  to run  it in a job.

Answer 6

Well, I guess you already have SQL server  installed, and in any case installing it on your web  server is not a good practice. So, if you do have it already then it is just a matter of executing  the needed SQL Agent job  remotely (regardless it this is a DTS or ssis  packages). You can execute  the job using a custom built component. 

To start a job remotely use EXEC msdb.dbo.SP_START_JOB   N'MyJob';

To stop a job you execute EXEC msdb.dbo.SP_STOP_JOB   N'MyJob';

This kind of code can be placed in your component, it may be as simple as a VBScript or a command line application.



<< Previous      Next >>

Microsoft   |   Windows   |   Visual Studio   |   Sharepoint   |   Azure