Jorg Klein's Microsoft Business Intelligence Blog [Macaw] - All Comments

Blog moved! – Jorg Klein's Microsoft Business Intelligence Blog … | Management Business Wisdom

Mon, 14 Dec 2009 03:11:06 GMT

Pingback from Blog moved! – Jorg Klein's Microsoft Business Intelligence Blog … | Management Business Wisdom

SSIS - Decrease your fact table loading time up to 40%

Jorg Klein — Fri, 11 Dec 2009 22:48:24 GMT

Replace the multiple "Lookup Error Output" Derived Columns and Union All's with a single Derived

MCITP - I passed the 70-446 “PRO: Designing a Business Intelligence Infrastructure by Using Microsoft SQL Server 2005” exam!

Jorg Klein — Fri, 11 Dec 2009 22:42:29 GMT

Today I passed the Microsoft IT Professional(MCITP) 70-446 “ PRO: Designing a Business Intelligence Infrastructure

MCITP - I passed the 70-446 “PRO: Designing a Business Intelligence Infrastructure by Using Microsoft SQL Server 2005” exam!

Jorg Klein — Fri, 11 Dec 2009 22:42:23 GMT

Today I passed the Microsoft IT Professional(MCITP) 70-446 “ PRO: Designing a Business Intelligence Infrastructure

MCTS - I passed the 70-445 exam!

Jorg Klein — Fri, 11 Dec 2009 22:04:39 GMT

Last month I passed the MCTS 70-445 BI exam. This makes me one of the 338(October, 2007) Microsoft Certified

re: SSIS - Let the Excel connection manager pick the right column data types from an Excel source

Robbertvisscher — Mon, 30 Nov 2009 15:48:51 GMT

The reg key also allows the value 0. When this value is set, the excel connection manager scans every row to determine the data type for a column in your SSIS source component.

re: SSIS - Let the Excel connection manager pick the right column data types from an Excel source

jorg — Thu, 19 Nov 2009 20:28:23 GMT

Hi Todd,

Thanks for your comment! Very useful.

I did not know 16 was the max so I will add that to the blog post, thanks for the addition!

If I read the note on Microsoft Support about the IMEX=1 option it seems the TypeGuessRows property is still important. IMEX=1 only seems to affect the way Excel determines the data type, or am I missing something here?

This is the note from support.microsoft.com/.../194124 (got there by a link on Dougbert's blog):

"NOTE: Setting IMEX=1 tells the driver to use Import mode. In this state, the registry setting ImportMixedTypes=Text will be noticed. This forces mixed data to be converted to text. For this to work reliably, you may also have to modify the registry setting, TypeGuessRows=8. The ISAM driver by default looks at the first eight rows and from that sampling determines the data type. If this eight row sampling is all numeric, then setting IMEX=1 will not convert the default data type to Text; it will remain numeric. "

re: SSIS - Let the Excel connection manager pick the right column data types from an Excel source

toddmcdermid — Thu, 19 Nov 2009 16:36:21 GMT

Unfortunately, that reg key only allows values from 1 to 16 - yes, you can only increase the number of rows Excel will "sample" to 16.

I much prefer leaving Excel's guesswork out of it, and using IMEX=1. Full details of that on Dougbert's blog: dougbert.com/.../default.aspx

Twitter Trackbacks for SSIS - Let the Excel connection manager pick the right column data types from an Excel source - Jorg Klein's [sqlblogcasts.com] on Topsy.com

Thu, 19 Nov 2009 04:34:19 GMT

Pingback from Twitter Trackbacks for SSIS - Let the Excel connection manager pick the right column data types from an Excel source - Jorg Klein's [sqlblogcasts.com] on Topsy.com

http://consultingblogs.emc.com/jamiethomson/archive/2008/02/12/ssis_3a00_-case_2d00_sensitivity-in-lookup-component.aspx

TrackBack — Wed, 18 Nov 2009 21:29:02 GMT

http://sqlservercentral.com/blogs/dknight/archive/2009/06/24/better-know-a-ssis-transform-the-sort-transform.aspx

TrackBack — Wed, 18 Nov 2009 21:28:46 GMT

http://sqlservercentral.com/blogs/dknight/archive/2009/06/24/better-know-a-ssis-transform-the-sort-transform.aspx

TrackBack — Wed, 18 Nov 2009 21:28:43 GMT

http://sqlblogcasts.com/blogs/simons/archive/2008/10/26/lookup-performance-and-buffer-usage.aspx

TrackBack — Wed, 18 Nov 2009 21:27:56 GMT

http://technet.microsoft.com/en-us/sqlserver/cc510302.aspx

TrackBack — Wed, 18 Nov 2009 21:27:18 GMT

http://msdn.microsoft.com/en-us/sqlserver/cc511477.aspx

TrackBack — Wed, 18 Nov 2009 21:26:44 GMT