SSIS : File System Task: Move files to another folder if the record count is greater than or equal to 1

kkran 661 Reputation points

Hi Team - In SSIS, I have created a task to create a file. The destination is a text file. The file is created in the Stage folder.

So, Now from the Stage folder, I want to read the file. If the file in the Stage folder (which I created above) has a record count of 1 or greater than 1 record then move to another folder i.e Final Folder.

Could you please me out on how I do that? Thank you in advance.

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
1,962 questions
No comments
{count} votes

Accepted answer
  1. Monalv-MSFT 5,691 Reputation points

    Hi @kkran ,

    1.We can use the following script in Script Task to check if the file is empty.

    #region Namespaces  
    using System;  
    using System.Data;  
    using Microsoft.SqlServer.Dts.Runtime;  
    using System.Windows.Forms;  
    using System.IO;  
    public void Main()  
                string ffConnection = (string)(Dts.Connections["EmptyFlatFileTest"].AcquireConnection(null) as String);  
                FileInfo flatFileInfo = new FileInfo(ffConnection);  
                // If file size is 0 bytes, flat file does not contain data.    
                long fileSize = flatFileInfo.Length;  
                if (fileSize > 0)  
                    Dts.Variables["FFIsEmpty"].Value = false;       
                    Dts.Variables["FFIsEmpty"].Value = true;  
                bool fireAgain = false;  
                Dts.Events.FireInformation(0, "Script Task", String.Format("{0}: {1}", ffConnection, Dts.Variables["FFIsEmpty"].Value), String.Empty, 0, ref fireAgain);  
                Dts.TaskResult = (int)ScriptResults.Success;  


    2.Then set the Precedence Constraint between Script Task and File System Task.

    3.After that, please drag File System Task to copy the file to the final folder.

    4.We can set breakpoint to see the value of variable when the package is executing.

    Best regards,

    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

0 additional answers

Sort by: Most helpful