Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Run SSIS packages from remote client with stored procedure Expand / Collapse
Author
Message
Posted Monday, January 11, 2010 8:38 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 7:28 AM
Points: 89, Visits: 795
Comments posted to this topic are about the item Run SSIS packages from remote client with stored procedure
Post #845825
Posted Tuesday, January 12, 2010 12:07 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 5:28 PM
Points: 20,483, Visits: 14,136
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


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
Posted Tuesday, January 12, 2010 12:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, April 18, 2014 6:27 AM
Points: 6,997, Visits: 8,411
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


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

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #845895
Posted Tuesday, January 12, 2010 6:45 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 1:19 PM
Points: 311, Visits: 282
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
Posted Tuesday, January 12, 2010 7:20 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 20, 2013 5:39 PM
Points: 207, Visits: 197
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
Posted Tuesday, January 12, 2010 7:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, January 12, 2010 7:55 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 7:28 AM
Points: 89, Visits: 795
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
Posted Tuesday, January 12, 2010 8:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, April 18, 2014 6:27 AM
Points: 6,997, Visits: 8,411
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


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

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #846197
Posted Tuesday, January 12, 2010 8:52 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 7:28 AM
Points: 89, Visits: 795
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
Posted Tuesday, January 12, 2010 9:54 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC 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 »

Add to briefcase 123»»»

Permissions Expand / Collapse