Illustration: SSIS batch import of Excel files

Import an Excel file with the same structure in a directory (including sub-Directories) to sql2005 in batches. You can use SSIS to customize the task. A large number of images are used below to describe the entire process.

1. Create a test Excel file. Assume that there are four fields a B c d stored in the F:/Excel directory.

And copy many identical files.

2. Open Microsoft Visual Studio 2005 or SQL server business intelligence studio installed with sql2005 to create a business intelligence project.

3. Drag a foreach loop container in the toolbox

4. Edit the container and set the directory traversal and other parameters.

5. Create a New ing variable to store the file path in traversal.

6. You don't need to worry about how to store it. You just need to specify a variable name and it will be OK.

7. After confirmation, the container is generated and a data flow task is dragged to the container.

8. Switch to the data flow tab and drag an Excel Source

9. Edit the Excel source and select an existing Excel file.

10. Select a sheet

11. Drag an ole db target to the data stream

12. Press the green arrow of the Excel source and drag it to the oledb target.

13. Edit the oledb target and select an sqlserver data table. The table must already exist. Here we create an ssistest database to generate a table TT with the same structure as Excel.
Create Table TT (A varchar (100), B varchar (100), c varchar (100), d varchar (100 ))
Then use oledb to connect

14. Edit the field ing relationship. If the structure is the same, it will automatically find

15. edit the following Excel link Manager. The foreach variable is used to replace the selected Excel file.

16. Set the ing usage of variables in Connection Manager Properties

17. In the expressions attribute editing list, select excelfilepath on the left. This is the Connection Manager attribute, which will be replaced by variables.

18. Click the attribute edit button of the expression to drag the variable User: xlspath in the list to the following expression box.

19. SSIS will report an error and give a warning.

20. Right-click the yellow area and choose properties. In the Control Flow Properties window, set delayvalidation to true.

After the above 20 steps of configuration, the entire process is over. You can press F5 to see the effect. All the Excel files in the folder will be imported into the database in batches. In addition, you can use SQL server jobs to call the SSIS package to automatically import the Excel files in the directory to the database on a regular basis.