Get the most recent file to copy into a sql table with ssis

Good Morning,

I get a weekly csv file from one of our vendors that I put into a sql table through a ssis package.  The file name has a datetamp in the title,ex coursereport_2015_05_25, coursereport_2015_05_18, coursereport_2015_05_11.  I want to be able to get the file with the latest datestamp through the ssis package,  the path is \\ms01adm03\data\courses.  I cannot use xp_cmdshell or bulk file insert(Dba's at my job wont let us).
Butterfly2Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vikas GargBusiness Intelligence DeveloperCommented:
Hi,

You can do this by using for each loop and a script task.
For each loop will loop through the file names from the folder and with use of script task you can store the file name to a variable and later in that loop store that variable in an sql table.

After completion of the loop split the name and date from sql table and find the file name with max date value.
Now store it in to a variable and in your flat file connection string use this variable as file name in the connection string.

Hope this would help you to implement
0
sammySeltzerCommented:
I do something similarly but take a different approach to appending the most recent records into the database.

First, I dump ALL the contents of the .csv file into a staging table.

Then I use a simple script to add only the records from the .csv file that don't exist on the database.

The only requirement is that there must be a unique key that can relate the db to the  .csv file.

In your case, you would use the ssis package to dump all the .csv data into a staging table and then use the script I alluded to.

Let me know if you wish to go this route.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.