What on earth is an Array Formula?

This has nothing to do with SQL Server, save a teeny weeny part. Me gets this requirement from above: Do this report thingy with Excel using macros. It shall be a temporary solution. And it shall be parallel to the permanent reporting solution that you are already developing, but with a higher priority (That’s like building a super saloon car while also building a rickety lorry with a high priority to do somewhat the same thing as the car, ‘cept that the rickety lorry should be shipped first. – That’s how one colleague put it). Ye shall extract the data from Good ole SQL Server to the Excel workbook, and Mr. Senior Whiner shall do the macros in such a way that it’ll create some nice looking reports on more Excel sheets. Anyways, with a lot of whining and I-dunnos and I-cannots the macro ball was passed by Mr. Senior Whiner to scape-goat builder. R n’ Ds by meself and scape-goat builder led to the discovery of array formulae.

I have been working with Excel for a long time, not on an extensive scale though, but quite a power user. Therefore, array formulae just came out as a big surprise. I never knew something like that ever existed.

So what’s an Array Formula, you ask?

It’s a formula which can be used in place multiple ‘normal’ formulae. And it works on arrays of values. And in order to enter the formula, you need to first type it in and then press Ctrl + Shift + Enter on the keyboard. Otherwise you’ll end up with the #VALUE! error. Once you enter the formula successfully, you will notice that the whole formula is contained within a pair of curly braces which disappear when you click on the formula bar.

In the screenshot you could see that in order to get the total value for each region I use an array formula as follows:

=SUM((C2:C17)*(D2:D17)*(A2:A17=”East”))

This is just a simple example. There’s a whole dimension of complex formulae that can be written in a simpler and powerful form using array formulae. Just google ‘Excel Array Formula’, or perhaps you could even bing it (if you dare)

 

Of Excel, Errors, SSIS and x64

Here’s something to always keep in mind when Working with Office Excel (or for that matter Office Access) in Integration Services on a 64-bit operating system: You are bound to get errors.

This is because Office Excel and Access files are connected to, from SSIS using Jet drivers, and Jet drivers come only in 32-bit form. A lot of us have taken to using 64-bit OSes such as Vista Ultimate 64-bit or Windows Server 2008 64-bit, what with the prices of memory being so affordable and all that. But running a perfect Integration Services Package (which you had tested and run several times) copied from a 32-bit machine can cause that all-too-familiar sinking feeling in your stomach. Or even building an Integration Services package from scratch like you have always done (in a 32-bit environment) may give you an AcquireConnection call failure (i.e. your package is unable to connect to your Excel file although everything seems alright).

The solution to this is:

• If you are running the package in the debug mode (i.e. from BIDS), set the Run64BitRuntime property of the solution to False. This property is set to true by default in x64 environments, hence giving you the error (which is believe me, very frustrating for a first-timer on x64).

• If you are running the package directly (from the file system) through the DTExec or DTExecUI utilities you will have to make sure that you run the package using the 32-bit version of the respective utility: The 32-bit version by default can be found in the <drive>:\ Program Files(x86)\Microsoft SQL Server\100\DTS\Binn folder, whereas the 64-bit version can be found be default in the <drive>:\Program Files\Microsoft SQL Server\100\DTS\Binn folder. This, of course is for SQL Server 2008, whereas for SQL Server 2005 you would have to substitute 100 with 90.

So, do make a note to check your 64bit “settings”, the next time you are working with Integration Services and Excel on a 64-bit environment. You may cross paths with errors such as
The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009
or
The For Each File enumerator is empty. The For Each File enumerator did not find any files that matched the file pattern, or the specified directory was empty.