February 24, 2011 at 7:44 am
Good day,
A programmer has given me a stored procedure that extracts data from a database. This Proc has two parameters (beginning date and ending date). What they are wanting is for this procedure to run, then FTP the results to a different server. That is no problem. But they want to be able to change the dates and run the job on an ad-hoc basis. My thought was to create a SQL Agent job to do run the stored proc. Then create another stored proc using the 'exec sp_start_job' that they can execute inputting the desired date parameters. I have not been able to figure out how to take the date parameters they provide and map it to the date parameters needed for the stored procedure. In my Package I have a Data Flow Task that includes an OLE DB Source (Where the stored Proc is run) with an output to a dynamically named text file. The completion of the dataflow task feeds into the FTP job. Can anyone help me to understand how to accomplish this. Do I have the logic all wrong and there is an easier way to do this?
Thank you in advance for your assistance.
February 24, 2011 at 3:13 pm
If I'm understanding this, you want to give someone the ability to call a stored procedure with a couple date parameters that you can then pass into an SSIS package.
There are probably a few different ways to do this. Here is my initial thought.
First in the package should have 2 variables for the from / to dates.
Setup a SQL job that calls the SSIS package.
Under the set values tab of the job step setup. enter the package paths to the variables such as \package\variables\Mydate.value and give it a starting value.
Then you can modify the job step command parameter by running something like the following:
EXEC msdb.dbo.sp_update_jobstep @job_id=N'2f50a50e-346f-493c-bde2-d9bfc037a79d', @step_id=1 ,
@command=N'/DTS "\File System\MyJobName" /SERVER "MyServer" /CHECKPOINTING OFF /SET "\package\variables\mydate.value";"1/1/1900" /REPORTING E'
and replace the value 1/1/1900 with the values passed in through the stored procedure. Of course on each run the value to replace will have changed.
Then use the sp_start job and it will use the new values you supplied when calling the SSIS package.
There may be a more elegant way of doing this but this would work.
You can generate the code by using the script button on the job setup page.
February 24, 2011 at 3:23 pm
Tom Van Harpen (2/24/2011)
There may be a more elegant way of doing this but this would work.
Not really, that's the first thing that came to mind for me, too, though you could do something similar with DTEXEC as well and avoid any SQL Job issues.
A question for the OP though, if users can initiate the SSIS package and job, how are you going to deal with attempted concurrent runs from users who don't talk to each other?
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
February 24, 2011 at 6:42 pm
You can check if the job is running before executing it. Let me know and I'll post the code.
February 24, 2011 at 8:30 pm
Tom Van Harpen (2/24/2011)
You can check if the job is running before executing it. Let me know and I'll post the code.
Oh, no argument. I just wanted to know what his ideas regarding concurrency were. sp_getapplock could be used for something like this, as well. If he needs multiple simultaneous runs, though, he's going to have to deal with file-naming, locking issues, the works on the fly. Just wanted to know where he was hoping to go with this.
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
February 25, 2011 at 5:52 am
That one is pretty simple, I only plan to give one person execute rights on the stored proc to launch the process. It's a very specific solution to a very specific problem.
February 25, 2011 at 9:40 am
February 25, 2011 at 10:29 am
SQLLawman (2/25/2011)
That one is pretty simple, I only plan to give one person execute rights on the stored proc to launch the process. It's a very specific solution to a very specific problem.
Ah, definately go with Tom's first recommendation then, that's the least painful of the options.
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply