You can use SSIS to customize a task by importing the sql2005 of Excel files in one directory (which can include subdirectories). The following is a complete description of the entire process with a large number of pictures.
1, the Establishment test Excel file, assumes has a B C D four fields, saves in the F:/excel directory
and copy many of the same files.
2. Open a new business intelligence project by opening Microsoft Visual Studio 2005 or SQL Server Business Intelligence Development Studio installed with sql2005.
3. Toolbox Drag a Foreach Loop container
4, edit the container, set the Traverse directory and other parameters
5. Create a new mapping variable to store the path of the file in the traversal
6, how to store without you care, you just specify a variable name on the OK
7, after the determination, the container generated, and then drag a data Flow task into the container
8, switch to the Data Flow tab, drag an Excel source
9, edit Excel source, select a just any Excel
10. Choose a sheet
11. Drag an OLE DB target into the data stream
12, hold down the Excel source Green arrow, drag to the OLE DB target
13, edit OLE DB target, select a SQL Server data table, this table must be already existing, here we build a ssistest database, generate a table and Excel structure of TT
CREATE TABLE TT (a varchar (m), B varchar (m), C varchar (m), D varchar (100))
And then connect it with OLE DB.
14, edit the field mapping relationship, the same structure, it will automatically find
15, edit the following Excel chain to the manager, here will use the variable of foreach to replace the just selected Excel file
16. Set the mapping usage of variables in the properties of the Connection Manager
17, Expressions's property edit list, the left choice Excelfilepath, this is the Connection Manager's attribute, we will use the variable to replace
18, click the expression of the attribute Edit button, the list of variable users:: Xlspath variable drag into the following expression box
19, then SSIS will be an error, and give warning
20, above the yellow color area, right key-properties, open the Control Flow Properties window, set DelayValidation to true on it.
After the above 20-step configuration, the entire process is over, you can press F5 to see the effect of the folder, all the Excel will be imported into the database in batches. In addition, you can invoke the SSIS package implementation via SQL Server jobs to automatically import Excel from the directory to the database at regular intervals.
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.
