none
Append the recordset destination

    Question

  • Hi,

    In dataflow i put the data set into Recordet Destination to use that dataset in my controlflow. In controlflow i have to query from another table and this result set should be append into same recordset object variable.But in control flow if i give that same object variable i will lose that old dataset.

    I need both data set (from data flow and control flow) in one object variable.After that i should use Foreachloop container.

    Wednesday, March 14, 2012 12:41 AM

Answers

  • Hi - in which case, definitely use 1 data flow task

    Have one flat file connection to get your flat file data - add a derived column to this with a hardcoded value of "1"

    an OLEDB connection to query your database for the single value - add a derived column to this with a hardcoded value of "1"

    do not add any precedence constraint between these 2 items

    add a merge join to take inputs from both of them and  join on your 2 derived columns

    take the output fropm teh merge join and push nto your ADO destination


    Rgds Geoff
    ----------------------------------------------------------
    Mark as Answer if this resolves your problem or "Vote as Helpful" if you find it helpful.

    • Marked as answer by neel24 Friday, March 16, 2012 6:19 PM
    Friday, March 16, 2012 4:59 AM

All replies

  • do both queries in 1 data flow then use the union all element to join them together and output to your recordset destiation

    Rgds Geoff
    ----------------------------------------------------------
    Mark as Answer if this resolves your problem or "Vote as Helpful" if you find it helpful.

    • Proposed as answer by Koen Verbeeck Wednesday, March 14, 2012 7:13 AM
    Wednesday, March 14, 2012 2:08 AM
  • Thank you GeoffBarraclough.

    I am very new to SSIS. Maybe i will ask silly questions.I have one more doubt.

    If i want to add one column in data flow what should i do? ie I am getting three column from flat file source and i have to add one more column value from another table. The fourth column is gonna same value for whole data set.So, i dont want to check the flat file data set.Simply i want to add one more column to my data set.

    Wednesday, March 14, 2012 6:01 PM
  • Hi GeoffBarraclough,

    I am gonna do what did you suggest. But i am wondering is there any way to append my recordet in control flow? If i need to add data set from control flow?

    Wednesday, March 14, 2012 7:19 PM
  • You can't append, join, merge, or otherwise modify recordsets in the control flow without resorting to Script Tasks, which just about defeats the purpose of using SSIS to get the recordset(s) in the first place.

    Use a Data Flow Task to issue the same queries using OLE DB Sources, use the Union All to "append" two sources' rowsets into one, then the Merge Join to stitch on your fourth column from the third source.  (Perhaps you need a Cross Join technique to do so.)


    Todd McDermid's Blog Talk to me now on

    Wednesday, March 14, 2012 8:58 PM
    Moderator
  • Thanks Todd.

    I am not not getting what did you mention.why do i want to use two sources with same query and union all?

    Could you please explain step by step?

    Wednesday, March 14, 2012 10:48 PM
  • Neel24 hello,

    Just join the two sources as described here:

    http://sqlvillage.wordpress.com/2011/07/28/joining-datasets-in-ssis-merging-recordsets-in-ssis-merge-join-transformation-editor/, you then can dump the resulted [merged] data into the Recodset Destination as explained over here: http://sqlserverrider.wordpress.com/2011/10/27/in-memory-data-processing-ssis/. And finally, shredding the recodset is done this way: http://www.sqlis.com/post/Shredding-a-Recordset.aspx

    Have fun!


    Arthur My Blog

    Thursday, March 15, 2012 12:57 AM
    Moderator
  • You don't want to use the same query in two sources.  You mentioned that you had two queries and that you wanted to append one to the other.  Do that using two sources in the Data Flow and use a Union All to "append" one to the other.

    Todd McDermid's Blog Talk to me now on

    • Proposed as answer by Eileen Zhao Thursday, March 15, 2012 7:52 AM
    Thursday, March 15, 2012 1:38 AM
    Moderator
  • neel24 - what is your exact requirement?

    is it simply to do 1 query from flat file and another query to be used to add a column to the flat file data or do you also need to add another query into the mix as well?


    Rgds Geoff
    ----------------------------------------------------------
    Mark as Answer if this resolves your problem or "Vote as Helpful" if you find it helpful.

    Thursday, March 15, 2012 9:00 AM
  • Hi Geoff,

    I am getting 3 column from flat file source and to add one more column to that. Before starting my logic i want 4 column in my data set.

    Thursday, March 15, 2012 10:30 PM
  • Hi - in which case, definitely use 1 data flow task

    Have one flat file connection to get your flat file data - add a derived column to this with a hardcoded value of "1"

    an OLEDB connection to query your database for the single value - add a derived column to this with a hardcoded value of "1"

    do not add any precedence constraint between these 2 items

    add a merge join to take inputs from both of them and  join on your 2 derived columns

    take the output fropm teh merge join and push nto your ADO destination


    Rgds Geoff
    ----------------------------------------------------------
    Mark as Answer if this resolves your problem or "Vote as Helpful" if you find it helpful.

    • Marked as answer by neel24 Friday, March 16, 2012 6:19 PM
    Friday, March 16, 2012 4:59 AM
  • thanks Geoff.
    Friday, March 16, 2012 6:19 PM