rss2
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(JobN ame, 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.GetStoredPr ocCommand( "sp_start_ job");
_msdb_database.AddInParame ter(dbJobC ommand, "job_name", DbType.String, JobName);
_msdb_database.ExecuteNonQ uery(dbJob Command);
}
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
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(JobN
DTSExecResult result2 = package2.Execute();
Console.WriteLine("Package
}
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.GetStoredPr
_msdb_database.AddInParame
_msdb_database.ExecuteNonQ
}
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
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.GetStoredPr ocCommand( "sp_start_ job");
_msdb_database.AddInParame ter(dbJobC ommand, "job_name", DbType.String, JobName);
_msdb_database.ExecuteNonQ uery(dbJob Command);
}
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.
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.GetStoredPr
_msdb_database.AddInParame
_msdb_database.ExecuteNonQ
}
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.
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
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
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("tes t_rmr", "bbamdwdev01", null, null, null);
Thank you!!!
rss2
Package package2 = app.LoadFromSqlServer("tes
Thank you!!!
rss2
Rss2,
You can use trusted connection in the LoadFromSqlServer using DTSSQLStgFlag_UseTrustedCo nnection
See this link:
http://sqldev.net/dts/ExecutePackage.htm
Helped?
Regards!
pedro
You can use trusted connection in the LoadFromSqlServer using DTSSQLStgFlag_UseTrustedCo
See this link:
http://sqldev.net/dts/ExecutePackage.htm
Helped?
Regards!
pedro
ASKER
There is no DTSSQLStgFlag_UseTrustedCo nnection using the Microsoft.SqlServer.Dts.Ru ntime 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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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