SSIS [Foreach loop container _ Foreach file enumerator] (content of all txt files in the import path) (convert)
SQLServer 2008 R2
SSIS_Foreach loop container _ Foreach file enumerator (content of all txt files in the import path)
1. Drag a Foreach loop container to control flow, and then drag a Data Flow task to Foreach loop container.
2. Edit the Foreach loop container, select the Foreach file enumerator in the Set option, and configure the folder and file type to be traversed.
3. Add a traversal in the variable ing option. This traversal is used to store the complete path of the file that the container traverses each time.
4. Now, the Foreach loop container configuration is complete. Now, design the data stream ]. Add a flat file source and ole db destination to connect to the data stream.
5. Edit the flat file source and create a flat Connection Manager to find any file in the txt directory (because all the txt files are in the same format)
6. Select the intermediate format in [column]. I only tested two columns. After that, you can set the output name in another "column" on the outer layer.
7. After the [ole db target] defines the database connection, I import the data to the new table of the database. Click [new] to create a table, and then create a table in the database.
8. Right-click the txtSource attribute and select the button on the right of the two data sources.
9. Select ConnectString for the property, select the expression button, find the file variable defined previously, and drag the mouse to the text box below, OK!
10. At this point, the design is complete, and now it runs. As a result, the program runs five times, one line at a time. I tested one record for each file, so there were only five records.
Done!