Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Problems with excel 2007 import using SSIS 2005


Problems with excel 2007 import using SSIS 2005

Author
Message
Palash Mitra
Palash Mitra
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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.
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16469 Visits: 13207
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 SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Palash Mitra
Palash Mitra
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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.
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16469 Visits: 13207
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 SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Tab Alleman
Tab Alleman
Old Hand
Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)

Group: General Forum Members
Points: 315 Visits: 115
[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)



Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16469 Visits: 13207
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 SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search