SQL Server 2010 vs. SQL Server 2008 R2

Microsoft initially planned to call next version SQL Server 2010. As they were working on it, they focused only on some aspects of it and due to the fact that most components didn't have significant changes decided to call it R2.l

That was a fair decision. I see only one problem with it. It will have a significant impact on adoption rate of SQL Server 2008.

Unfortunately, most shops waited until 2008 was released to upgrade their 2000 servers to 2005. Their rational for not upgrading to 2008 was that Service Pack 1 was not yet available. If Microsoft named next release 2010 as initially planned, everybody would hurry to deploy 2008. Unfortunately, most non-technical decision makers will not even notice R2. They will simply continue running their shops on 2005.

Technorati Tags: SQL Server 2008 R2,SQL Server 2008,SQL Server 2010,SQL Server 2005,SQL Server adoption rate

Excel (and Access) Data Source in 64-bit environment

When you try to run a scheduled job (with SSIS package) that connects to Excel or Access file on 64-bit server you will get errors like following:

Excel Destination 1 1 [15]] Error: The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009.

[DTS.Pipeline] Error: component "Excel Destination 1" (15) failed validation and returned error code 0xC020801C.

[Connection manager "Excel Connection Manager "] Error: An OLE DB error has occurred. Error code: 0x80040154. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".

This is all due to the fact that SQL Agents tries to run SSIS package using a 64-bit version of dtexec.exe. The utility will try to use 64-bit version of JET/ISAM driver to access Excel/Access files and fail since they do not exist.

Solution is to run 32-bit version of dtexec.exe. You can do that by manually creating the command that references the right utility. First, copy the dtexec parameters of the job step that you created (see picture):

Then replace it with a new Opertaing System (CmdExec) job step and assemble the command line from reference to the 32-bit dtutil (typically C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe) and copied parameters.

SSIS and Vista (.NET Framework incompatibility)

If you have Vista as a development machine, you should upgrade to SP2 or upload fixes described in the following knowledgebase article. Apparently, there is a difference in version of .NET Framework which prevents Script task from working properly.

http://support.microsoft.com/default.aspx?scid=kb;en-us;931846

This problem may occur also if you have .NET Framework 2.0 hotfix later than build 2.0.50727.300 installed.