I am having trouble with number of SSIS Packages, which fails sometimes, but not always. The error message i am getting is: Executing the query "flow.JobInitialize" failed with the following error: "The type of the value being assigned to variable "User::JobId" 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. It comes when calling a Stored Procedure with output parameters from the SSIS package (Execute SQL Task). I am using an ADO.NET Connection with IsStoredProcedure = true. The variable JobId is an Int32 in SSIS, and an int in SQL Server. However this also happens in another package where the SSIS datatype is Int64 and bigint on SQL Server. I changed the SSIS datatype to String here, to see if it would make any difference. It did, the problems seems rarer now, but not gone. I am running SQL Server 2008 R2, and this problem does not occur on one of our other servers, which is running SQL Server 2008. I have tried all remedies i know of, like ensuring that the values are always set inside the SP, and not just ignored. The value is also logged on the server each time, and it seems just fine, it is never null. I also cast the value now every time, little does it help. ex. select @JobId = cast( -1 as int ) Am i missing something or have i found a bug?

Did you inspect the values at the time the package failed? For example, you expect an Int, but the Stored Procedure returns 01, in this case your package will fail with the error above.Arthur My Blog

Yes i log i values in a table. They seem perfectly fine. Here is an example from my log: The type of the value being assigned to variable "User::JobId" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. {5DE31EA1-75EA-41D2-B6F0-219EE20583F5} Created batch for Job: 1 Executing the query "flow.JobInitialize" failed with the following error: "The type of the value being assigned to variable "User::JobId" 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. {C81DFC5A-3B22-4DA3-BD3B-10BF861A7F9C}

A have additional information. While trying to recreate the problem at will (it still comes, but i cannot trigger it myself), i have added Execute SQL Task's later in the flow. The first of these tasks calls a stored procedure with input arguments, this works fine. It uses an ADO.NET Connection. The second calls a stored procedure with an output parameter, this also works fine. It uses an OLE DB Connection. The next calls a stored procedure with an output parameter (the same as with the OLE DB Connection) and it uses an ADO.NET Conneciton. this fails! Also, might be important. The original error comes from a subpackage, meaning it is a package started from another package. And these extra debug tasks i put in, is in the outer package, after the subpackage fails.

I'm having the same problem and I can't figure out what it is. In my stored procedure it is returning an INT which should map to an Int32 variable according to all the documentation I've found. It can't be a NULL Issue because I'm using ISNULL(Variable, -1) to make sure I don't get a NULL value back.