SQL Server Bi step by step SSIS 3-Batch import of EXCEL tables

The last time we introduced simple data export and import, we only operate on a single file. If we want to import data to all the files under a directory at the same time, how can we achieve this? SSIS provides the foreach loop container in the control flow, which is easy to understand. Compared with the sequential container, SSIS can traverse and execute cyclically, you can repeatedly execute the control flow for each member of the specified enumerator:

As you can see, the foreach loop window can traverse folders through some settings or regular expressions to match. The foreach loop container can be enumerated:
• ADO records set row and Architecture Information
• File and directory structure
• Systems, packages, and user Variables
• SQL management object (SMO)

Productid <in notebook to the newly created product2.xls (note that the two Excel sheet names are the same), then create a new foreachinput package in our SSIS project, foreach loop Container comes in, double-click set, specify the path of the Excel file in the folder, for example, F: \. Write the file name *. in this way, all files with the suffix "xls" can be matched, and then the process in the container is executed separately. However, in our container, you need to access each excel file, we must obtain the name of each enumerated variable, that is, the file, which can be achieved by setting the variable ing.

For example, after a new variable is created, every time a file matching the condition is found, the foreach loop container will fill the User-Defined variable with this file name. similarly, when traversing other objects, we can also map variables to obtain a single matched object.
Then we directly put the package (outputandinput. in dtsx), the data stream import component is copied to the foreach loop container (just like copying a common file), and the Excel file connection and database connection in the Connection Manager are also copied. this is basically done, but the current Excel file connection still specifies the file, we just need to bind it to our variable currentfilepath. click the Excel file connection and click expressions in the properties on the right.

In the attribute expression Editor, we can directly bind all attribute values connected to an Excel file with an expression,It is not only an Excel file connection, but also a normal file connection, database connection, or FTP connection. This undoubtedly improves flexibility.. Here, we only need to bind the file path, select excelfilepath In the attribute, and then click the ellipsis next to the expression.

Not only can system variables and user variables be directly bound, but also can be combined into expressions using scripts. we only need to select the user variable currentfilepath. click the calculated expression, which is just an empty string because no traversal is performed at this time. therefore, the Excel source data preview component is displayed in red, indicating that the file does not exist. We do not need to detect the file at this time, therefore, set the delayvalidation attribute value to true for the Excel file connection delay verification.
To prevent conflicts with the existing data in the database, modify the derived column component, because the last time we added productnumber and name, we added 2.

Right-click the package and execute it. You will find that the execution is successful and the data flow is executed twice (which may not be visible soon). You can see it by comparing the database, the data has been added to two Excel tables. of course, you can divide it into more Excel files.

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 the green icon in the middle of the derived column component and the product target table, click data viewer, click Add, and select Network. All others are default. OK.

When we change the derived column to + "3", run the package again. You will find that all components are yellow packets, which means they are being executed, in the pop-up data viewer, 55 rows of data from all data streams are displayed. Click the green button above to continue running. through the data viewer, we can view the data transmitted in the data stream.

In the above data viewer, we can see whether the newly added newproductnumber and newname columns are correct.
Now we have completed batch import of Excel. This time we have learned about the foreach loop container, used with user variables, and some simple settings for connecting to the connector, for example, dynamic attribute binding expressions, and data viewer usage are also introduced.

ReferenceArticle: Http://blog.csdn.net/jinjazz/archive/2008/07/25/2710169.aspx
Http://www.oracle.com.cn/redirect.php? Tid = 85304 & goto = lastpost

Download this project file. (For VS 2005)

 

Author: lone knight (like a year of water)
Source: http://lonely7345.cnblogs.com
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.