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

kkran 581 Reputation points
2021-03-24T23:34:50.293+00:00

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,856 questions
No comments
{count} votes

Accepted answer
  1. Monalv-MSFT 5,686 Reputation points
    2021-03-25T03:18:52.197+00:00

    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;  
    #endregion  
    
    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;       
                }  
                else  
                {  
                    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;  
    
            }  
    

    81313-scripttask-1.png
    81314-scripttask-2.png

    2.Then set the Precedence Constraint between Script Task and File System Task.
    81260-precedenceconstraint.png

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

    4.We can set breakpoint to see the value of variable when the package is executing.
    81392-cf-cm-variable.png
    81299-cf-execution.png

    Best regards,
    Mona


    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.

    No comments

0 additional answers

Sort by: Most helpful