Wednesday 18 November 2015

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

No comments:

Post a Comment