Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
scabral7
Yak Posting Veteran
57 Posts |
 Posted - 2013-06-06 : 21:54:07
|
| I have an SSIS package that has a variable called @RUN_DATE.I would like to create a sql agent job and uses the set values tab to pass a dynamic parameter to the ssis package. for example, i would like to always set the variable to the last day of the previous month. I know that If i hardcode the date as 5-31-2013 it works fine, but i have not been able to figure out how to make the value dynamic by using an expression or if this is even possible.any help would be appreciated.thanksScott |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-07 : 01:52:08
|
| You can use a Execute sql task inside and use a query like below to assign value to the variableSELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)-1this would set it to last day of prev monthHowevevrIf you want to pass it from job you can always pass value through dtexec call and dispense with above step------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
scabral7
Yak Posting Veteran
57 Posts |
Posted - 2013-06-07 : 10:21:33
|
| I understand this will work, but what I want to do is be able to set this script as the "Value" field in the set values tab of the sql agent job that runs the SSIS package.Hard coding a date works fine, but I want to make it dynamic so I don't have to change the date each month. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-07 : 10:39:07
|
| you're not hardcoding even in my method. It changes dynamically based on GETDATE value and will automatically change for each month!------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
scabral7
Yak Posting Veteran
57 Posts |
Posted - 2013-06-07 : 11:34:03
|
| how about if i want to re-load the table for a previous month end, say 1-31-2013? I would have to open the SSIS package and manually change the date. By passing the value as a parameter in the sql agent job, i should be able to default to last day of previous month, and then change it accordingly to whatever month I need to re-load if needed. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-08 : 03:20:34
|
| you can set values in sql agent job step properties set values tab for variables too if you want to pass from job. And if you want to have control over this from outside then you can use a control table approach where you can set the date via an insert/update and as the first step inside ssis you can use execute sql task to get value from table using a select (just like what i told before but using select column from table rather than expression involving getdate())------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|
|
Pass dynamic value to ssis package