Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2009
    Posts
    62

    Unanswered: Pass dynamic Value to SSIS package

    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

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    I haven't had much opportunity to play with SSIS yet, because most of the applications that I support are still in SQL 2000. *SOB* Therefore, my suggestions will be heavily flavoured with DTS.
    I think that the easiest way to achieve this is to create a table with two fields - VariableName and RunDate. Then add a step to your SSIS package upstream of the bit that requires the parameter that sets the required value in the new table for the relevant parameter, and then pulls that value into the parameter.
    HTH!
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Mar 2012
    Location
    Somewhere In Europe
    Posts
    24
    Hi
    I'm not quite sure that You have to pass it as dynamic parameter to the package.
    Imho it's better to create SQL task and pass single row of sql query
    Code:
    SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) AS LastDate
    as a ResultSetType_SingleRow to User::RUN_DATE.

    Thanks
    Mike

  4. #4
    Join Date
    Sep 2009
    Posts
    62
    What if i needed to reload the table for Jan 2013? I would need to go into the SSIS package manually and update the code. What I want to do is use the Value field on the SQL Agent Job to default the value to be the last day of the previous month. If i need to re-load the table for any prior month, I just need to update the Value field to the date that I need.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If you don't object to "majik numbers", you can calculate it more simply using:
    Code:
    SELECT DateAdd(m, DateDiff(m, 31, GetDate()), 30) AS prior_month_end
    This should also work just as well as an SSIS expression as it does for SQL Server, but I haven't actually tested that.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •