none
The type of the value (Int32) being assigned to variable error occurs when there is nothing wrong. RRS feed

  • Question

  • Hi,

    We are getting the following error from our SSIS packages via SQL Agent. It has only happened twice in the last month and never happened before this. Restarting the package solves the problem however it means an 8 hour load has to start again.

    Microsoft (R) SQL Server Execute Package Utility  Version 11.0.5058.0 for 32-bit  Copyright (C) Microsoft Corporation. All rights reserved.    Started:  2:01:18 AM  Error: 2017-12-15 02:01:23.76     Code: 0xC001F009     Source: Import_Stock_Category      Description: The type of the value (Int32) being assigned to variable "User::FileImportID" differs from the current variable type (Int32). Variables may not change type during execution. Variable types are strict, except for variables of type Object.  End Error  Error: 2017-12-15 02:01:23.88     Code: 0xC002F210    

    The obvious issue here is it's indicating that an int32 is not compatible with an int32.And the restart of it works fine.

    Can anyone got any idea on the cause of this.

    Regards.

    Simon.

    Thursday, December 14, 2017 11:27 PM

All replies

  • Is there any possibility that FileImportId is null/blank? 

    Microsoft error messages aren't always the most meaningful

    Thursday, December 14, 2017 11:47 PM
  • Is there any possibility that FileImportId is null/blank? 

    Microsoft error messages aren't always the most meaningful

    Highly unlikely. The field is used by about 40 different SSIS packages all in the same way. All other attempts at using this field work.

    The code that runs would have ran 10,000s of times and it's only in the last few weeks has it failed twice with this silly error.

    Regards.

    Friday, December 15, 2017 1:42 AM
  • What and how is it being set?
    Friday, December 15, 2017 2:00 AM
  • We run a stored procedure from SSIS which adds a record to a table with an identity column. This new identity column value is then passed back from the stored procedure to the package. Every single file import we do does the same thing and 99.999% of the time it has worked.

    Regards

    Friday, December 15, 2017 2:41 AM
  • Hi Simon Darlow tRS,

    What's the value of variable when the issue occurred? Is this issue occurred in single package? Do you set the value of variable at runtime?

    Regards,

    Pirlo Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, December 18, 2017 4:46 PM
    Moderator
  • How are you returning this value to SSIS variable? As an OUTPUT parameter from your T-SQL procedure or through the return statement?

    In Execute SQL Task where you return it, what have you set as resultset option?

    Also post the screenshot of your resultset tab where you map it to the FileImportID variable


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, December 18, 2017 4:51 PM
  • This happens when your stored proc returns a string to SSIS.  Most likely it is getting an error under some circumstance and returning "Error occurred.....".

    Please post your stored proc code.

    Monday, December 18, 2017 6:35 PM
  • If you can test the ETL, using breakpoint to see the value returned from SP. And then how is it used in Source: Import_Stock_Category component.

    Thanks, hsbal

    Monday, December 18, 2017 9:29 PM