How to get File Count from a Folder in SSIS Package - SSIS Tutorial

Scenario:

Was reading a question on MSDN forum and a user asked How to Get File Count in a Folder? He has further added to his question, He wanted to get the file with with file starting with some prefix.

We are going to write an SSIS Package by using that we can Find total Number or files in a folder or if prefix value provide, then get the files count which start with that prefix.

Solution:

Step 1: 
Let's create three variables
FileCnt: In this variable we will save the total file count from a folder
FolderPath: We will provide the folder path from which we want to count files
Prefix: Provide the prefix. I have provided Customer. If we will not provide anything then our package will count all the file in given folder.

How to get File Count in SSIS Package by using Script Task - SSIS Tutorial 



Step 2:

In provided folder path, I have four files as shown below.
Get the file count in SSIS Package and use in Expressions to Run next Tasks - SSIS Tutorial



Step 3:
Bring the Script Task to Control Flow Pane. Double Click and open, then Map the Variables as shown  below.
How to map variables in Script Task in SSIS Package to get File Count from a folder - SSIS Tutorial

Click on Edit Script and then you will be writing script by using C# in Script Task Editor.


Step 4: 
Go under #Region Namespaces and type
using System.IO;

Go Further Down and you will see this line of code
public void Main()

{
Paste below code here in Main Function.


           String FolderPath=Dts.Variables["User::FolderPath"].Value.ToString();
            string Prefix = Dts.Variables["User::Prefix"].Value.ToString();
            Int32 FileCnt = 0;
            var directory = new DirectoryInfo(FolderPath);
            FileInfo[] files = directory.GetFiles(Prefix+"*");
             //Declare and initilize variables            
            //Get one Book(Excel file at a time)
            foreach (FileInfo file in files)
            {
                FileCnt += 1;                       
                MessageBox.Show(file.Name);
            }
            MessageBox.Show(FileCnt.ToString());
            Dts.Variables["User::FileCnt"].Value = Convert.ToInt32(FileCnt);


Step 5:
Save the code and close Script Task Editor. As we want to run next task depend upon the value of our variable FileCnt. Bring the next Task. In my case I brought another Script Task. Open it and then write expressions as shown below.
The green arrow is called precedence constraint. If you want to learn more.  Check the videos under Precedence Constraints heading on this link.

How to use Presedence Constraint in SSIS Package to Control Flow of Execution - SSIS Tutorial


Step 6: 
Let's run our SSIS Package. I have left MessageBox.Show enable in Script Task. You can delete or comment once done. I am going to use that to see the variable value just for test purpose.
FileCnt variable printed =2 as we have two files which start with prefix Customer.

Once you hit Ok, Next Task is going to run as expressions will be true for Precedence Constraint. If we get any other value beside 2. Second Task will not run.

4 comments:

  1. Code works but how do you get rid of the prompt to click ok for next file load run

    ReplyDelete
  2. User::FileCnt in ReadWriteVariables of Script Task editor.

    ReplyDelete