CODEWITHSUNDEEP
ssis
twon
twon

Reputation: 21

How to get file name dynamically with time stamp in SSIS

I have a flat file source which has to be loaded daily to a table. I receive the file in the following format "filename_20190509040235.txt"

I used expression to get file name with date, how can I get the time stamp?

The time stamp is different in each date. The file get generated in the afternoon and the package is planning to run every night.

Upvotes: 2

Views: 4763

Answers (1)

userfl89
userfl89

Reputation: 4810

Assuming you want to load files based on a certain time defined by the timestamp on the file name, an overview of this process is below. As noted, files with a timestamp within the 12 hours prior to the package execution are returned, and you may need to adjust this to your specific needs. This also uses the same file name/timestamp format as indicated in your question, i.e. filename_20190509040235.txt.

  • Create an object and string variable in SSIS. On the Flat File connection manager, add the string variable as the expression for the connection string. This can be done from the Properties window (press F4) on the connection manager, going to the Expressions field, pressing the ellipsis next to it, choosing the ConnectionString property on the next window and selecting the recently created string variable as the expression for this.

  • Add a Script Task on the Control Flow. Add the object variable in the ReadWriteVariables field. If the directory holding the files is stored in an SSIS variable add this variable in the in the ReadOnlyVariables field.

  • Example code for this is below. Your post stated the files are generated in the afternoon with the package running nightly. Not being sure of the exact requirements, this just returns files with a timestamp within 12 hours of the current time. You can change this by adjusting the parameter of DateTime.Now.AddHours, which currently subtracts 12 hours from the current time (i.e. adds -12). This will go in the Main method of the Script Task. Be sure to add the references noted below too.

  • Add a Foreach Loop after the Script Task and for the enumerator type select Foreach From Variable Enumerator. On the Variable field of the Collection tab, choose the object variable that was populated in the Script Task. Next on the Variable Mappings pane select the string variable created earlier (set as the connection string for the Flat File connection manager) at index 0.

  • Inside the Foreach Loop add a Data Flow Task. Within the Data Flow Task, create a Flat File Source component using the Flat File connection manager and add the appropriate destination component. Connect these two and ensure that the columns are mapped correctly on the destination.

Script Task:

using System.IO;
using System.Collections.Generic;


//get source folder from SSIS string variable (if held there)
string sourceDirectory = Dts.Variables["User::SourceDirectory"].Value.ToString();

DirectoryInfo di = new DirectoryInfo(sourceDirectory);

List<string> recentFiles = new List<string>();
foreach (FileInfo fi in di.EnumerateFiles())
{
    //use Name to only get file name, not path
    string fileName = fi.Name;

    string hour =  fileName.Substring(17, 2);
    string minute = fileName.Substring(19, 2);
    string second = fileName.Substring(21, 2);
    string year = fileName.Substring(9, 4);
    string month = fileName.Substring(13, 2);
    string day = fileName.Substring(15, 2);

    string dateOnFile = month + "/" + day + "/" + year + " "
        + hour + ":" + minute + ":" + second;

    DateTime fileDate;
    //prevent errors in case of bad dates
    if (DateTime.TryParse(dateOnFile, out fileDate))
    {
        //files from last 12 hours
        if (fileDate >= DateTime.Now.AddHours(-12))
        {
            //FullName for file path
            recentFiles.Add(fi.FullName);
        }
    }
}
//populate SSIS object variable with file list
Dts.Variables["User::ObjectVariable"].Value = recentFiles;

Upvotes: 2

Related Questions