SSIS -How To Get Most Recent File from Directory And Load To a Table [ SSIS Script Task ]

Scenario:

We get the files in our Source Folder all day long. Each file is appended copy of previous file. We want to create a SSIS Package that will load only the most recent file to our table.


Solution :

We will be using Script task to find out the name of the most recent file and Data Flow Task to load that file to our table.Test_File_20131226.txt is the file that should be read as it is most recent file.

Step 1:
Create a variable VarFolderPath that will contain the folder path in which our files exist and second variable with name VarFileName which will hold the value of most recent File Name.

Step 2:
Drag Script Task to Control Flow Pane and Provide the variables to it as shown 

Click on Edit Script and write below script. I have only added the code which is in Red. I have included Messagebox.show just for debugging.

/*
   Microsoft SQL Server Integration Services Script Task
   Write scripts using Microsoft Visual C# 2008.
   The ScriptMain is the entry point class of the script.
*/

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;



namespace ST_2650e9fc7f2347b2826459c2dce1b5be.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

          public void Main()
        {
   
     // TODO: Add your code here
            var directory= new DirectoryInfo(Dts.Variables["User::VarFolderPath"].Value.ToString());
                     
            FileInfo[] files = directory.GetFiles();
            DateTime lastModified = DateTime.MinValue;

            foreach (FileInfo file in files)
            {
                if (file.LastWriteTime > lastModified)
                {
                    lastModified = file.LastWriteTime;
                    Dts.Variables["User::VarFileName"].Value = file.ToString();
                }
            }

            MessageBox.Show(Dts.Variables["User::VarFileName"].Value.ToString());
         

            Dts.TaskResult = (int)ScriptResults.Success;
        }
    }
}

Step 3:
Let's run our SSIS Package to make sure that our script is returning us expected file name( Most recent modified by)

Step 4 :
Next step will be to load this file to our destination table. Bring Data Flow Task to Control Flow Pane and connect Script Task to it.Drag Flat File Source inside Data Flow Task and make connection to any file in the Source Folder as all the files have same structure.


Step 5:
As latest file name will be changing, Let's configure our Flat File Connection Manager to use VarFileName variable.

Final Output:
Drag Multicast Transformation in Data Flow Task and connect Flat File Source to it. Double clicking on green line between them and put Data Viewer.  I am using Multicast Transformation just for test purpose. In real scenario you will be using OLE DB Destination if you are loading data to table or any other destination depending upon your requirements.

The latest file is read from folder and display by using Data Viewer.

34 comments:

  1. Thanks a lot!!! YOu are the best.

    ReplyDelete
  2. This comment has been removed by the author.

    ReplyDelete
  3. Thanks for providing this information .I hope it will be fruitfull for me. Thank you so much and keep posting.scaffolding dealers in chennai

    aluminium scaffolding dealers in chennai

    ReplyDelete
  4. Thanks for providing this information .I hope it will be fruitfull for me. Thank you so much and keep posting.scaffolding dealers in chennai

    aluminium scaffolding dealers in chennai

    ReplyDelete
  5. Thanks for providing this information .I hope it will be fruitfull for me. Thank you so much and keep posting.scaffolding dealers in chennai

    aluminium scaffolding dealers in chennai

    ReplyDelete
  6. Sorry got it..works well.. thanks

    ReplyDelete
  7. Thank you so much for practically explaining things about SSIS and SQL.

    SSIS Postgresql Write

    ReplyDelete
  8. The information you've shared in this blog is remarkable. Thanks for sharing such quality information.
    aluminium scaffolding dealers in chennai
    scaffolding dealers in chennai

    ReplyDelete
  9. may i get the source of the above

    ReplyDelete
  10. This is very good information, Thank you

    ReplyDelete
  11. Thanks for provide great informatic and looking beautiful blog, really nice required information & the things i never imagined and i would request, wright more blog and blog post like that for us. Thanks you once agian

    court marriage in delhi ncr
    court marriage in delhi
    court marriage in noida
    court marriage in ghaziabad
    court marriage in gurgaon
    court marriage in faridabad
    court marriage in greater noida
    name change online
    court marriage in chandigarh
    court marriage in bangalore

    ReplyDelete
  12. I really feel there is a need to provide the best information about SSIS and its uitlities that produces the best results.

    SSIS Postgresql Write

    ReplyDelete
  13. where we can download above example text files

    ReplyDelete
  14. Thank you so much for sharing this worth able content with us. The concept taken here will be useful for my future programs and i will surely implement them in my study. Keep blogging article like this.
    CRM with Invoicing

    ReplyDelete