Home > SSIS Best Practices > SSIS – Load Data From An Excel File

SSIS – Load Data From An Excel File

We all would prefer that all our data are nicely stored in our database management system. But the reality is that it is not always. Here are some basic steps for loading data into SQL tables from Excel files.

Step 1: Create an Excel Connection Manager in SSIS. The Excel file path needs to be in UNC (Universal Naming Convention) format, i.e. \\ComputerName\SharedFolder\Resource. The Excel file is not required to have real data, but needs to be created with appropriate column names during development time. I also use the default name Sheet1 for the to-be-loaded data.

image

Step 2: Use Excel Source as data source in the Data Flow step.

image

Step 3: Set up the OLE DB Destination

image

Categories: SSIS Best Practices
  1. Yelong Cui
    April 15, 2010 at 2:54 pm

    following your way, get error DTS_E_OLEDB_EXCEL_NOT_SUPPORTED. OLEDB 4.0 not support in the 64-bit version of SSIS

    My excel file is Microsoft excel 97-2003 but the Sql sever is Windows server 2008 X 64 (64-bit operating system)

    How to work around? Thanks!

    Like

  2. Sherry Li 李雨
    April 15, 2010 at 5:44 pm
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: