Dynamic Flat file connection manager

Question

Dynamic Flat file connection manager

bladerunner148

SSCommitted

Points: 1824
More actions
November 11, 2010 at 8:18 am

#157308

Hi There,
Currently, one of our clients dump a file everyday at midnight into our system in the following format:
zvvendor+Year+Month+Day+Hour+Min+Sec.dat
so, on Nov 11th, it would be zvvendor20101111002513.dat
The hours mins and seconds varies everytime they dump the file.
I have a SSIS package that uses a Flat File Connection Manager to grab the dat file and dump the data to a sql database. Well, to get the right file, I would need to point the flat file to the right filename. Since the filename varies each day, I found this expression builder where you can set a variable and point to the file you want. The date part is fine but the hours, mins and seconds differ, how would i set this in my expression builder?
This is what I have currently in my expression.
@[User::NewFileName] + "zvvendor"+(DT_STR,4,1252)YEAR(GETDATE())+RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()),2)+RIGHT("0" + (DT_WSTR,2)DAY(DATEADD("dd", -1, GETDATE())),2)+".dat"
@[User::NewFileName] returns "C:\DealerFile\"
When i evaluate the expression, I get C:\DealerFile\zvvendor20101110.dat". Since i am not sure how to include the hours, minutes and seconds in this expression as they vary, what would be the best way to accomplish this? Please advise!!

Viewing 5 posts - 1 through 4 (of 4 total)

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

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

Could you have a task that runs first which renames the file, dropping the h:m:s bit and then feed that into your dataflow as normal?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

bladerunner148 SSCommitted Points: 1824 More actions · Answer 2

Hi Phil,

Thanks for the reply. But this folder has files from every day, so there would be a lot. How would i rename the particular file that i want?

Phil Parkin SSC Guru Points: 246982 More actions · Answer 3

Accepted good practice is to move a file which has been processed to a separate archive folder - then you don't get a mixture of processed and unprocessed in the same place. Could you adopt this - becomes easier then.

Also, if you know that the final six characters of all of the filenames in the folder are hhmmss and can safely be removed, why not rename them all via a script? (You'd need to put something in the script to recognise those filenames which have already been processed - eg if the ninth character from the right (ignoring file extension) is non-numeric, do not rename...

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

bladerunner148 SSCommitted Points: 1824 More actions · Answer 4

Here is what i ended up doing.

Since there would atleast one file uploaded everyday, before i call my routine, i have another package that wipes out the files from this directory. So, when the file is dumped again, there will be only one file in that directory.

Using Foreachloop task, I am getting the file from that directory and storing that in a variable. This variable is then what the Flat File Manager looks at to import from.

For this project, it was okay for me to wipe those files out since someone was planning on cleaning that directory anyway. And this directory is dedicated to just one client who would be uploading only one file after midnight.

Thanks for giving me ideas.