Problems with excel 2007 import using SSIS 2005

  • Hi,

    I am trying to import data from excel 2007 into my sql server 2005 database using SSIS 2005. SSIS 2005 cannot read .xlsx files using excel data source. So I have used OLE DB data source alongwith Microsoft Office 12.0 Access Database Engine OLE DB provider.

    This works out fine except for text fields which are getting truncated when they have more than 255 characters. These columns are comments to be included by the business and as such have the potential of exceeding the 255 character barrier. [Note that the table where the text column is targeted has ntext as datatype]. On doing some web searches I have come across suggestions to increase TypeGuessRows doing registry edit.

    Ref: http://support.microsoft.com/kb/281517

    But found that that only works when the source in SSIS dataflow is an excel datasource.

    Any ideas?

    Regards,

    Palash.

  • Setting TypeGuessRows will help if the JET provider is used, it doesn't really matter if it is the Excel source or a script task using OLE DB.

    There is an equivalent setting for the ACE provider, you can find more details in the following thread:

    http://social.msdn.microsoft.com/Forums/en/adodotnetdataproviders/thread/40f62ac7-7a14-44a7-9e38-115fde4f0e66

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

  • Hi Koen,

    Was missing just that! Thanks a ton!

    Went to registry and updated TypeGuessRows to 0; only this time @HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel.

    And the dataflow worked just fine. However, there could be a potential downside to this solution if the excel is too big.

    Regards,

    Palash.

  • Palash Mitra (3/8/2011)


    Hi Koen,

    Was missing just that! Thanks a ton!

    Went to registry and updated TypeGuessRows to 0; only this time @HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel.

    And the dataflow worked just fine. However, there could be a potential downside to this solution if the excel is too big.

    Regards,

    Palash.

    Great! Glad that it worked out.

    The TypeGuessRows setting can indeed affect performance very badly.

    But if your Excel file is that large, I would suggest to convert it to .csv and import it as a flat file. Much more efficient.

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

  • Palash Mitra (3/7/2011)


    Hi,

    On doing some web searches I have come across suggestions to increase TypeGuessRows doing registry edit.

    Ref: http://support.microsoft.com/kb/281517

    That KB article says it only applies to SQL 2000 and 7.0. Can anyone confirm that this registry key is still used with 2005+?

    (I am a developer who doesn't have machine-level access to our SQL Server, or I'd look myself)

  • Tab Alleman (6/7/2013)


    Palash Mitra (3/7/2011)


    Hi,

    On doing some web searches I have come across suggestions to increase TypeGuessRows doing registry edit.

    Ref: http://support.microsoft.com/kb/281517

    That KB article says it only applies to SQL 2000 and 7.0. Can anyone confirm that this registry key is still used with 2005+?

    (I am a developer who doesn't have machine-level access to our SQL Server, or I'd look myself)

    It works for every version of the JET OLE DB provider (right until Excel 2003) and for every version of the ACE OLE DB provider (until Excel 2013 for the moment).

    The version of SQL Server doesn't matter, it is the OLE DB provider that is giving issues.

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

Viewing 6 posts - 1 through 5 (of 5 total)

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