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.
Create a custom SSIS source component that encapsulates the 32-bit driver and compile
it in 64-bit mode.
B.
Install a 64-bit ACE driver and execute the package by using the 64-bit run-time option.
C.
Execute the package by using the 32-bit run-time option.
D.
Replace the SSIS Excel source with a SSIS Flat File source.
Explanation:
* See step 3 below.
To publish an Excel worksheet to Azure SQL Database, your package will contain a Data
Flow Task, Excel Source task, and ADO NET Destination.
1) Create an SSIS project.
2) Drop a Data Flow Task onto the Control Flow design surface, and double click the Data
Flow Task.
3) Drop an Excel Source onto the Data Flow design surface.
Note When using the Excel Source task on a 64-bit machine, set Run64BitRuntime to False.
*
Incorrect:
Not D: The Flat File source reads data from a text file. The text file can be in delimited, fixed
width, or mixed format.
The answer should be “B”
In the question it says “The package fails when executed on the production server.”
It fails because ACE driver is missing.
Install a 64-bit ACE driver and replace the Excel source with an OLE DB source.
I believe answer C is correct based on following article. Excel source does not work with a 64 bit source. So you have to set it to false in project properties.
http://www.codeproject.com/Articles/534651/How-to-solve-SSIS-error-code-0xC020801C-0xC004700C
*64 bit runtime.