Shulou(Shulou.com)05/31 Report--

How to import Excel files in bulk using SSIS? I believe that many inexperienced people are at a loss about this, so this article summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

Import excel files with the same structure under a directory (including subdirectories) into sql2005 in batches, and you can use ssis to customize the task. The following complete description of the whole process with a large number of pictures. 1, the establishment of a test excel file, assuming that there are four a b c d fields, save in the f:/excel directory and copy many of the same files 2, open Microsoft Visual Studio 2005 or SQL Server Business Intelligence Development Studio installed with sql2005, create a new business intelligence project. 3. Drag a Foreach loop container 4, edit the container, set the traversal directory and other parameters 5, create a new mapping variable to store the file path in the traversal, 6. You don't have to care about how to store it. You only need to specify a variable name to ok the container. 7. After confirmation, the container is generated, then drag a data flow task to the container 8, switch to the data flow tab page, drag an excel source 9, edit the Excel source Select an arbitrary excel10 just now, select a sheet11, drag an oledb target to the data flow 12, press and hold the green arrow of the excel source, drag to the oledb target 13, edit the oledb target, select a sqlserver data table, this table must already exist, here we create a ssistest database Generate a table ttcreate table tt (a varchar (100), b varchar (100), c varchar (100), d varchar (100)) with the same structure as excel, and then use oledb to join 14 and edit the field mapping relationship. With the same structure, it will automatically find 15 and edit the following excel link manager Here we will use the variable of foreach to replace the excel file 16 that we just selected, the mapping usage of setting variable in the properties of connection manager 17, in the property editing list of expressions, select excelfilepath on the left, this is the property of connection manager, we will use variable instead of 18, and then click the property edit button of the expression, drag the variable user in the list:: xlspath variable to the following expression box 19, then ssis will report an error And give warning 20, the yellow area in the image above, right-property, open the properties window of the control flow, and set DelayValidation to True. After the above 20 steps of configuration, the whole process is over, you can press F5 to see the effect, all the excel under the folder will be imported into the database in batch. In addition, you can use sqlserver's job to call ssis's package to automatically import the excel in the directory into the database on a regular basis.

After reading the above, have you mastered the method of how to import Excel files in batch using SSIS? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.