SSIS 2008 Question: Get One Value From One Excel Cell and Put in Variable

  • As the title says, I would like to take one value from a single Excel spreadsheet cell and put it in a package-level variable.

    I already know about OpenRowset in the Data Flow Task's Excel Source. The problem for me is, once I set the range to one cell, how do I put the contents of that cell in a package-level variable?

  • First thing that comes to mind is dropping it out to a recordset destination and then using that in a foreach loop (with 1 loop for your one row) in the control flow, or using a script object in the flow as a transformation and then set the dts.variables("").value in the script.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Regarding the Script Component, how would I set the DTS.Variables("").Value to the value from the spreadsheet?

  • imani_technology (3/23/2011)


    Regarding the Script Component, how would I set the DTS.Variables("").Value to the value from the spreadsheet?

    You wouldn't directly. You'd read the row into the stream in a datasource, then as the next step in the stream, use the script component. You'll be able to feed in the column from the stream (which goes row by row) and then set it there.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • You can just store it into a ResultSet.

    Select single line and store the value in a string variable. You'll avoid the for each loop this way.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Koen Verbeeck (3/24/2011)


    You can just store it into a ResultSet.

    Select single line and store the value in a string variable. You'll avoid the for each loop this way.

    *facepalm* Thanks Koen. I'll be back over there building a few more Rube Goldberg inventions... 😀


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Craig Farrell (3/24/2011)


    Koen Verbeeck (3/24/2011)


    You can just store it into a ResultSet.

    Select single line and store the value in a string variable. You'll avoid the for each loop this way.

    *facepalm* Thanks Koen. I'll be back over there building a few more Rube Goldberg inventions... 😀

    I had to look Rube Goldberg up on Wikipedia (yeah, I'm not really part of the American culture :-)) Great cartoons 😀

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply