Import most recent csv file to sql server in ssis

ssis how to get most recent file from directory
import multiple csv files into sql server using ssis
ssis copy files based on date
ssis foreach loop file modified date
ssis get file created date
how to load multiple comma delimited files to sql server table in ssis package
ssis get file modified date
importing multiple flat files to multiple tables in ssis

i have an folder, in which i receive .csv files for every half hour with time stamps. Now, i need to take the latest file from the available files and import it into sql server.

For Example

in my source folder, i have

test_01112012_120122.csv test_01112012_123022.csv test_01112012_123555.csv

now i need to fetch the latest file and import that file into sql server with the help of SSIS.

Thanks satish

The code from @garry Vass, or one like it, is going to be needed even if you're using SSIS as your import tool.

Within SSIS, you will need to update the connection string to your flat file connection manager to point to the new file. Ergo, you need to determine what is the most recent file.

Finding the most recent file

Whether you do it by file attributes (Garry's code) or slicing and dicing of file names is going to be dependent upon what your business rules are. Is it always the most recently modified file (attribute) or does it need to be based off the file name being interpreted as a sequence. This matters if the test_01112012_120122.csv had a mistake in it and the contents are updated. The modified date will change but the file name will not and those changes wouldn't get ported back into the database.

I would suggest you create 2 variables of type String and scoped to the package named RootFolder and CurrentFile. Optionally, you can create one called FileMask if you are restricting to a particular type like *.csv. RootFolder would be the base folder you expect to find files in C:\ssisdata\MyProject. CurrentFile will be assigned a value from a script of the fully qualified path to the most recently modified file. I find it helpful at this point to assign a design-time value to CurrentFile, usually to the oldest file in the collection.

Drag a Script Task onto the Control Flow and set as your ReadOnlyVariable User::RootFolder (optionally User::FileMask). Your ReadWriteVariable would be User::CurrentFile.

This script would go inside the public partial class ScriptMain: ... braces

    /// <summary>
    /// This verbose script identifies the most recently modified file of type fileMask
    /// living in RootFolder and assigns that to a DTS level variable.
    /// </summary>
    public void Main()
    {
        string fileMask = "*.csv";
        string mostRecentFile = string.Empty;
        string rootFolder = string.Empty;

        // Assign values from the DTS variables collection.
        // This is case sensitive. User:: is not required
        // but you must convert it from the Object type to a strong type
        rootFolder = Dts.Variables["User::RootFolder"].Value.ToString();

        // Repeat the above pattern to assign a value to fileMask if you wish
        // to make it a more flexible approach

        // Determine the most recent file, this could be null
        System.IO.FileInfo candidate = ScriptMain.GetLatestFile(rootFolder, fileMask);

        if (candidate != null)
        {
            mostRecentFile = candidate.FullName;
        }

        // Push the results back onto the variable
        Dts.Variables["CurrentFile"].Value = mostRecentFile;

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

    /// <summary>
    /// Find the most recent file matching a pattern
    /// </summary>
    /// <param name="directoryName">Folder to begin searching in</param>
    /// <param name="fileExtension">Extension to search, e.g. *.csv</param>
    /// <returns></returns>
    private static System.IO.FileInfo GetLatestFile(string directoryName, string fileExtension)
    {
        System.IO.DirectoryInfo directoryInfo = new System.IO.DirectoryInfo(directoryName);

        System.IO.FileInfo mostRecent = null;

        // Change the SearchOption to AllDirectories if you need to search subfolders
        System.IO.FileInfo[] legacyArray = directoryInfo.GetFiles(fileExtension, System.IO.SearchOption.TopDirectoryOnly);
        foreach (System.IO.FileInfo current in legacyArray)
        {
            if (mostRecent == null)
            {
                mostRecent = current;
            }

            if (current.LastWriteTimeUtc >= mostRecent.LastWriteTimeUtc)
            {
                mostRecent = current;
            }
        }

        return mostRecent;

        // To make the below code work, you'd need to edit the properties of the project
        // change the TargetFramework to probably 3.5 or 4. Not sure
        // Current error is the OrderByDescending doesn't exist for 2.0 framework
        //return directoryInfo.GetFiles(fileExtension)
        //     .OrderByDescending(q => q.LastWriteTimeUtc)
        //     .FirstOrDefault();
    }

    #region ScriptResults declaration
    /// <summary>
    /// This enum provides a convenient shorthand within the scope of this class for setting the
    /// result of the script.
    /// 
    /// This code was generated automatically.
    /// </summary>
    enum ScriptResults
    {
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    };
    #endregion

}
Updating a Connection Manager

At this point, our script has assigned a value to the CurrentFile variable. The next step is to tell SSIS we need to use that file. In your Connection Manager for your CSV, you will need to set an Expression (F4 or right click and select Properties) for the ConnectionString. The value you want to assign is our CurrentFile variable and the way that's expressed is @[User::CurrentFile]

Finally, these screen shots are based on the upcoming release of SQL Server 2012 so the icons may appear different but the functionality remains the same.

Import most recent csv file to sql server in ssis - MSDN, I would like to import the latest csv file from the file directory, could i know how to select the latest csv file from ssis connection manager. I would like to import the latest csv file from the file directory, could i know how to select the latest csv file from ssis connection manager. File Directory: C:\Report. File Name: Report 2013 0521_1402.csv. Report 2013 0521_1502.csv. Report 2013 0522_0502.csv. In this case, I should choose the bold one (Report 2013 0522_0502.csv). Then the

Assuming that you wanted to use C#, to get the newest file in a given directory, you can use a method like this...

private static FileInfo GetLatestFile(string directoryName, string fileExtension)
{
    DirectoryInfo directoryInfo = new DirectoryInfo(directoryName);
    return directoryInfo.GetFiles(fileExtension)
         .OrderByDescending(q => q.LastWriteTimeUtc)
         .FirstOrDefault();
}

This method is called like...

FileInfo file = GetLatestFile( "C:\myDirectory", "*.csv");

And it returns a FileInfo instance (or null) of the file with the most recent write time. You can then use the FileInfo instance to get the name of the file and so on for your processing...

Welcome To TechBrothersIT: SSIS -How To Get Most Recent File , Microsoft SQL Server Integration Services Script Task Write scripts using Microsoft Visual C# 2008. The ScriptMain is the entry point class of the script. */ There are several techniques available to import and export data for SQL Server. In addition, there are free SQL tools and utilities available that might be helpful for specific use cases. In SQL Server Integration Services (SSIS), we can use a Flat File Source to load Text/CSV files.

Take a look at this article for directory listing in tsql http://www.simple-talk.com/sql/t-sql-programming/the-tsql-of-text-files/

Then you can find the latest one by ordering them.

Import Text and CSV Files into SQL Server Database with SSIS , Import Text and CSV Files into SQL Server Database with SSIS Script Task. By: Bhavesh Patel | Updated: 2019-08-30 | Comments (3) | Related: More� step 8. Step 9 : Inside DataFlow Designer , establish link between “Flat File Source ” and ” OLE DB Destination ” . step 9. Step 10 : click on ” New ” Button and select Sql server connection , Data access mode should be ” Table or view ” , Select any Table after clicking DropdownBox .

Load Data from Multiple Text/CSV Files to SQL Server Table with , SSIS Video Scenario: How to load Data from Multiple Text / CSV Files to SQL Server Table Duration: 22:24 Posted: Apr 4, 2016 SSIS -How To Get Most Recent File from Directory A SSIS - How To Load Multiple Files ( .txt or .csv ) SSIS -Export Files from SQL Table To Folder [ How SSIS -How to Import Files (Text, Pdf, Excel, Image TSQL - Alter Table Add Multiple Columns SQL Server; SSIS - Archive Files And Add Timestamp To Each Fi

SSIS Interview Question, SQL Server Integration Services(SSIS) Interview Question " There are 100 files in a Duration: 3:06 Posted: Jan 31, 2015 SSIS: Import files with column names, some without OrPresumably due to inconsistent configuration of logging devices, I need to load a collection of csv files via SSIS that will sometimes have a first row with column names and will sometimes not.

SSIS, SSIS - import most recent files based on varying file name and file time – Learn Home � Forums � SQL Server 2012 � SQL 2012 - General; SSIS - import most recent files that only the more recent file (say, FILEA_02-20-2017-11.csv) is imported and A SQL query walks into a bar and sees two tables. Import the CSV file in Azure SQL database using SQL Server Import/Export wizard First, let us import data from the MySQL Table to the CSV files. To do that, open MySQL Workbench Connect to the MySQL Server From the left pan, expand the sakila database Expand Tables Right-click on any Table click on Table Data Export Wizard .

Comments
  • Thanks Garry and billinkc, this post was very useful, and i would like to ask one more thing, how to get this latest filename which was generated in script task to be dumped into my destination table in sql server.
  • Use that variable in a derived column task. I'll answer it over on your other question as well stackoverflow.com/questions/8877695/…
  • How can I achieve that here: stackoverflow.com/questions/22969449/…
  • i would to know this approach in ssis.
  • Should the C# tag be removed?
  • How can I use your code for my question here: stackoverflow.com/questions/22969449/…
  • @SiKni8 , probably you should use a c# utility to read the file system and directly populate a table. Then read from the table