April 23, 2009 at 11:23 am
How do I declare a runtime variable in SQL Agent and the pass it to an Execute SQL Task (stored proc) to be used in the data extraction.
Thanks
May 4, 2009 at 5:34 pm
Hi,
I would solve this issue by using a database table to store parameters to be passed to the stored procedures. You can also store many params for your database application. This global table helps store many other system metrics as well.
To create SQL Agent jobs I would prefer to use the following system stored procedures as well:
sp_add_job,
sp_add_jobstep
sp_add_schedule etc., which allows you to code in T-SQL.
Hope this helps...
May 7, 2009 at 9:59 am
Thank you so much. This was my original inclination. However, if I would need to have a job re-run (for specific dates), I wanted to be able to specify them in SQL Agent and I didn't know how to set this up. Alternatively, I can insert a new row in my parameter's table to reflect the dates of my re-run. This parameter is then read by the stored proc.
May 9, 2009 at 10:42 pm
one way that i have done this is to do the import through an SSIS package. Within the SSIS package, you can create a variable, and then you can dynamically build your SQL commands based on that.
Then, in the job step calling the SSIS package, you can set the value of the SSIS variable in the "Set Values" tab with this syntax:
Property Path Value
\Package.Variables[str_date].Value 05/09/2009
Within the SSIS package, you set up a date variable, and that is the variable you want to set through the job, then you have a second variable to store the sql command. You create a script task to build the command and set the command value.
Example of the vb.net code:
Dts.Variables("User::cmd_Import").Value = "SELECT * FROM dbo.table1 where date = '" & Dts.Variables("User::str_date").Value.ToString() & "';"
Your SQL command would then be available for the Data Flow Task logically after the script task in the package.
What I have done is have the str_date in the job normally be "TODAY" and then build two different Select statements within the script task based on that. If you leave the str_date value blank within the job, the job will remove that variable from the "Set Values" tab, so you need to have a value in there.
Example:
If Dts.Variables("User::str_date").Value.ToString() = "TODAY" Then
Dts.Variables("User::cmd_Import").Value = "SELECT * FROM dbo.table1 where date = getdate();"
Else
Dts.Variables("User::cmd_Import").Value = "SELECT * FROM dbo.table1 where date = '" & Dts.Variables("User::str_date").Value.ToString() & "';"
End If
In the end, if you need to re-run for a particular date, you just have to change the "Set Values" value from TODAY to whatever date you need it to be. After the run, you change the value back to TODAY and you're back to normal without modifying tables or anything else.
It sounds more drawn out that it actually is to implement.
Let me know if you need any help implementing this if it's something you want to try.
Steve
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply