SSIS package parameters

• SkyBVI

  SSCoach

  Points: 15128

  More actions

  May 12, 2011 at 11:44 am

  #250173

  Hi,

  I have a SSIS package which has a t sql query running. In that:

  select ...

  from...

  WHERE DT > '2011-04-01'

  and DT < '2011-04-30'

  I want to put parameters for date and I want to run this in a job step in SSMS.

  But the point where I am stuck is that, I want these dates to be 1 week earlier from the time the job runs...

  FOr example

  If job runs on sunday night (15th may)

  Then the dates should be from 8th may - 14th may

  Similarily, the next job runs on 22nd may and dates should be from

  15may-21 may.

  Also, how could the data add upto the previous data..

  Like if the first time job runs i have 400 rows( in output table),

  then next weekend the job runs another 450 rows, so I want my output table to have 850 rows in total..

  Somebody can give me an insight on how to proceed :

  Thanks

  Regards,

  Sushant

  Regards
  Sushant Kumar
  MCTS,MCP

• Koen Verbeeck

  SSC Guru

  Points: 259085

  More actions

  May 12, 2011 at 12:50 pm

  #1323914

  I don't remember exactly how it works, but maybe you could look into the "job tokens".

  http://msdn.microsoft.com/en-us/library/ms175575.aspx

  I don't know if they're the solution, but you could give it a try...

  Need an answer? No, you need a question
  My blog at https://sqlkover.com.
  MCSE Business Intelligence - Microsoft Data Platform MVP

• vani_r14

  SSCrazy

  Points: 2843

  More actions

  May 12, 2011 at 11:11 pm

  #1324085

  Hi

  Regarding your first question - on dates - I needed to do something similar to that. So this is what I have done.

  Created a table in SQL SERVER with two columns Start Date and End Date

  Created a Job that has Step to find out the Date you need in this case start and end of the week. The step will calculate the date and enter it in to the table above.

  THe SSIS Package will then use those dates to run.

  How to schedule a job to run the SSIS package, i have a document which i have attached to help.

  Please let me know if there is anything else that i can possibly help you with

  Cheers

• Anjan Wahwar

  SSCommitted

  Points: 1617

  More actions

  May 13, 2011 at 4:41 am

  #1324178

  Why not you go for passing 2 parameters that will take value from GETDATE()-7 days to GETDATE()-1 day . This will be better approach than creating table as changing schema is not always a great approach.

  And reg your 2nd question If my understanding is correct (you want to accumulate multiple runs data into same output table), SSIS, by default will apend data to your destination table. No need to worry abt that at all.

  __________________________________________
  ---------------------------------------------------
  Save our mother Earth. Go Green !!!