2

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.

Result Set mapping in SSIS .

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:

SSIS Execute SQL Task - General tab .

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?

  • If you are using ODBC connector just put 1 in ResultName tab instead of keeping column name. – Subrat Jan 24 '17 at 13:05
2

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)

1

Yes, you can do this with an ODBC connection. However, you don't have flexibility with naming your result set when using ODBC. Trying changing your Result Name "MyNVarchar1" to the number "0" and changing "MyNVarchar2" to the number "1". ODBC wants to reference these columns as 0, 1, 2, etc.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.