Post reply

OLE DB Source is converting a string into an int

  • July 20, 2007 at 5:28 pm

    #120870

    Hi All,

    I'm creating a package to synchronize my development database to production (cross server), and noticed a mapping problem with one table. Both production and development table schema are identical, but the OLE DB Source task converted a varchar(11) column into DT_I4 (SSIS integer type) and it didn't want to insert to the destination DT_STR.

    I edited the advanced column settings to make the source output as DT_STR, but I'm wondering if anyone has had their data damaged because it got converted from string to int to string or something similar? I imagine that this would do a nice Excel style truncation of leading zeros.

    Thanks for sharing!

  • July 20, 2007 at 5:44 pm

    #721305

    I've run into a number of issues like this, most frequently related to Excel. The way I understand the issue is that there's a guessing process which looks at the first couple of rows of data and based on that makes a guess at the ODBC data type it's going to represent that column as.

    If I recall there's a setting somewhere that you can put in your connection string which will change the behavior of how it guesses data types.

  • July 23, 2007 at 5:36 am

    #721492

    The original poster does not mention the source. I am guessing it is SQL. If so, it's weird SSIS got this wrong as it uses the mapping document in the SQL installation dir. I cannot say I have seen it make this mistake.

    For "unknown" data sources suck as EXCEL, flat files, sometime Oracle etc, it does make the occasional mistake. It scans the first n rows and guesses what the time might be. Excel is a prime candidate for getting it wrong.

    As for loosing or damaging your data due to this problem, should you not check the meta data anyway for each column? I would blame data loss on poor development and testing. Not blaming the source wizard.

    Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

  • July 23, 2007 at 9:22 am

    #721549

    Both source and destination are SQL Server 2005.

    I don't think developers should be made to check the meta data on hundreds or thousands of columns for every data transfer. That means us customers have to do the QA work, ad infinitum, that Microsoft should have done once.

    Thanks Bill!

    I usually have cause to thank Bill G. at least once a day.

    Cheers,

    David

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

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