I developed several SSIS solutions to load data into our CRM application. The solutions are all developed using SQL Server 2008 Service Pack 1. Each solution has its own SQL Server database which are all created in the same SQL Server instance. Each solution contains several packages, which run in sequence because of the dependencies of the data to be loaded, and are all scheduled using the SQL Server Agent. Some of the solutions run simultanuously others are scheduled at other times. Each solution has at least one package in which an Execute SQL Task is setup as follows : 1. ConnectionType = ADO.NET 2. SQLStatement = a stored procedure. The ADO.NET connection manager is setup in a standard way except for the RetainSameConnection property which is set to True. The stored procedure has several input parameters and 1 output parameter. In the stored procedure the output parameter has the data type INTEGER. In the Execute SQL Task the output parameter has the data type Int32 and the variable receiving the value of the output parameter also has the Int32. When the package runs the following error message on the Execute SQL Task, and more specific on the variable receiving the value of the output parameter, is triggered : Executing the query "Query" failed with the following error: "The type of the value being assigned to variable "Variable" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. ". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. The type of the value being assigned to variable "Variable" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. The error causes the package to fail and the solution run to stop running. The reason why the property RetainSameConnection of the ADO.NET connection manager is set to True is the following : when it is set to False we encounter the following error message when one of the last packages of the solution starts : Executed as user: 'User'. Microsoft (R) SQL Server Execute Package Utility Version 10.0.2531.0 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 1:30:00 AM Error: 2010-11-06 01:33:31.14 Code: 0xC00291EC Source: 'Task Name' Execute SQL Task Description: Failed to acquire connection "Connection". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 1:30:00 AM Finished: 12:04:42 AM Elapsed: 81284 seconds. The package execution failed. The step failed. This message also causes the package to fail and the solution to stop running. In an attempt to solve the problem I installed the Cummulative Update Package 10 for SQL Server 2008 Service Pack 1 but this didn't solve the problem. I am looking for a quick and easy solution for this issue. Changing the connection type to an OLEDB connection manager could be a solution but because of the frequent use of this situation in the several solutions it could take me a lot of time and is therfore not preferable. My questions are : 1. If I leave the RetainSameConnection property on False : Is there a property on the SQL database or the SQL instance to increase the number of simultanuously connections to avoid the second error described above ? 2. If I want to set the RetainSameConnection property to True : What causes the first error described above and how can it be avoided ? 3. Is there no solution for this issue except switching to an OLEDB connection manager ? Regards. Dirk Diels

what did you set as sqlstatement in execute sql task? how did you set input parameters and result set?http://www.rad.pasfu.com

The SQLStatement property is filled with the name of a stored procedure which is created in the SQL Server database. The IsQueryStoredProducedure property is set to True. The ResultSet property is set to None. All the input parameters are set as follows in the Parameter Mapping property of the Execute SQL Task : Variable Name : the name of a user variable created in the package with Scope = package Direction : Input Data Type : a data type that corresponds to the data type of the input parameter in the stored procedure Parameter Name : the name of the input parameter in the stored procedure Paramater Size : -1 The output parameter is set as follows in the Parameter Mapping property of the Execute SQL Task : Variable Name : the name of a user variable created in the package with Scope = package Direction : Output Data Type : a data type that corresponds to the data type of the output parameter in the stored procedure Parameter Name : the name of the output parameter in the stored procedure Paramater Size : -1

All looks correct till this step, Now:does this stored procedure returns a value or a result set? if yes, did you set ResultSet Property in general tab and also result set tab ? how did you set them?http://www.rad.pasfu.com

The stored procedure returns a value. The ResultSet Property is set to None in the General Tab.

if stored procedure returns a single value and you want to fetch this value in a package variable, first create new variable, with appropriate data type, let's name it ResultValue then in execute sql task editor, set ResultSet property in general tab as "Single Row" and in result set tab: set variable name as User::ResultValue , and result name as the name of column which returned by stored procedure.http://www.rad.pasfu.com

I would like to have some more explanation on this recommondation. What I don't understand is why no error was encountered as long as the RetainSameConnection property of the ADO.NET connection was set to False. The definition of the Execute SQL Task was not altered, i.e. the ResultSet Property was always set to None on the General Tab and the value was always passed via an output parameter set in the Parameter Mapping tab. The only thing that was changed is the RetainSameConnection property by setting it to True. Can you explain why or how changing the RetainSameConnection property can cause the issue ?

I can't implement the solution you suggested because the stored procedure doesn't have a column that returns the result. In the stored procedure an output parameter is defined of data type INTEGER. At the end of the stored procedure this output parameter is set to 0 or 1 and the definition of the parameter in the Parameter Mapping tab of the Execute SQL Task assigns this value to a variable of type Int32. I.e. the stored procedure sends only 1 value back not a result set. OK, you could say that this 1 value is a single row result set, but what should I put in the Result Name column of the Result Set tab. Let's say the output parameter is called @o_output in the stored procedure should I than put @o_output also in the Result Name or just o_output or what ? And what should I do with the definition for the output parameter in the Parameter Mapping tab ?

if your stored procedure returns data with output parameters so you leave result set property as None as beforehttp://www.rad.pasfu.com

why you set RetainSameConnection as true? what you want to do exactly?http://www.rad.pasfu.com

I don't want to be rude but I think you need to read my original message again. You would see than that I mentioned that the stored procedure returns the data via output parameters and also the reason why I want to use the RetainSameConnection property. Originally the property was left to the default value of False but than the following error was triggered when one of the last packages of the solution starts : Executed as user: 'User'. Microsoft (R) SQL Server Execute Package Utility Version 10.0.2531.0 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 1:30:00 AM Error: 2010-11-06 01:33:31.14 Code: 0xC00291EC Source: 'Task Name' Execute SQL Task Description: Failed to acquire connection "Connection". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 1:30:00 AM Finished: 12:04:42 AM Elapsed: 81284 seconds. The package execution failed. The step failed. This message causes the package to fail and the solution to stop running. I thought that too many connections where open simultanuously on the SQL Server database which caused this error. Therefore I thought, after some investigation in SQL Server Help and on the internet, that by setting the property to True the problem would be solved but then I got the other error when running the solution : Executing the query "Query" failed with the following error: "The type of the value being assigned to variable "Variable" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. ". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. The type of the value being assigned to variable "Variable" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. The error causes the package to fail and the solution to stop running. One extra remark which could be relevant : we use the 64 bit version of SQL Server 2008.

Any other suggestions ?