SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Run SSIS packages from remote client with stored procedure


Run SSIS packages from remote client with stored procedure

Author
Message
JasonRowland
JasonRowland
SSC-Enthusiastic
SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)

Group: General Forum Members
Points: 130 Visits: 1145
Comments posted to this topic are about the item Run SSIS packages from remote client with stored procedure
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32048 Visits: 18551
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, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

ALZDBA
ALZDBA
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12065 Visits: 8923
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


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont 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 :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
gchornenkyy
gchornenkyy
SSC-Addicted
SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)

Group: General Forum Members
Points: 440 Visits: 399
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
LeeFAR
LeeFAR
SSC-Addicted
SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)

Group: General Forum Members
Points: 418 Visits: 322
I agree with ALZDBA. We have done the same thing by calling the appropriate SP to kick off a job that runs the package.



nlarge
nlarge
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
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.
JasonRowland
JasonRowland
SSC-Enthusiastic
SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)

Group: General Forum Members
Points: 130 Visits: 1145
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' );
ALZDBA
ALZDBA
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12065 Visits: 8923
Jason Edward Rowland (1/12/2010)
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' );



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


Then it would just blow up your server ;-)

Johan


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont 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 :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
JasonRowland
JasonRowland
SSC-Enthusiastic
SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)

Group: General Forum Members
Points: 130 Visits: 1145
If you use the stored procedure, "start dtexec" won't be executed, but "dtexec" will and it will wait for the command to complete.
Ben Sullins-437405
Ben Sullins-437405
SSC-Addicted
SSC-Addicted (485 reputation)SSC-Addicted (485 reputation)SSC-Addicted (485 reputation)SSC-Addicted (485 reputation)SSC-Addicted (485 reputation)SSC-Addicted (485 reputation)SSC-Addicted (485 reputation)SSC-Addicted (485 reputation)

Group: General Forum Members
Points: 485 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...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search