November 16, 2010 at 2:17 pm
hi all
I was wondering if anyone could help, I need to pass 2 variables to a stored procedure that in turn fires off an SSIS job that calls a stored procedure.... (dont ask!). I can get the variables into the stored procedure no problem, but getting them through to the job is proving harder than I would expect. I can set static params through the Set Values option but cannot see a way of making them dynamic ?> any thought greatly appreciated
ta
November 16, 2010 at 2:23 pm
This gets complex, but the simple form of the method is this:
You don't call the job, you call the SSIS package (dtsexec.exe) and via that method, you feed it variable parameters in a command line.
OR
You create a local variables table. You feed/set those variables when the proc is called. Make sure you lockout other users from being able to simultaneously call the job. sp_applock, for example. You then use an Execute T-SQL task to feed the results to local SSIS variables.
THEN
The SSIS package then feeds those values up into local variables, which in turn are used as parameters in another 'execute T-SQL' task, against the target proc.
---
All in all, incredibly convoluted. Why can't you just exec proc across a linked server? Less bandwidth and pain, and much less obfuscated.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 16, 2010 at 2:37 pm
hi
i figured it wasnt going to be easy.... anyway, I did start off down the route of temporary tables but that was looking decidedly bad news, I prefer the idea of calling it command line style but how would I do that ? dont I need to be using xp_cmdshell or something like that to get to it.
ps this is a webserver, calling a remote db server so whatever happens it needs to happen on the db server from the call on the webserver. eeesh why are these things never simple.
November 16, 2010 at 2:43 pm
I still think you're locked into a bad approach.
You can call a foreign procedure from a local one. For example, lets say I had a linked server DWServ2, and I'm currently logged into OLTPServer1.MyDatabase.
CREATE PROC dbo.CallOtherProc
@param1 INT,
@param2 VARCHAR(100) = NULL
AS
EXEC DWServ2.MyDatabase.dbo.ForeignProc @p1 = @param1, @p2 = @param2
GO
This calls the foreign proc from the local one, with no mucking about trying to fight concurrency issues.
Otherwise, yes, dtsexec requires xp_cmdshell access to try to call it from within a proc.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 16, 2010 at 2:56 pm
unfortunately dont have sql on the web box, otherwise this would be a lot simpler, the main problem is, is that the stored procedure that gets called generates the data that gets put into a temporary table created by the SSIS packagethat gets used by SSRS afterwards, so....
we have SSIS calling a stored proc and passing two variables to it and putting the result into a temporary table.
once this has finished successfully the SSRS gets called and uses that data to makes the reprot, this is then discarded.
if I could just call the stored proc that would be great, but that doesnt work as the data is lost, almost as much as i am now......*sigh*
November 17, 2010 at 3:44 am
in the end I rewrote the dtsx package as a query and stored proc and it works fine, i have no idea why the dtsx package was even there tbh, seemed a pointless overhead and obstacle and not acheiving anything that couldnt be done simply in code.
cheers for the pointers though
(btw last mth - i had the joy of doing a similar task but having to move(BAT file with xcopy) after they uploaded a excel file from webserver to dbserver to run a stored proc to kick the job off to run the dtsx using jet4.0 driver to import the excel into the db on a 64bit server.....ah joy is me!_) you HAVE to run that outside of the package store (filesystem) but execute it via the command options in the job, thus avoiding the need for xp_cmdshell.
if anyone wants/needs to know the exact process for that gimme a shout as it can be a total nightmare as there is sooo much conflicting info on how to do it.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply