Introduction
In this post, we will show how to save a list of files into a table a table using SSIS. The article will show how to get a list of files and then store the list of files into an Excel file.
Prerequisites
Before we perform the steps listed in this article, you will need to make sure the following prerequisites are met:
- SSIS designer installed. Sometimes it is referred to as BIDS or SSDT (download it from the Microsoft site).
- Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
- Make sure ZappySys SSIS PowerPack is installed (download it).
- Optional (If you want to Deploy and Schedule ) – Deploy and Schedule SSIS Packages
Step-by-step process to save the list of files and folders into an article
Save the information in a variable with the Advanced file system task
1. Drag and drop the Advance File System Task from the SSIS toolbox, chose to Get file list as ADO.net Data table and select an object variable type
Getting the file list in a variable
Save the result in a file with Logging task in order to save a list of files into a table
2. The next step is to save the result in a HTML file: select the file destination, uncheck append and prefix datetime. Select the file in log mode and none in message type, and insert the variable in the message as follows:
Save the value from the variable in a file without a prefix
Read the table and save it on a new file in order to save a list of files into a table
3. Now, Drag and Drop SSIS Data Flow Task from SSIS Toolbox
4. Double click on the DataFlow task to see DataFlow designer surface.
5. From the SSIS toolbox drag and drop HTML source and insert the path for the new file, you can preview the file if it was previously created.
Preview the HTML file
7. Then you can use a destination like the Excel destination, JSON destination or CSV destination
Save the path list into an excel file
If everything is OK, the information will be stored in Excel. However, you can select any destination.
Conclusion
In this article, we show how to save a list of files in a table. Basically, we use the Advanced file system to save the list in a file. We use the Logging Task
