Post reply

Problem with data type when importing an Excel spreadsheet

  • January 2, 2017 at 6:19 pm

    #339091

    3 global variables

    1. objMen, data type is object

    2 varID, data type is int32

    3 varName, data type is string

    Execute SQL Task setup as follows:

    1 result set - full result set

    2 connection type = Excel

    3 connection = conMgr

    4 sqlsource type = direct input

    5 sqlstatement = Select MentorNumber, MentorName from [Sheet1$]

    Under the result set tab

    set the Result Name to 0 and variable name to objMen

    SQL Task is connected to a foreach container- this setup is as follows:

    1 Under collection set the Enumerator to foreach ado enumerator

    2 ado object source variable to objMen

    3 Under the Variable Mapping-varID to index 0 and varName to index 2

    Script Task inside the foreach container- setup as follows:

    1 ReadOnlyVariables - varID and varName

    2 hit the edit script button - code is as follows:

    int mentorID = Convert.ToInt32(Dts.Variables["VarID"].Value);

    string mentorName = Convert.ToString(Dts.Variables["VarName"].Value);

    MessageBox.Show("ID is " + mentorID + "" + " Name is " + mentorName);

    When I run the package, MessageBox reads the first record but the ID is the varID default (0) and

    then it does read the correct Name. So it is not reading the varID, hit ok button, it errors out, the

    message is:

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

    However, when I change the VarID to string, code in the script task from int to string, and change the Excel

    column ID to characters, ie "Seven" instead of "7", it works great.

    I do understand it is a data type issue. It seems like when I import it into my objMen from Excel it is not

    recognizing it as an int. I have no idea how to correct this. Maybe I am missing a property setting somewhere.

    Any help will be very much appreciated.

    Thanks,

    rtp

  • January 2, 2017 at 8:56 pm

    #1920302

    Were you using Number Datatype for MentorNumber column in excel sheet? If no, was it of datatype General?

    There are couple of ways to handle this issue. There are some things we need to make in note while reading excel columns in SSIS.

    1. First way is, change all excel columns' data type to Text. (No need to change data like 7 to Seven. 7 will be imported). Then, map each column to their respective variables of type String. Convert them to desired datatype in Script while using them.

    2. Other way is, change excel's MentorNumber column's datatype to Number. Map it with package variable of type Int32. Then, using it in script task (There is no need to again convert it to Int32 in script task)

    Give it a try. I mean try to work on column datatype of excel sheet only. It should work.

    Regards
    VG

  • January 2, 2017 at 9:17 pm

    This was removed by the editor as SPAM

  • January 3, 2017 at 10:20 am

    #1920433

    Vivek,

    The string of the number works, but could not get the int32 to work. There must be

    something with the Number format within Excel. I would like to know why but

    string will work just have to convert it to int32.

    Thanks for your help.

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

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