The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered

If you run below SQL statement
---------------------------------------------------------
Select * into DBTable FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:\myExcelFile.xlsx;HDR=YES', 'SELECT * FROM [Sheet1$]')




INSERT INTO DBTable select * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:\myExcelFile.xlsx;HDR=YES', 'SELECT * FROM [Sheet1$]')
--------------------------------------------------------


and you get an error message like below
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered


Solution
you need to download 
"Data Connectivity Components for 2007 office system Driver".
after you download, please restart your server.


you can click link here to download from microsoft site.


you will able to saw your microsoft excel driver 12.0 in your Data Sources(ODBC) in control panel

DTS not support in SQL Server 2008 R2

Limited SQL Server 2000 DTS Functionality on 64-bit Operating Systems

SQL Server 2008 does not include support for DTS in the following circumstances:

• There is no 64-bit design-time or run-time support for DTS packages. On a 64-bit computer, DTS packages, and Integration Services packages that run DTS packages, can run only in 32-bit mode. For more information, see How to: Install Support for Data Transformation Services Packages.
• There is also no 32-bit design-time or run-time support for DTS packages on Itanium-based operating systems. Therefore, you cannot create, view, modify, or run DTS packages on Itanium-based operating systems.
  

Supported SQL Server 2000 DTS Functionality

SQL Server 2008 includes support for the following DTS features:

• The DTS runtime, the object model that it exposes, and the dtsrun.exe command prompt utility.
• The Execute DTS 2000 Package task, for executing DTS packages within Integration Services packages.
• The ActiveX Script task, for backward compatibility only.
• The DTS Package Migration Wizard, for migrating DTS packages to the Integration Services package format.
• The Upgrade Advisor rules for DTS packages, for identifying potential issues that may be encountered when migrating packages.

more details on http://msdn.microsoft.com/en-us/library/bb500440.aspx