locked
How can I take a column from a data source and put it in a parameter ? RRS feed

  • Question

  • I have a simple task but can't figure out the mechanics.

    I am successfully moving data from point A to point B, using a Sql command from variable, this works great.

    But now I want to take one of the columns from this query and populate another variable so I can use that in another subsequent data flow.

    So how do I do this ? Copy column didn't work, and Derived Column looked promising, but that didn't work as I thought it might either.

    My source query returns 1 row, and I want to take the value of one of the columns and populate a variable for later use. 

    Here's the data I'm trying to move

    HDR
        Detail
            Detail Child record
    Trailer

    I think the issue is I don't understand how SSIS works . . . 

    In oracle, I would write a cursor for HDR, maybe it pulls 5 records.

    So I fetch first record, insert it to the dest table HDR in a different d.b.
      then, while holding this record, 
          I pull all related detail records that have the Header.UniqueKey as FK
                   while processing each detail record, I similarly look for children of the detail
          after all details are inserted, I'll then use the header in context and get the trailer
          then fetch the next header record and begin the above sequence again.

    So how do I accomplish in SSIS ?
    Thursday, October 31, 2013 2:41 PM

Answers

  • Sorry in turn if have misled you.

    If you need to capture a single value off the data stream and put it into a package variable then use the Script Task: http://toddmcdermid.blogspot.ca/2010/08/use-readonlyvariables-and.html

    If you want to assign the contents of the column to a package variable on every record in the data stream you then need to use the Script Component set as transformation: http://www.bimonkey.com/2009/09/the-script-transformation-part-1-a-simple-transformation/


    Arthur My Blog

    Tuesday, November 12, 2013 3:32 PM

All replies

  • The Derived Column should be the way to go, I am unaware of what exactly did not go well. You just need an expression to work. Alas need more details.

    Arthur My Blog

    Thursday, October 31, 2013 6:47 PM
  • foreach loop container is what would work for what I am trying to do. I am bringing an Oracle SP mindset and that was my perspective in tackling this in SSIS. 

    I don't think derived column works either, as I've already tried that. In derived column, I was attempting to kidnap a column and assign it to a variable. But as I see how derived column works, I can assign a variable to a column, but not assign a column to a variable. Does that sound right ?

    Monday, November 11, 2013 7:22 PM
  • You got a new column (Derived Column 1) but you use the same variable in it, not an expression

    you need to replace it with an actual expression because in your scenario all the values will be the same.


    Arthur My Blog

    Monday, November 11, 2013 8:18 PM
  • sorry, I'm not following you.

    So I want to put my column VERS_NBR into the variable varTest, is the pic below the right way to do it ?

    This doesn't seem right to me, because it looks like it's taking what's ALREADY in varTest and using THAT to be VERS_NBR - - - but I want the reverse . . . I want to take what's retrieved IN VERS_NBR and assign that to varTest . . .

    Tuesday, November 12, 2013 2:39 PM
  • Sorry in turn if have misled you.

    If you need to capture a single value off the data stream and put it into a package variable then use the Script Task: http://toddmcdermid.blogspot.ca/2010/08/use-readonlyvariables-and.html

    If you want to assign the contents of the column to a package variable on every record in the data stream you then need to use the Script Component set as transformation: http://www.bimonkey.com/2009/09/the-script-transformation-part-1-a-simple-transformation/


    Arthur My Blog

    Tuesday, November 12, 2013 3:32 PM
  • Ok, those are good links. but if you go to my original question, it still looks like foreach loop is what I want.

    I suppose I'm also not using SSIS as intended, which is to zap from point A to point B.

    What I'm trying to do is more traditional - create a cursor, then process each record in the cursor (header).

    As I have the 1st header record on context, then I go get all associated details and insert them to the detail table.

    then fetch the next cursor and repeat.

    So I think I'm good, thanks for the tips.

    Tuesday, November 12, 2013 7:51 PM