SSIS - Moving File on Different Folder Based on Size

Introduction

One of my friends is asking me to provide some real life scenario with SSIS. He has some query related to SSIS and he mailed me to provide the solution. In this article I am providing one of the solutions he is asking me for long time. Hope it will be informative and you enjoy the article.

Case Scenario

We have three flat file in the folder named E:\Preparation\SSIS\FlatFile\EmployeeFile. The details of the Flat file is mentioned bellow

Flat File Name	Size
EmployeeFile-1.txt	69 bytes
EmployeeFile-2.txt	0 bytes
EmployeeFile-3.txt	56 bytes

We have a MS SQL Server Table named tbl_EmployeeFilesDetails with columns name IdNo, FilePath, FileName and FileSize.

We have to read the file from the folder and fill the tbl_EmployeeFilesDetails table first and then move the 0 bytes file to destination folder. In our case it is E:\Preparation\SSIS\FlatFile\EmployeeFile\MoveZero

Hope you understand the scenario.

How we do that

Step-1 [ Create the Table Object ]

-- Employee Flat File Details Table --

IF OBJECT_ID(N'[dbo].[tbl_EmployeeFileDetails]', N'U')IS NOT NULL

   BEGIN

       DROP TABLE [dbo].[tbl_EmployeeFileDetails];

   END

GO

CREATE TABLE [dbo].[tbl_EmployeeFileDetails]

    (

        IdNo      INT           NOT NULL IDENTITY PRIMARY KEY,

        FilePath  VARCHAR(255)  NOT NULL,

        FilesName VARCHAR(50)   NOT NULL,

        FileSize  INT           NOT NULL

    );     

Step – 2 [ Create some Package Level Variable ]

Variable Name	Scope	Data Type	Value
v_FileName	Package	String	E:\Preparation\SSIS\FlatFile\EmployeeFile\file1.txt
v_FilePath	Package	String	E:\Preparation\SSIS\FlatFile\EmployeeFile
v_FileSize	Package	Int32	0
v_MoveZeroPath	Package	String	E:\Preparation\SSIS\FlatFile\EmployeeFile\MoveZero
v_OobjectFile	Package	Object	System.Object

Step-3 [ Create ADO.net Connection Manager ]

We create a simple ADO.net Connection manager and named it ADO.NET DBConnection

Step-4 [ The Package  in Control Flow Tab ]

Step – 5 [ The Execute SQL Ttask named Truncate Table ]

TRUNCATE TABLE [dbo].[tbl_EmployeeFileDetails];

Step – 6 [ The Script Task named Insert into Table ]

Click on Edit Script

#region Namespaces

using System;

using System.Data;

using Microsoft.SqlServer.Dts.Runtime;

using System.Windows.Forms;

using System.IO;

using System.Data.SqlClient;

#endregion

public void Main()

        {

        SqlConnection myADONETConnection = new SqlConnection();

        myADONETConnection = (SqlConnection)(Dts.Connections["ADO.NET  

           DBConnection"].AcquireConnection(Dts.Transaction) as SqlConnection);

           

        myADONETConnection.Close();

        myADONETConnection.Open();

        string DirPath = Dts.Variables["User::v_FilePath"].Value.ToString();

        string[] files = Directory.GetFiles(DirPath);

        SqlCommand sqlCmd = new SqlCommand();

        sqlCmd.Connection = myADONETConnection;

        foreach (string filename in files)

        {

        FileInfo file = new FileInfo(filename);

        sqlCmd.CommandText =

        "INSERT INTO [dbo].[tbl_EmployeeFileDetails](FilePath, FilesName, FileSize)" +

        " Values('" + DirPath + "','" + file.Name + "','" + file.Length + "')";

               

        sqlCmd.ExecuteNonQuery();

        }

        myADONETConnection.Close();

        Dts.TaskResult = (int)ScriptResults.Success;

        }

Here we are actually read the file form folder and Insert the value into tbl_EmployeeFilesDetails table.

Step – 7 [ Execute SQL Task named Read from Table ]

SELECT FilePath+'\'+FilesName AS FilePath, FileSize

FROM   [dbo].[tbl_EmployeeFileDetails];

Step – 8 [ For Each Loop Container ]

Step – 9 [ The Expression Task ]

Step -10 [  The Precedence Constraint ]

Step – 11 [ File System Task named Move 0 file to Folder ]

Step-12 [ Observation After Execution of Task ]

SELECT * FROM [dbo].[tbl_EmployeeFileDetails];

IdNo      FilePath                                                            FilesName                 FileSize

1              E:\Preparation\SSIS\FlatFile\EmployeeFile    EmployeeFile-1.txt    69

2              E:\Preparation\SSIS\FlatFile\EmployeeFile    EmployeeFile-2.txt    0

3              E:\Preparation\SSIS\FlatFile\EmployeeFile    EmployeeFile-3.txt    56

Hope you like it.

Posted by: JOYDEEP DAS