SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 Pass dynamic value to ssis package
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

scabral7
Yak Posting Veteran

57 Posts

Posted - 06/06/2013 :  21:54:07  Show Profile  Reply with Quote
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.

thanks
Scott

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 06/07/2013 :  01:52:08  Show Profile  Reply with Quote
You can use a Execute sql task inside and use a query like below to assign value to the variable

SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)-1

this would set it to last day of prev month

HowevevrIf you want to pass it from job you can always pass value through dtexec call and dispense with above step

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

scabral7
Yak Posting Veteran

57 Posts

Posted - 06/07/2013 :  10:21:33  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 06/07/2013 :  10:39:07  Show Profile  Reply with Quote
you're not hardcoding even in my method. It changes dynamically based on GETDATE value and will automatically change for each month!


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

scabral7
Yak Posting Veteran

57 Posts

Posted - 06/07/2013 :  11:34:03  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 06/08/2013 :  03:20:34  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000