SSIS OLEDB Source output parameter datatype

Question

I'm creating an SSIS package targeting SQL Server 2016. I'm trying to call a stored procedure in an OLE DB Source inside a Data Flow Task that has an output parameter I'm looking to set into a variable. I have the variable defined as a Int64 in SSIS and defined as a BIGINT in the procedure, but SSIS keeps returning the error:

Error: The type of the value (Decimal) being assigned to variable "User::SomeOutput" differs from the current variable type (Int64). Variables may not change type during execution. Variable types are strict, except for variables of type Object.

I have no idea why it's seeing a decimal. If I change my local SSIS variable to a decimal it doesn't error, but I want it to be a BIGINT/Int64

To recreate the issue you can create this stored procedure:

CREATE OR ALTER PROCEDURE [dbo].[Pr_GetTestProcedure] @SomeOutput BIGINT = NULL OUTPUT
AS
SET @SomeOutput = 8
SELECT 'Yes' AS Something

GO

Then create a local user variable:

Then create a data flow with an OLE DB Source calling the stored procedure:

Then set the parameters to the user variable defined as Int64:

Running the package will return the error I have listed above.

tinazmu · Accepted Answer · 2022-06-06 23:04:52Z

0

It appears to be a long running issue with SSIS supporting SQL Server's bigint datatype; apparently at the time of design/dev of SSIS, 64 bit integers were not yet standard across products. You can read the bigint value into a DECIMAL SSIS variable (or into an OBJECT whose type will become decimal).

There are many references to this problem, example: https://techcommunity.microsoft.com/t5/sql-server-integration-services/why-can-8217-t-i-store-my-bigint-result-in-an-int64-variable/ba-p/387335

answered Jun 6, 2022 at 23:04

tinazmu

5,5442 gold badges12 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SSIS OLEDB Source output parameter datatype

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related