Passing values returned by Execute SQL Task to data flow queries


  • I've been scouring the Internets for a solution to this newbie question. I can get part way there with what I've read, but now I need somebody to take me the rest of the way. Here's the scenario:

    I have 11 data flows, one for each table that I am copying from one database into another. Seven of these data flows have queries that require a StartDate and EndDate variable. I've added the Variables at the package level. 

    Based on what I've read, I created another table in my source database that contains only two fields and one record. You guessed it. The fields are StartDate and EndDate and values in the record are the date for StartDate and the date for EndDate. I created an Execute SQL Task that returns the StartDate from the table and assigns it to a ResultSet as NewStartDate. I tested it and it runs successfully. do I use this info? How do I tell my seven data flow queries to use this StartDate value? 

    Bonus question. Do I need to create a separate Execute SQL Task for EndDate? I'll be happy just to get the StartDate to work, but ultimately I need both.

    Thanx for your consideration.


    Friday, July 19, 2013 2:19 PM


All replies

  • Hi todtown,

    In the Data flow, please put a derived column with Name as DStartDate and in the expression box put the vStartDate variable and use DStartDate as the SourceMapping column in the destination of data flow to 7 of data flow you needed.

    Thanks- Prajesh Please mark the post as answered if it answers your question

    Friday, July 19, 2013 2:26 PM
  • Thanx Prajesh. I'll try this. It also occurred to me after posting my question that if I'm going to store the StartDate and EndDate in a table in the source database, I can also just reference this table in the queries that require these values. I'll try it both ways. 
    • Proposed as answer by Prajesh Friday, July 19, 2013 3:48 PM
    Friday, July 19, 2013 2:36 PM
  • I already have a Data Conversion in my Data Flow, between the source and destination. Do I put the Derived Column before or after the Data Conversion?

    Also, is there another way to do this? It seems (from a newbie perspective) that if I set the Package-level Variables with a default that I can also have something at run time that changes the value of these Variables. 

    So if I have a Variable:

    Name: StartDate

    Scope: Package

    Data Type: String

    Value: 2013-06-01

    Is there a way at run time I can change the Value to something else?

    If I can do that, then my queries can get the value from there.


    Friday, July 19, 2013 4:00 PM
  • You can use the derived column before or after if there is no data conversion involved else use before the data coversion. to run the variable at run time is not a issue for you to use the changed value in destination table need derived column

    Thanks- Prajesh Please mark the post as answered if it answers your question

    Friday, July 19, 2013 6:41 PM