How to upload data from files stored in several folders to a database using SSIS?

Question

I have many files stored in separate folders that I would like to extract and paste into a single table in a SQL Server database. Setting up folders looks something like this:

Folder 1 |--> SubFolder 1.1 | |--> File 1 | |--> File 2 |--> SubFolder 1.2 | |--> File 1 | |--> File 2 |--> SubFolder 1.3 |--> File 1 |--> File 2 

There are about 100 subfolders, and all of them have two .txt files that I need to extract.

I need to load data into a structured table as shown below.

 Column1 Column2 Column3 Column4 Column5 Column6 ------------- --------- --------- --------- --------- --------- SubFolderName File1Col1 File1Col2 File1Col3 File1Col4 File2Col1 

Does anyone know how I can do this using SSIS?

Answer 1

You should use the Traverse subfolders option under the Foreach File Enumerator option available in the Foreach Loop container . The Loop Loach container is available in the Control Flow task.

The following are some examples illustrating the enumeration of Foreach file enumeration:

How To - Tasks and Transformations: Foreach File Loop

Containers for SSIS Packages - Part 5: Loop Outline Containers

Hope this helps.

Answer 2

You can follow these steps:

• You can use the Foreach File Enumerator and Traverse subfolders options to iterate the files. Keep in mind that you must use Fully qualified and save the file path to a user variable called IncomingFile .
• You can create variables with the names SubFolderName and FileName .

• Pass the variable IncomingFile as the variable readonly and subfolderName and FileName as ReadWriteVariable. You can use the script to get the subfolder name.

   var incomingFile = Dts.Variables["IncomingFile"].Value.ToString(); FileInfo fileInfo = new FileInfo(fileFullPath); string subFolderPath = fileInfo.Directory.Name; string fileName = fileInfo.Name; Dts.Variables["SubFolderName"].Value = subFolderPath; Dts.Variables["FileName"].Value = fileName; 

• Drag and drop the two data streams that connect to the ScriptComponet. You should write the priority constraint as follows [for both evaluations the operation must be "Expression"].

    For DataFlow 1 -> `@FileName=="Text 1"` For DataFlow 2 -> `@FileName=="Text 2"` 

• In your data stream 1, use the derived column and use the SubFolderName variable to display in "Column 1" in your example. Other columns will be displayed from your txt file except “Column6”.

• In your data stream 2, generate data on the SQL server using subfoldername.

• Write a stored procedure that updates the Coloumn 6 table by comparing the name of the subfolder.

• Use the SQL Execute task to start the stored procedure.

Your package will look like this.

Hope this helps!