• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 544
  • Last Modified:

SSIS - Import data from excel using the most recent file in the folder

Hello,

What I'm trying to do is insert data into a SQL table from an excel spreadsheet. The challenge is being able to grab the most recent excel spreadsheet as the data source.

The file has a naming convention similar to:
File(1).xls, File(2).xls, File(3), File(4) etc.,

Can someone tell me how I can grab the latest file? The issue is the person who is placing the file in the directory only increments the file each day, so if today was Monday, the file will be named File(1).xls, and Tuesday would be File(2).xls.

So the logic would be grabbing the file with the greatest number in the parenthesis.

Right now, the data is being inserted correctly if I preselect an excel spreadsheet, however, not pulling the most recent file in the folder.

The data is being inserted using Excel Source --> OLE DB Destination.

Does anyone have any idea how to accomplish this?
0
TeknikDev
Asked:
TeknikDev
  • 7
  • 6
1 Solution
 
sentnerCommented:
Have you tried simply sorting the directory by date modified and picking the latest file?
0
 
TeknikDevAuthor Commented:
This is an automated process that runs daily....so how would i get the SSIS to sort the folder and what and how can i accomplish this?

Apologizes, I'm fairly new to SSIS but was tasked with this to do.
0
 
sentnerCommented:
I'm afraid I'm not familiar with SSIS myself, but this could be done using VBA or WSH script.  Your best bet may be to have a script run on a schedule to pick out the first file in a directory listing that's been modified that same day, and copy it to another location with a fixed name that SSIS can just use.

For example, you could use the "FORFILES" command in windows:

http://ss64.com/nt/forfiles.html

Something like:

forfiles /m File*.xls /c "cmd /c copy @path c:\temp\todaysfile.xls" /d 0
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
TeknikDevAuthor Commented:
Thanks, anyone else with SSIS experience?
0
 
TeknikDevAuthor Commented:
Also, just confirmed that the "Date modified" field would not be useful since the files are delivered via Box which is a app that syncs the server against the local file so the modified date would be the same for all the files since it all syncs at the same time. Blah

0
 
TeknikDevAuthor Commented:
I've requested that this question be deleted for the following reason:

Repost in correct section.
0
 
sentnerCommented:
Since using the date stamp won't work, here's another suggestion.  You can run a batch file like the one attached, to copy the one with the highest number in () to a fixed named file.

@echo off
set /a _biggest=0
Setlocal EnableDelayedExpansion
 
for /f "tokens=2delims=()" %%i in ( 'dir /b "file(*).xls"' ) do (
   if %%i GTR !_biggest! (set /a _biggest=%%i)
)
echo "Latest: file(%_biggest%).xls"
copy "file(%_biggest%).xls" masterfile.xls


0
 
TeknikDevAuthor Commented:
sentner,  for some reason it doesnt seem to like the path I put in the script.

say if the file i need to compare is located at:

\\chitest\batch\file  or x:\\group\chitest\batch\file
file to compare is: filecompare(1).xls, filecompare(2).xls, filecompare(3).xls etc.,

how would i define that in the script?
0
 
sentnerCommented:
You just have to have it first change to the correct directory, and do the searching on the correct file pattern.  For what you listed, I'd modify it like below.  This assumes that you can change directly to the directory at the command line.  If not, you may have to map the directory to a drive letter.

@echo off
cd \\chitest\batch\file

set /a _biggest=0
Setlocal EnableDelayedExpansion
 
for /f "tokens=2delims=()" %%i in ( 'dir /b "filecompare(*).xls"' ) do (
   if %%i GTR !_biggest! (set /a _biggest=%%i)
)
echo "Latest: file(%_biggest%).xls"
copy "file(%_biggest%).xls" masterfile.xls

Open in new window

0
 
sentnerCommented:
still in progress, but now 2 questions open.  Can a moderator combine the 2?
0
 
TeknikDevAuthor Commented:
Ok, so it worked. Senter, can you tell me how to delete a file using the same script?
0
 
TeknikDevAuthor Commented:
nvm i figured the del function thanks
0
 
sentnerCommented:
No problem.  One other thing, this could very easily be modified to allow you to pass arguments to the script, thus allowing you to use it for multiple purposes.  (I mention this because you said that you had other similar requirements).  For example, you could have the directory and search pattern both passed as arguments.

0
 
Jason Yousef, MSSr. BI DeveloperCommented:
Hi,
You'll need a script task inside your SSIS Package, using the file properties to grab the create date of the file and just use a condition to grab the latest one.
I've blogged a similar solution to grab the last created in the last hour.

http://asqlb.blogspot.com/2011/08/ssis-process-files-that-has-been.html

Hope that helps.
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now