SSIS set stored procedure parameter dates via SQL agent

Question

SSIS set stored procedure parameter dates via SQL agent

acrutchley

SSCrazy

Points: 2117
More actions
August 15, 2014 at 9:39 am

#289054

Hi All,
I am new to SSIS and have been trying to work out how to do the following:
To set the scene I am using SQL 2012, in project deployment mode (SSIS Catalog rather than file system).
I have setup an SSIS package to run a stored procedure which exports data for the last hour to a .tsv file and then FTP's the file to some other location via a sql agent job - This all works fine.
However, I can see there may be a requirement to run the package with dates that need to be set i.e. in the event of a lost file of some other reason the package has not run and missed some of its hourly slots and the customer requires the files to be resent.
The stored procedure I am using has parameters for "DateOverride" - boolean), "start" and "end" dates (datetime) with defaults set "0" for "DateOverride" and null for the "Start" and "End" dates, I have built logic into the procedure which sets the dates if the parameters are null (as in the above to an hour before now). What I would like to be able to do (and this is to make it user friendly for support staff) is to be able to set parameters/variables in SQL agent with "DateOverride" set to "1" and the the dates I would like to be sent to the stored procedure "Start" and "End" parameters.
I did try using the parameters in SSIS which worked well when the values were true or false (0,1) but didn't work at all for the dates. If I left the dates as I had set them is SSIS it worked, but if I changed them (even if it was just changing the hour) the job errored/crashed and corrupted the job step leaving me the ability to only delete it (couldn't edit the job) (please see my other post for details: "DateOverride")) which I am not sure if its a bug or my lack of knowledge.
Any help with this will be amazing.
Thanks is advance,
Adam

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Adam

A SQL Agent job is intended to run unattended, so you can't pass parameters to it. You could create a table with date column(s) that contain the dates to be passed in. If there are no rows in the table, run the stored procedure with the default value(s).

John

acrutchley SSCrazy Points: 2117 More actions · Answer 2

Hi John,

Thanks for you reply.

I am not trying to pass parameters to the the agent, rather set parameters/variables/overrides within the agent. There is a new parameter section with SQL Intergration services jobs for parameters which I tried but didn't work.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 3

Adam,

I have a similar concern for one of my packages. I approached this differently, however. I use a SQL Server Configuration Table and have a parameter which is basically 'LastRunTime'. We set that value at the end of the successful package so the next one knows where to start. It's the simplest method I've found and also allows us to modify it if we know something else went wrong and need to rerun it.

- Craig Farrell

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

acrutchley SSCrazy Points: 2117 More actions · Answer 4

Hi Craig,

That's a really good point and one I will certainly use. It makes a lot of sense to do check the last run date. I am just really suprised that the new ssis 2012 parameter which provides parameters in sql jobs doesn't allow you to dynamically update them unless the value choice is already known e.g true/false.

Thanks again,

Adam