Importing last modified file in SSIS

Question

Good Day All,

I apologise in advance for what is probably a pretty simple problem for many of you.

Basically a file is saved down every hour into a certain folder (file ext .AMA), and I would like to create an SSIS package that runs every hour and imports only the last modified file into a SQL Server database.

I realise I need to use the script component to do this, but I have zero working knowledge of vb.net (I'm stuck with VS 2005). Also, I'm not sure if this needs to be done within a Foreach Loop Container or if it's possible to go directly from the scrip component to the OLE DB Destination?

Would anyone be kind enough to give me a sample script that I can work off, and explain to me how to incorporate it into an SSIS package? I can't make head nor tale of script solutions I've seen from Googling, and many of them seem to be using C# anyway.

Going by the last modified date/time should be okay, but there is a date/time in the filename in the following format "YYMMDDHHMM", I'm not sure how useful that would be though.

Thanks in advance!

Have you considered using a [File System Task](http://technet.microsoft.com/en-us/library/ms140185.aspx)? You can define your *source connection* to be from a variable. — TsSkTo, Nov 27 '13 at 10:25
I have not, but wouldn't I still need to use the script component to define the variable? Thanks for the response. — DPPD, Nov 27 '13 at 11:36
possible duplicate of [Import most recent csv file to sql server in ssis](http://stackoverflow.com/questions/8831060/import-most-recent-csv-file-to-sql-server-in-ssis) — billinkc, Nov 27 '13 at 13:16
The query might be the same but the answers in that thread are utilizing C#, I can't use that as I'm using Visual Studio 2005, so I need a script in vb.net. I'm not a programmer so if I'm missing something I apologise. — DPPD, Nov 27 '13 at 15:17

score 0 · Answer 1 · edited May 23 '17 at 10:25

Instead of giving out the full solution, let us break this project into smaller pieces. Try to solve each of these pieces and the solution should emerge - hopefully. If you get stuck on any piece, feel to revert back with more pointed question. That said, here is what I would suggest - 1. Take one of your .AMA files. Use data flow task. Use Flat file source connection as Source and OleDB as Target. Hard code the source and target connections in the connection manager. If you need any transformation, try using derived columns (because it's easier and accomplishes most of the transformations). If you are able to complete this piece, it would remove your doubts about using a script component.

Next, work on removing the hard coding part mentioned in step one. Find out how to dynamically change the connection strings by using variables.
Put another .AMA file in the same location. Use ForEach task to process both the files. (Not necessarily you will need it)
Import most recent csv file to sql server in ssis

Hope this helps you in find your solution by yourself - as opposed to asking a full solution.

Hi Anoop Verma, that's for your response. I've used variables within a Foreach Loop container before to import all files in another directory, using a flat file source, that's no issue. My issue is in writing the script to identify the most recently modified file in the directory. The thread you linked to in step three is the first thing I came across on my google quest, but it uses a C# script to do this, I don't know how to apply this in the script component in visual studio 2005? Thanks. — DPPD, Nov 27 '13 at 15:23
Thanks for reverting back. That code is very well-documented. I used the a code converter site. Here is the output. — Anoop Verma, Nov 27 '13 at 16:16

score 0 · Answer 2 · answered Nov 27 '13 at 16:25

Public Sub Main()
Dim fileMask As String = "*.csv"
Dim mostRecentFile As String = String.Empty
Dim rootFolder As String = 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
Dim candidate As System.IO.FileInfo = ScriptMain.GetLatestFile(rootFolder, fileMask)

If candidate IsNot Nothing Then
    mostRecentFile = candidate.FullName
End If

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

Dts.TaskResult = (int)ScriptResults.Success
End Sub




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();
}

This is the site I used for conversion: http://www.developerfusion.com/tools/convert/csharp-to-vb

Importing last modified file in SSIS

2 Answers2