Cara menggunakan ssis excel connection manager

I'm trying to create a data flow task which brings data from Excel File to SQL table. I'm doing it in SQL Server Data Tools for Visual Studio 2012 as an SSIS package. However, when I run the task I receive below error:

[Excel Source [52]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0209303. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

Complete Error message:

SSIS package "C:\Users\adm.turpan\Documents\Visual Studio 2012\Projects\Integration Services Project1\Integration Services Project1\project.dtsx" starting. Information: 0x4004300A at Excel as source, SSIS.Pipeline: Validation phase is beginning. Error: 0xC020801C at Excel as source, Excel Source [52]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0209303. There may be error messages posted before this with more information on why the AcquireConnection method call failed. Error: 0xC0047017 at Excel as source, SSIS.Pipeline: Excel Source failed validation and returned error code 0xC020801C. Error: 0xC004700C at Excel as source, SSIS.Pipeline: One or more component failed validation. Error: 0xC0024107 at Excel as source: There were errors during task validation. SSIS package "C:\Users\adm.turpan\Documents\Visual Studio 2012\Projects\Integration Services Project1\Integration Services Project1\project.dtsx" finished: Success.

Comprehensive suite of 200+ components for developing rock solid ETL solutions with Microsoft SQL Server Integration Services.

Learn more

SSIS NoW

Event-based processing using the same SSIS technology we all love.

Learn more

Excel Add-in for SAS

Process SAS system data set files from within Microsoft Excel.

Learn more

Partners

• Find a Partner
• Become a Partner
• Partner Sales Registration

Download

Purchase

• PayPal
• Purchase Orders

Support

• Help Center
• Installation and Getting Started
• Licensing
• Tutorial Videos
• Forum
• Release Notes

Blog

About Us

• About Us
• News
• Testimonials
• Case Studies
• Contact Us

Excel Connection

Overview

Excel Connection Manager is SSIS Connection Manager for processing data in Microsoft Excel workbook. Use the API to build components based on Excel Connection Manager.

Parameters

Excel

Use the Excel page of the Excel Connection Manager dialog to specify properties for opening Excel workbook.

Test ConnectionConfirm connection manager configuration by clicking Test Connection.

• File

  Specify Excel workbook file to open.

• RetainSameConnection

  Specify if all clients should use the same connection. This parameter can be accessed from the Properties editor and has the options listed in the following table.

  ValueDescriptionTrueAll clients use same connection (default).FalseEvery client use different connection.

---

Advanced

Use the Advanced page of the Excel Connection Manager dialog to specify additional properties if the default configuration is not working.

• Format

  Specify Excel workbook format. This property has the options listed in the following table.

  ValueDescriptionAutomaticAutomatically determines Excel workbook format from specified file extension (default).Excel 97-200397-2003 Excel workbook.Excel 20072007-2010 Excel workbook. Requires installation of .NET 3.0 or later.Pocket ExcelPocket Excel.

• Open Password

  Specify password to open Excel workbook.

• Modify Password

  Specify password to modify Excel workbook.

• Recalculate

  Specify to recalculate and update external references in Excel workbook on open. This property has the options listed in the following table.

  ValueDescriptionTrueExcel workbook is recalculated on open.FalseExcel workbook is not recalculated on open (default).

• Date format

  1.9

  Optional. Specify custom string format for writing date/time values. The format used by the standard Excel connection is : yyyy-MM-dd HH:mm:ss.fffffff