Post reply

Bug in foreach loop with ado.net enumerator

  • July 27, 2014 at 9:34 am

    #288213

    Hi Greetings to everyone,

    Today I am going to report a bug in SSIS 2012. In one of the packages I was using execute sql task and then using ado.net foreach loop enumerator. My execute sql task had a simple select statement like - 'select c1,c2,c3 from table' where c1,c2,c3 were integer columns. Based on the rows returned I was running foreach loop container and assigning values from the columns to the variables of datatype Int32 inside SSIS package. However on executing the package it was returning an error message such as

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

    or it can be

    The type of the value (Int32) being assigned to variable "User::result1" differs from the current variable type (Decimal)

    While researching I found that this is a bug in ssis - http://connect.microsoft.com/SQLServer/feedback/details/732413/foreach-ado-enumerator-returns-error-when-bigint-and-int64-are-used

    Solution - So I changed the data type in my ssis package to Decimal and it worked (you need to change the data type of the SSIS variable to map it with the one being returned from sql)

    Hope this helps

    Thanks

    Lokesh

    Lokesh Sharma
    sql4all.org

  • July 28, 2014 at 12:37 am

    #1732700

    The error message in your package clearly says you are trying to put Int32 into Int64. Is this the case?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • July 28, 2014 at 1:01 am

    #1732706

    No this is actually the error message that I tried to simulate on my personal computer. However in my office I had bigint being returned from sql and int64 in ssis. So I had to change it to decimal in ssis.

    Actually it has to do with how ssis treats values being returned from sql. I hope it makes sense

    Lokesh Sharma
    sql4all.org

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply