More Geeking with Excel Sources in SSIS

This is what you do when you're bored.

I saw a post on www.social.msdn.com about Excel data sources and SQL Server Integration Services (SSIS) that was answered by two folks, and not particularly correct by either one I might add. Someone asked a two-part question - the first was… “How would you SELECT only the first six rows from an Excel spreadsheet?” This is easily done by simply changing over from a table in the Excel Data Source to a SQL command, and typing something like the following:

Figure 1. TOP query to Excel source in SSIS

No problems there.  The second question was, “How would you skip the 12th row in succession from a spreadsheet?”. That one is a bit tougher...all I could think of was that it was very possible if you had a column that is numbered, such as one created by IDENTITY or ROW_NUM(), maybe do something like this:

SELECT *

FROM [Sheet1$]

WHERE COL4 MOD 12 <> 0

Figure 2.  MOD in Excel source in SSIS

How about this handy SELECT statement?

TRANSFORM Count( [Sheet1$].F1) AS CountOfF1

SELECT [Sheet1$].F2

FROM [Sheet1$]

GROUP BY [Sheet1$].F2

PIVOT [Sheet1$].F3

Care to guess what this one is? Yep, it’s the syntax for a ‘Pivot Table’ in Microsoft Access, which also works in our Excel source to SSIS. And, there is a pattern developing here…wanna take another guess what's happening? You’re correct again! In SSIS, for Excel sources, we’re using the old, worn-out, sometimes iffy and oftentimes questionable 'Microsoft.Jet.OLEDB.4.0' provider that we’ve had for 100+ years or more.  Click on your Excel source connection manager, and then go check the properties window (F4):

Figure 3.  Excel Connection Manager - 'Microsoft.Jet.OLEDB.4.0'

Another person on the thread answering questions made the comment that the SQL language to Excel from SSIS isn’t "full-featured"; this is untrue. It’s just that it’s not the SQL language that you’re used to, i.e. TSQL.  Sure, there are lots of things that it can't do, but plenty that it can do. TRANSFORM, above, and MOD, are but a few keyword examples that the language can do that TSQL can, but with some other word.  These two happen to work perfectly from an Excel source right to a SQL Server table, flat file, etc. It’s got nothing to do whether or not the language is full-featured or mature, au contraire!

Lee

-----------------------------------

"The number of lines of comments in code is inversely proportional the expertise of the coder"   Lee Everest