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
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?