Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Append the recordset destination

Answered Append the recordset destination

  • Wednesday, March 14, 2012 12:41 AM
     
     
    Sign In to Vote
    0

    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.

     

All Replies

  • Wednesday, March 14, 2012 2:08 AM
     
     Proposed Answer
    Sign In to Vote
    1
    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.

     
  • Wednesday, March 14, 2012 6:01 PM
     
     
    Sign In to Vote
    0

    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 7:19 PM
     
     
    Sign In to Vote
    0

    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 8:58 PM
    Moderator
     
     
    Sign In to Vote
    0

    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 10:48 PM
     
     
    Sign In to Vote
    0

    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?

     
  • Thursday, March 15, 2012 12:57 AM
    Moderator
     
     
    Sign In to Vote
    0

    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 1:38 AM
    Moderator
     
     Proposed Answer
    Sign In to Vote
    0
    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

     
  • Thursday, March 15, 2012 9:00 AM
     
     
    Sign In to Vote
    0

    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 10:30 PM
     
     
    Sign In to Vote
    0

    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.

     
  • Friday, March 16, 2012 4:59 AM
     
     Answered
    Sign In to Vote
    0

    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 6:19 PM
     
     
    Sign In to Vote
    0
    thanks Geoff.