How to get the count of files in a folder using for each loop container in ssis
I have multiple files in a folder and these files are processed by a for each loop container one by one, after which the data is stored in a SQL Server table.
I need to store a sequence of files that loop container is processing.
Suppose there are 5 Files being processed sequentially:
A, B, C, D, E
So table should have data like-
Data of A, 1
Data of B, 2
Data of C, 3
and so on
Can I do it without using script task?
Follow these steps:
Create a Foreach Loop Container.
a. Right-click and Edit the container.
b. On the Collection tab, specify the folder and file mask. Set Retroeve file name to 'Fully qualified'.
c. On the Variable Mappings tab, click into an empty Variable cell and select . In the Add Variable dialog, specify a variable name, eg 'Filename'; press OK. This variable will be mapped to Index 0 (ie the fully qualified file name).
Create a new Flat File Connection.
a. In the Connection Managers area (under the main designer pane), right-click and select New Flat File Connection.
b. In the Flat File Connection Manager Editor dialog, specify the Connection manager name, eg 'Data file'. Click Browse and select any one of your data files. Configure file format as required (delimiters, columns, text qualifier, etc). Press OK.
c. Select the new Data file connection manager. In the Properties window (on the right side, usually under the Solution Explorer), locate Expressions item. Click on [...] to show the Property Expressions Editor dialog.
d. In the Property Expressions Editor dialog, set Property cell to 'ConnectionString' and Expression to @[User::Filename] (where Filename is the name of the variable you created earlier. Press OK.
Add a Data Flow Task to the Foreach Loop container.
Right-click and Edit Data Flow Task. In the Data Flow view, add a Flat File Source and an OLE DB Destination (or another destination, as required). Right-click and Edit on each Source and Destination, and configure as required.
