1

I am thinking of using ssis reading excel files in the folder.

The folder is updated daily by putting new file in without deleting any old files.

I am now using a 'for each' container to loop all the files and loading them into a consolidated table.

However, the boss only wants the latest file to be loaded into the table and he does not want a incremental table.

Can ssis check the file creation date using some functions in ssis and only load the latest one?

John Saunders
  • 160,644
  • 26
  • 247
  • 397
ryan
  • 91
  • 2
  • 2
  • 8
  • 1
    Please don't just ask us to solve the problem for you. Show us how _you_ tried to solve the problem yourself, then show us _exactly_ what the result was, and tell us why you feel it didn't work. See "[What Have You Tried?](http://whathaveyoutried.com/)" for an excellent article that you _really need to read_. – John Saunders Nov 27 '14 at 00:06
  • "I am thinking of using ssis reading excel files in the folder" - that would be your first mistake! Every time I mistakenly have that thought, sanity prevails and I do it in C# (pick a language that it not SSIS) – Mitch Wheat Nov 27 '14 at 00:07
  • @MitchWheat I disagree. There are definitely occasions when SSIS is the right tool for the job, depending on the structure of the spreadsheet. If the structure is simple then SSIS is ok. Otherwise I agree that C# and the OpenXML SDK is a better approach. – Greg the Incredulous Nov 27 '14 at 00:09
  • @Greg: Everytime I think "It's a multiple excel files in folder read. I know! I'll use SSIS." Everytime I regret it. You make a single change to the package and you end up reconfiguring it. It's insane. – Mitch Wheat Nov 27 '14 at 00:11

1 Answers1

4
ou can use this script:

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

also you can cgeck this link below:

http://sqlage.blogspot.in/2013/12/ssis-how-to-get-most-recent-file-from.html
Rahul Sharma
  • 453
  • 3
  • 10