0

I am encountering a frustrating problem with SSIS. The background is as follows:

  1. I have an excel file with 5 sheets - the first sheet is named "Meta" (the remaining 4 sheets are not relevant to this problem at hand)
  2. Inside the Meta sheet, I have a listobject/table called "Tbl_Meta" with 6 columns. The listobject has headers but will only ever hold a single row of data (so headers on row 1 (A1:F1) and data on row 2 (A2:F2))
  3. Of interest in Tbl_meta is the 2nd column which has a column header called GUID in cell B1. The data row under this column header holds a string value (lets assume "XXX-YYY-ZZZ-1234" for purposes of this example) in cell "B2"
  4. I need to extract this single cell value ("B2") from this excel file and hold it in an SSIS variable. Ultimately, this SSIS variable will be used to populate 3 sql tables with the GUID value extracted in point 3 above.
  5. I am attempting to do this through a Execute SQL task in SSIS. So I fired up a new package and configured an Excel connection, traced the path to the Excel file that holds the Meta sheet and in the SQL query I inserted Select * from [Meta$B2:B3] as various google searches indicated.
  6. Previous to point 5, I created a variable ("Output") which would the result of the SQL query, that would be subsequently used to insert the value into multiple SQL tables. The "output" variable is package level and of the datatype string
  7. In parallel, set the record set to "single row" and in the result set mapping, I mapped a "Output" against result name '0'
  8. In the belief that everything is running fine, I fired the package. However, I am continuously encountering the error pasted below

The following may be factored while forming any responses

  1. I believe I have not made any error in connecting the file
  2. I have checked (and rechecked) that the Meta sheet has data in cell B2
  3. There is nothing parameterized that is being passed to the SQL query - it is a straightforward "Select * from [Meta$B2:B3]" operation directly entered into the SQL query field of the SQL task window
  4. I inserted a breakpoint on post execute and sure enough there is nothing against the "output" variable in the locals window

Everything considered, there appears to be very little in the environment to reduce a grown man to tears after struggling with this for the best part of a day.

And yet..............

Your truly Suffering-like-I-never-believed-a-small-task-would-make-me-do

Error: 0xC002F309 at Execute SQL Task, Execute SQL Task: An error occurred while assigning a value to variable "Output": "Single Row result set is specified, but no rows were returned.". Task failed: Execute SQL Task

Mangésh Méhendalé
  • 47
  • 6
  • In General page > connection > SqlSourceType as `Direct Input`. You need to select `[User::output]` or `[User::Output]` as `variable name` in `Result` page of `Execute SQL Task` pop up. Please check your `variable name` while you select. – JIKEN Sep 16 '19 at 19:31
  • Thanks Jiken - let me try it that way. What i did do in the interim was to use the script task to achieve the same result. Certainly not what I wanted since the script used is in C#, a language which I am not familiar with. While the results are along expected lines, the approach leaves me feeling a little wary since a small deviation or change would mean a change to the C# code - a dreadful proposition given the circumstances. So all in all, I would still seek to do this through the SQL route. Happy to try your suggestion and see if that works – Mangésh Méhendalé Sep 17 '19 at 07:33

0 Answers0