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
rss2Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.