In SSIS 2012, I'm using an "Execute SQL Task" to call a stored procedure that returns a single row with two columns. I'd like to map that result set to two SSIS user variables. Should be easy, right? This is works when I use an ADO.NET or OLEDB Connection Manager, but fails when I used an ODBC Connection Manager.
When I map using ODBC, SSIS returns the following runtime exception:
[Execute SQL Task] Error: An error occurred while assigning a value to variable "MyString": "Value does not fall within the expected range."
The SQL Server columns have a data type of nvarchar(50). I've also tried varchar(50) and char(1) to no avail. The SSIS variable "MyString" has a data type of String.
.
The ODBC connection's Code Page is 1252 and the SQL Server 2012 collation is SQL_Latin1_General_CP1_CI_AS.
I'm using a SQL Server 2012 database.
Here's the general setup of the task for reference:
.
Is this possible to do using an ODBC connection? Or is this yet another type of functionality that just isn't supported for ODBC in SSIS 2012?
Best Answer
With ODBC in SQL task the first position is 1 not 0, this totally cured my problem. Also note that ODBC does not seem to recognize the alias so do not use the alias as the resultName. It seems that the message "expected range" is referring to the result set positioning; i.e. '0' or a name is not seen in the range of variable positions (such as 1 and 2 if you have two selected, 0 or 3 is out of range)