Run SSIS packages from remote client with stored procedure

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Jason Rowland » Run SSIS packages from remote client with...

25 posts, Page 1 of 3

1 2 3 »»»

Run SSIS packages from remote client with stored procedure

Rate Topic

Display Mode

Topic Options

Author

Message

JasonRowland

Posted Monday, January 11, 2010 8:38 PM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, June 13, 2013 10:23 AM
Points: 89, Visits: 706

Comments posted to this topic are about the item Run SSIS packages from remote client with stored procedure

Post #845825

SQLRNNR

Posted Tuesday, January 12, 2010 12:07 AM

SSCoach

Group: General Forum Members
Last Login: Today @ 3:33 PM
Points: 18,858, Visits: 12,443

Thanks for the article. I hadn't considered using the Broker for this before - I will be giving this a try.

Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp

Post #845890

ALZDBA

Posted Tuesday, January 12, 2010 12:26 AM

SSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 2:13 PM
Points: 6,866, Visits: 8,071

For sure this is a nice way of launching a package when you have no SQLAgent in your system ( express edtn )

But if you have SQLAgent in your instance, I would prefer to have your sproc or service broker starting a job that runs your wanted package.
Mainly for the management and up reasons that come with sqlagent.
e.g. you can disable a job (works only if you don't use sp_startjob ! ) , have a look at how many times it has been executed and when, ...
You wouldn't need to open xp_cmdshell !

cfr "help to tighten use of cmdshell or sp_start_job" http://www.sqlservercentral.com/scripts/Miscellaneous/31032/

Johan

     Jul 13

Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help

- How to prevent a sore throat after hours of presenting ppt ?

"press F1 for solution", "press shift+F1 for urgent solution" BigGrin

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me Hehe

Post #845895

gchornenkyy

Posted Tuesday, January 12, 2010 6:45 AM

Old Hand

Group: General Forum Members
Last Login: Thursday, February 14, 2013 2:39 PM
Points: 311, Visits: 277

Could be helpful:
"Expert SQL Server 2005 Integration Services" by Brian Knight and Erik Veerman
(Wrox Press 2007, 432 pages)

Chapter 12: Scaling SSIS -> Package Execution Principles -> Distributed Package Execution

Regards,
Gennadiy Chornenkyy

Post #846095

LeeFAR

Posted Tuesday, January 12, 2010 7:20 AM

SSC Veteran

Group: General Forum Members
Last Login: Monday, February 13, 2012 8:30 AM
Points: 207, Visits: 192

I agree with ALZDBA. We have done the same thing by calling the appropriate SP to kick off a job that runs the package.

Post #846142

nlarge

Posted Tuesday, January 12, 2010 7:47 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, May 09, 2011 7:38 AM
Points: 5, Visits: 48

Jason,

Just like in IT there is *most* of the time a solution that suits the problem. In this case, although the desired result is to *run* a package remotely, I have a couple of points that I wish to raise. One is that there is no transparency in this method. That is, if the package fails, how does one extrapolate the point in the package that failed? Also, how does one manipulate the variable assignments in the packages, both before and after the process has run in order to both reuse packages by reassigning them new variable values etc. Also, if we look at this process in a production environment, are we getting enough bang for our buck?

I was faced with the issue of having to run packages from an ASP.NET 2.0 application and required that there was total transparency on error handling and variable manipulation, as well as being aware of the security risks associated with a production environment when implementing procedures that execute on demand transactions against SQL Server, so I chose a different method.

The method I chose was to ask a fellow collegue to create me a WCF web/windows service that sits on the SQL server running SSIS. This WCF application exposes objects that can be manipulated to run a package, and, at any time, expose variables [via packagevariable() array object], errors etc [basically the ssis object model] to allow me to set variables in the package, run the package, extrapolate success and failure, and evaluate the values of variables following execution.

I hope that this adds resolve to this jigsaw puzzle in a more flavourable way.

But as I say at the begining of this post, the solution should fit the problem ...

Thanks, Nick.

Post #846166

JasonRowland

Posted Tuesday, January 12, 2010 7:55 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, June 13, 2013 10:23 AM
Points: 89, Visits: 706

There is another advantage to using this as opposed to SQL Agent. You can regulate the number of packages that execute concurrently by setting the max_queue_readers value.

The following example would ensure that at the most, 3 packages can run simultaneously.
create queue
package_Queue
with status = on,
activation
( procedure_name = spExecQueuedPackages,
max_queue_readers = 3,
--Change this value to allow more or less packages to be executed on this queue simultaneously.
execute as 'dbo' );

Post #846174

ALZDBA

Posted Tuesday, January 12, 2010 8:15 AM

SSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 2:13 PM
Points: 6,866, Visits: 8,071

Jason Edward Rowland (1/12/2010)

euhm ... unless someone actually runs "start dtexec ..." which doesn't wait for the command to complete.

Then it would just blow up your server Wink

Johan

     Jul 13

Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere w00t

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me Hehe

Post #846197

JasonRowland

Posted Tuesday, January 12, 2010 8:52 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, June 13, 2013 10:23 AM
Points: 89, Visits: 706

If you use the stored procedure, "start dtexec" won't be executed, but "dtexec" will and it will wait for the command to complete.

Post #846250

Ben Sullins-437405

Posted Tuesday, January 12, 2010 9:54 AM

SSC Veteran

Group: General Forum Members
Last Login: Monday, October 25, 2010 10:10 AM
Points: 297, Visits: 267

Would this work if the SQL Database Engine was not installed on the same box as SSIS? To my knowledge SSIS packages can only be called locally and in a large distributed architecture I generally don't run SSIS on the same box as the DB. For remote package execution however you can build a web service to run locally on the SSIS boxes that can call the packages. I've seen this method work very elegantly in the past...

Cheers,

Ben Sullins
bensullins.com
Beer is my primary key...

Post #846327

« Prev Topic | Next Topic »

25 posts, Page 1 of 3

1 2 3 »»»

Permissions