Change a file name everytime a job runs

Question

Change a file name everytime a job runs

chaudharyabhijit

Ten Centuries

Points: 1314
More actions
May 20, 2010 at 9:59 am

#221277

I am running a job to run a stored procedure from OLEDB Source and then save the output to a flat file destination.
The problem is every time the job runs i have an option to overwrite the data or append the data.
If there a way that every time the job runs a new file is created with the (file name + date run).

Viewing 9 posts - 1 through 8 (of 8 total)

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

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

Easiest solution is to leave the destination file static, but put a File System or Script task at the end of the job which does a file copy for you, to a file with a dynamic name - there are examples all over this forum of how to do that.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

chaudharyabhijit Ten Centuries Points: 1314 More actions · Answer 2

Thanks for the prompt reply Phil, could you guide me to a post where this is covered also how do i make the destination file static?

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

Try this[/url].

By 'static', I was referring to the file name, not the contents of the file. Unless you have done anything fancy, this will already be the case - the destination file name, as reference by an SSIS destination connector, will always be the same (output.csv, or whatever).

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

chaudharyabhijit Ten Centuries Points: 1314 More actions · Answer 4

Hi Phil,

This is what i am doing

On the Connection Manager for the Flat File Destination - In the

Properties page, expand Expressions and create a new Expression for

ConnectionString. Set the Expression to be like the following:

"filename" + (DT_STR, 8, 1252)(YEAR(GETDATE()) * 10000 +

MONTH(GETDATE()) * 100 + DAY(GETDATE())) + ".csv"

Now this appends the file name as i can see in the output, but now i need to upload them to a ftp.

In the ftp task as when i set the set "IsLocalPathVariable" to

true - but then what should I put in the local path? I suspect that

is my issue.

chaudharyabhijit Ten Centuries Points: 1314 More actions · Answer 5

Hello,

The expression is working and everytime i run the data flow i do see a new file come up with the current date. Now i have placed this data flow task in the foreach loop container and its supposed to pick up the latest file, however it picks up the old file.

Is there any solution for this?

vani_r14 SSCrazy Points: 2843 More actions · Answer 6

Hithere

not sure if u have tried this, but have you tried adding the time of creation to pick up the recent one

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 7

chaudharyabhijit (5/21/2010)
Hello,
The expression is working and everytime i run the data flow i do see a new file come up with the current date. Now i have placed this data flow task in the foreach loop container and its supposed to pick up the latest file, however it picks up the old file.
Is there any solution for this?

How did you configure the for each loop container and the data flow task to pick up the most recent file?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

chaudharyabhijit Ten Centuries Points: 1314 More actions · Answer 8

I added a variable for the file path and called it in the for each loop container.