Using variable to evaluate part of file name?

Question

Using variable to evaluate part of file name?

tiredofchoosing

Newbie

Points: 3
More actions
December 9, 2014 at 1:53 pm

#293382

I have a file that gets placed in a folder every morning. I am building a package to import that file into SQL Server 2008. The file has a date stamp in the filename which is easily coded for. My issue is that there's a timestamp on the filename as well and there's no predicting the seconds portion of it. Is it possible to only evaluate the filename through the date portion? ie my variable would read dir\folder\folder\filename_date but the actual path and name would be dir\folder\folder\filename_date_time.txt

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 247180 More actions · Answer 1

You could avoid the whole issue (assuming the import folder only ever contains the files to be processed, because processed files are archived) by using a FOREACH loop selecting files of type *.txt.

Alan Burstein SSC Guru Points: 61152 More actions · Answer 2

Removed... (Did not see that this was an Integration Services thread). What I put together would be wrong. :doze:

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

tiredofchoosing Newbie Points: 3 More actions · Answer 3

2 filenames:

QD Timins-MS U_20141208_050609.txt --run at 5:06 am

QD Timins-MS U_20141207_050634.txt --run at 5:07 am

Everything after the second underscore and before the .txt is unpredictable and random.In my world, the easiest solution would be to have the job that produces these files simply not use a timestamp and I would've been done already. Apparently, I was asking for too much.

Thank you for the input on this, it's very much appreciated.

tiredofchoosing Newbie Points: 3 More actions · Answer 4

Oh, old files are not archived. They simply accumulate in the folder.

Phil Parkin SSC Guru Points: 247180 More actions · Answer 5

tiredofchoosing (12/9/2014)
Oh, old files are not archived. They simply accumulate in the folder.

This is not best practice – you should consider moving the files to a 'Processed' folder after they have been, err, processed.

But to get around your problem, how about using a DOS command to rename the file before you attempt to process it. Something like this should work:

ren "QD Timins-MS U_20141207_*.txt" "QD Timins-MS U_20141207.txt"

Use an Execute Process task to run this, with appropriate Expressions to accommodate the variable file names.