asked on

Launch Sql job or SSIS package from web page

Hi Experts,

I would like to launch an SSIS package from a web page, but can't seem to get it to work. Preferably, I would like to run the SSIS job from the web page, which I also tried.

I need to know the execution status of the package while it's running, which I would like to report on the web page (I want the user to see that it's running, and to see when it's finished).

Here is my code so far:
public DataTable RunJob(string JobName)
{
Application app = new Application();

Package package2 = app.LoadFromSqlServer(JobName, xxxxx, null, null, null);
DTSExecResult result2 = package2.Execute();
Console.WriteLine("Package execution results: {0}", result2.ToString());

}

I've also tried calling the job, but the problem here is that I can't tell what the execution status is:
public DataTable RunJob(string JobName)
{
DataSet ds_job = new DataSet();
DbCommand dbJobCommand = _msdb_database.GetStoredProcCommand("sp_start_job");
_msdb_database.AddInParameter(dbJobCommand, "job_name", DbType.String, JobName);
_msdb_database.ExecuteNonQuery(dbJobCommand);

}

How can I launch a package from a web page, one that already exists, without supplying a username and password and just using the user's windows credentials? And how can I monitor it's execution status?

Thank you! Mucho urgento!

rss2

PedroCGD

Dear friend,
Im my blog there is a post about calling a SSIS Package from ASP page. Check it.

I can also call a SSIS package from a SQL Job, and for this you can call directly the SQL Job from an ASP aplication or from a Stored Procedure.

If you call SSIS package from ASP, you need to have installed SSIS on the WebServer (the server where you do the call)

MOre details, check here:
http://msdn.microsoft.com/en-us/library/aa337077.aspx

Helped?
Regards!
Pedro
www.pedrocgd.blogspot.com

Steve Hogg

Question 1: How can I launch an SSIS package from a web page. I see you got this to work?
I've also tried calling the job, but the problem here is that I can't tell what the execution status is:
public DataTable RunJob(string JobName)
{
DataSet ds_job = new DataSet();
DbCommand dbJobCommand = _msdb_database.GetStoredProcCommand("sp_start_job");
_msdb_database.AddInParameter(dbJobCommand, "job_name", DbType.String, JobName);
_msdb_database.ExecuteNonQuery(dbJobCommand);

}
This is the best way, it executes the package but does not wait for the job to finish. Save the package in a SQL Server Agent job.
Question 2: Monitor Status:
Run the sp_help_job procedure to get the status of the job. Also consider building meaningful logging into the SSIS package, perhaps even writing deatailed status to the database. You can then use this logging to query for details while the job is running.
Question 3: Use Windows Authentication, not an embedded connection with password.
It's all in the connection string. Trusted_Connection=Yes attribute, or the equivalent attribute Integrated Security=SSPI.

rss2

ASKER

So if I use sp_help_hob or if I use my own logging, what is the best way in the code to monitor the package's change in status from running to finished?

If I execute the job, as I do in the code above, then I exeucte sp_help_job against it, should I loop through running sp_help_job every 1 minute or something? Or should I have the page automatically refresh every minute or so? (If the latter, how do I do that in asp.net/c#?)

Many thanks!

rss2

rss2

ASKER

I like your solution, Pedro, but I used the other method because of the username/password issue. If I use the application class of the dts.runtime namespace to do this, how do I code the following line without having to include a username and password (in other words, just use trusted security)?

Package package2 = app.LoadFromSqlServer("test_rmr", "bbamdwdev01", null, null, null);

Thank you!!!

rss2

PedroCGD

Rss2,
You can use trusted connection in the LoadFromSqlServer using DTSSQLStgFlag_UseTrustedConnection

See this link:
http://sqldev.net/dts/ExecutePackage.htm

Helped?
Regards!
pedro

rss2

ASKER

There is no DTSSQLStgFlag_UseTrustedConnection using the Microsoft.SqlServer.Dts.Runtime reference.

Also, how do I find the location of the package? I think it exists as a File on the SQL Server rather than in the msdb database (select * from msdb.dbo.sysdtspackages returns 0 rows). How can I find out where it is and what the path to it is?

ASKER CERTIFIED SOLUTION

PedroCGD

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial