SQL Server Bi step by step 3 Import batch Excel data sources to databases

Step 3 Import batch Excel data sources to databases

The core of this section is to set the Foreing variables of the foreach loop container to traverse all files in the Excel directory and import the data to the target data source.
Illustration: SSIS batch import of Excel files, configuration data plus data viewer

1. Batch export of EXCEL tables

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.

And so on
 

2. Create a new package pageage2.dtsx
 

 

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 the data. You only need to specify a variable name.

 

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 attribute editing list of expressions, select excelfilepath on the left. This is the property of the Connection Manager. We will replace it with 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. On the Control Flow page, right-click a Data Flow task and choose properties. In the Control Flow Properties window, set delayvalidation to true.
 

 

Run:
After the above 20 steps of configuration, the entire process is over. You can run the package to check 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.
 
If you are careful, you will see that the data stream has been executed four times in total.
 

Ii. Configure the data viewer

You can also add a data viewer to view the data in the execution process. This allows you to easily debug the SSIS package. right-click "green" in the middle of the Excel source and old dB target tables, click "data viewer", click "add", and select "network". All others are default, and click "OK.

 

When you run the package again, you will find that all the components are yellow packets, which means that the package is being executed. In the pop-up data viewer, you can see two rows of data from all the data streams, click the green button above to continue running. through the data viewer, we can view the data transmitted in the data stream.
The data viewer intercepts data before it flows into the data component. It is like a valve.
 

Project Step1 --- 4 source code file: the version is SQL 2005. You must install the Excel application before running the code.

/Files/cocole/Step1-4Sql05.rar

 

Author: Wukong's Sky (tianma xingkong)
Source: http://www.cnblogs.com/cocole/
The copyright of this article is shared by the author and the blog Park. You are welcome to repost this article. However, you must retain this statement without the author's consent and provide a clear link to the original article on the article page. Otherwise, you will be held legally liable.