• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3076
  • Last Modified:

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
0
rss2
Asked:
rss2
  • 3
  • 3
1 Solution
 
PedroCGDCommented:
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
0
 
HoggZillaCommented:
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.
0
 
rss2Author Commented:
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
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
rss2Author Commented:
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
0
 
PedroCGDCommented:
Rss2,
You can use trusted connection in the LoadFromSqlServer using DTSSQLStgFlag_UseTrustedConnection

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

Helped?
Regards!
pedro
0
 
rss2Author Commented:
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?
0
 
PedroCGDCommented:
I'll check if exists only in DTS (SQL 2000 version), but also try to send use and PWD as empty.
Connect to SSIS server to check the packages you have (see attached image)
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_23849072.html

You can also call a SQL Job (that call SSIS package) direcly in spite of calling SSIS.
Or you can use sp_start_job stored procedure to call the SSIS from a stored procedure. You have several ways to do your job.

SEE THIS VERY GOOD LINK FOR DIFFERENT OPTIONS
http://msdn.microsoft.com/en-us/library/ms403355(SQL.90).aspx

Helped?
regards!
Pedro
SSIS.JPG
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now