You are implementing a SQL Server Integration Services (SSIS) package that imports
Microsoft Excel workbook data into a Windows Azure SQL Database database. The
package has been deployed to a production server that runs Windows Server 2008 R2 and
SQL Server 2012.
The package fails when executed on the production server.
You need to ensure that the package can load the Excel workbook data without errors. You
need to use the least amount of administrative effort to achieve this goal.
What should you do?
A.
Install a 64-bit ACE driver and execute the package by using the 64-bit run-time option.
B.
Enable Address Windowing Extensions (AWE) for the local SQL Server instance.
C.
Replace the SSIS Excel source with a SSIS Flat File source.
D.
Install a 64-bit ACE driver and replace the Excel source with an OLE DB source.
Explanation:
XLSX files, the new file type in Excel 2007/2010 can’t be open with Excel Source/Destination
in SSIS. To use XLSX in SSIS we need to install ACE driver and use OLE DB
Source/Destination to read read/write data in XLSX files.
ACE is a set of components that facilitate the transfer of data between existing Microsoft
Office files such as Microsoft Office Access (*.mdb and *.accdb) files and Microsoft Office
Excel 7 (*.xls, *.xlsx, and *.xlsb) files to Microsoft SQL Server. If the SQL Server or your
development box does not have ACE driver then install that 1st.
Note:
* The most efficient way to link to SQL Server is using an OLEDB connection manager.
Incorrect:
Not B:
* Address Windowing Extensions (AWE) is a Microsoft Windows application programming
interface that allows a 32-bit software application to access more physical memory than it
has virtual address space.* Address Windowing Extensions (AWE) is a set of extensions that allows an application to
quickly manipulate physical memory greater than 4GB. Certain data-intensive applications,
such as database management systems and scientific and engineering software, need
access to very large caches of data. In the case of very large data sets, restricting the cache
to fit within an application’s 2GB of user address space is a severe restriction.