Sunday, 6 December 2015

SSIS – Again Play with FLAT File


This article is related to one of my previous article named 

SSIS - Moving File on Different Folder Based on Size”. 

Web References:

To continue with this article, please move one my previous one. It is requested that in my previous article I am using a mid level temporary table and now my friend from Delhi requested me that not to use the mid table for that.

Anyway, I am always with my friend circle and try to fulfill their request. Due to my busy schedule sometimes I am late.

Hope this it will be informative and don’t forget to make comments if you like or dislike it. Your comments are valuable for me and all the readers also.

What the Scenario is

I have a folder E:\Preparation\SSIS\FlatFile\Source which contains three file. On them one of the file is Empty (Size 0) and other two have some records.

What we want

Ø  Read the ALL File from

Ø  If the File Size is ZERO then move it to

Ø  If the file have contains means NON ZERO move it to E:\Preparation\SSIS\FlatFile\Destination-NONZeroSize

Hope you understand the Scenario.

How we Do That


Step – 1  [ The Control Flow of the Package ]

Step – 2 [ The Variable Declaration ]

Variable Name
Data Type
Default Values


Step – 2 [ The ForEach Loop Container ]

Step – 3 [ Script Task named Check the File Size ]

The Script

#region Namespaces
using System;
using System.Data;
using System.IO;                        // Added to get file properties
using System.Security.Principal;        // Added to get file owner
using System.Security.AccessControl;    // Added to get file owner
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

public void Main()
       // Variable for file information
       FileInfo fileInfo;

       // Fill fileInfo variable with file information
       fileInfo = new FileInfo(Dts.Variables["User::v_FilePath"].Value.ToString());

       // Get the rest of the file properties if the file exists
       if (fileInfo.Exists)

                // Get size of the file in bytes
                Dts.Variables["User::v_FileSize"].Value = fileInfo.Length;


       Dts.TaskResult = (int)ScriptResults.Success;

Step – 4 [ Precedence Constraint ]

Step – 5 [ File System Task named MOVE File with ZERO Size ]

Step – 6 [ Precedence Constraint ]

Step – 7 [ File System Task named MOVE File with NON ZERO Size ]

Step – 8 [ Run the Package and Observe The Output ]

Hope you like it.

Posted by: MR. JOYDEEP DAS


  1. Thank you for your post it is easy to understand the fresher like me..Look for more post from you in future also.

  2. Thanks a lot for sharing this . Well explained with proper scree sort makes easy to understand. Looking forward more of your post in future .