March 23, 2011 at 4:49 pm
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?
March 23, 2011 at 5:31 pm
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.
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
March 23, 2011 at 5:39 pm
Regarding the Script Component, how would I set the DTS.Variables("").Value to the value from the spreadsheet?
March 23, 2011 at 6:07 pm
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.
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
March 24, 2011 at 1:57 am
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
March 24, 2011 at 2:05 am
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... 😀
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
March 24, 2011 at 2:10 am
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