Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Problems with excel 2007 import using SSIS 2005 Expand / Collapse
Author
Message
Posted Monday, March 7, 2011 7:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 8, 2011 7:33 AM
Points: 2, Visits: 11
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.
Post #1074173
Posted Tuesday, March 8, 2011 3:19 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 11:26 AM
Points: 13,622, Visits: 10,514
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




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1074668
Posted Tuesday, March 8, 2011 4:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 8, 2011 7:33 AM
Points: 2, Visits: 11
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.
Post #1074694
Posted Tuesday, March 8, 2011 4:59 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 11:26 AM
Points: 13,622, Visits: 10,514
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1074695
Posted Friday, June 7, 2013 7:35 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, August 29, 2014 8:37 AM
Points: 315, Visits: 89
[quote]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


[quote]

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)



Post #1461075
Posted Tuesday, June 11, 2013 3:26 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 11:26 AM
Points: 13,622, Visits: 10,514
Tab Alleman (6/7/2013)
[quote]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


[quote]

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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1461951
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse