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.
Step 2: Use Excel Source as data source in the Data Flow step.
Step 3: Set up the OLE DB Destination