January 11, 2010 at 8:38 pm
Comments posted to this topic are about the item Run SSIS packages from remote client with stored procedure
January 12, 2010 at 12:07 am
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[/url]
Learn Extended Events
January 12, 2010 at 12:26 am
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
Learn to play, play to learn !
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[/url]
- 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
January 12, 2010 at 6:45 am
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
January 12, 2010 at 7:20 am
I agree with ALZDBA. We have done the same thing by calling the appropriate SP to kick off a job that runs the package.
January 12, 2010 at 7:47 am
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.
January 12, 2010 at 7:55 am
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' );
January 12, 2010 at 8:15 am
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
Learn to play, play to learn !
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[/url]
- 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
January 12, 2010 at 8:52 am
If you use the stored procedure, "start dtexec" won't be executed, but "dtexec" will and it will wait for the command to complete.
January 12, 2010 at 9:54 am
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...
Ben Sullins
bensullins.com
Beer is my primary key...
January 12, 2010 at 9:57 am
[Ben describes the method I use.]
January 12, 2010 at 10:16 am
Ben Sullins-437405 (1/12/2010)
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...
This solution will only work where Integration Services and SQL Server are both on the same machine.
It must be nice to have seperate hardware for each service.
January 12, 2010 at 12:50 pm
Jason, thanks for putting this together. I think this will prove useful to folks trying to solve the problem of running an SSIS package from a stored procedure.
One comment. You do not issue an END CONVERSATION in your the stored procedures which send and recieve messages. If you query SYS.Conversation_Endpoints in your database, I expect that you will see a number of conversations that are in your estimation over, but broker services still reports them as open.
To clean this up, you can run the following:
SELECT 'END CONVERSATION ''' + CAST(conversation_handle as char(36))+ ''' WITH CLEANUP'
FROM sys.conversation_endpoints
Then execute the output from the SELECT.
January 12, 2010 at 3:33 pm
You are correct macqueen, the end conversation is missing.
Thank you.
January 12, 2010 at 3:48 pm
It must be nice to have seperate hardware for each service.
It's actually more work...
Ben Sullins
bensullins.com
Beer is my primary key...
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply