SSIS – Again Play with FLAT File

Introduction

This article is related to one of my previous article named 

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

Web References:

http://www.sqlknowledgebank.blogspot.in/2015/11/ssis-moving-file-on-different-folder.html

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
E:\Preparation\SSIS\FlatFile\Source

Ø  If the File Size is ZERO then move it to
E:\Preparation\SSIS\FlatFile\Destination-ZeroSize

Ø  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
v_FilePath	String	E:\Preparation\SSIS\FlatFile\Source\text.txt
v_FileSize	Int64
v_MoveFilePathWithNONZEROSize	String	E:\Preparation\SSIS\FlatFile\Destination-NONZeroSize\
v_MoveFilePathWithZEROSize	String	E:\Preparation\SSIS\FlatFile\Destination-ZeroSize\
v_SourceFileDirectory	String	E:\Preparation\SSIS\FlatFile\Source\

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;

#endregion

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