The History of Business Intelligence

Posted on by Gogula G. Aryalingam

Given here is a video of a presentation done by Nic Smith, BI Solutions Manager at Microsoft. He talks about how Business Intelligence came into being, along with its failures. I think it’s a really cool presentation which I believe should be viewed by anyone in BI.

Apart from the excellent and simple narrative, it also gives a little insight on the history of pop culture mixed with subtle humor, which really kept me smiling throughout the presentation, especially the “iced out” part. Man that was funniest! The thing is; it’s a very simple presentation, but you could see the effort that had gone into it, a manifestation of the quote by Spanish playwright Poncela: “When something can be read without effort, great effort has gone into its writing”. Hats off to Nic. Enjoy…

Of Excel, Errors, SSIS and x64

Posted on by Gogula G. Aryalingam

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.