question

kkran-5951 avatar image
0 Votes"
kkran-5951 asked Monalv-msft edited

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

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-transact-sqlsql-server-integration-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Monalv-msft avatar image
0 Votes"
Monalv-msft answered Monalv-msft edited

Hi @kkran-5951,

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.



scripttask-1.png (17.7 KiB)
scripttask-2.png (32.4 KiB)
filesystemtask.png (19.5 KiB)
cf-cm-variable.png (18.9 KiB)
cf-execution.png (31.1 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.