Changing destination csv filename

Question

Changing destination csv filename

Brad Allison

Hall of Fame

Points: 3550
More actions
September 7, 2016 at 6:24 am

#329816

I apologize if this question has been already asked and answered, but I have yet to find a good solution for this. I have a monthly SSIS job that sends formatted data out of one of our SQL database tables and into a simple csv file. The receiving company now needs that file to have the DATE of the operation in the file name (and not a static file name like before). Is there a way in the SSIS job to rename the destination csv filename?
Thanks for the information.

Viewing 13 posts - 1 through 12 (of 12 total)

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

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

It's probably easier to leave the destination file name as is and add a subsequent task to do a copy/rename.

If you do a search on SSIS file system task rename file with date or something akin, you should find examples all over the place.

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

Brad Allison Hall of Fame Points: 3550 More actions · Answer 2

Thanks and that is the direction I was just looking at, using the File System Task to manipulate that filename after the processing was done. I just now need to see how to use that control and I will search just as you advised.

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

Or if you are happy writing some C# and using a Script Task (my preference), it takes only three or four lines of code. I can provide an example if you are interested.

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

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

You could also just write an expression on the flat file connection manager.

example

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

Brad Allison Hall of Fame Points: 3550 More actions · Answer 5

Thanks, that would be great too as in this project I am not using C#, but I easily could.

Brad Allison Hall of Fame Points: 3550 More actions · Answer 6

Is it as simple as doing this: System.IO.File.Move(@"C:\From.txt", @"C:\TO.txt"); ?

Except I need the "TO.txt" file to be YYYYMMDD.csv

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

Brad Allison (9/7/2016)
Is it as simple as doing this: System.IO.File.Move(@"C:\From.txt", @"C:\TO.txt"); ?
Except I need the "TO.txt" file to be YYYYMMDD.csv

Yes, but be aware that the Move method cannot do an overwrite.

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

Brad Allison Hall of Fame Points: 3550 More actions · Answer 8

In this use case we would not need to do an overwrite (not that I am aware of yet). This is a file that will be run once a month, at the beginning of the month, and then FTP over to the requestor's server.

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 9

Koen Verbeeck (9/7/2016)
You could also just write an expression on the flat file connection manager.
example

This is how I normally do it.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Phil Parkin SSC Guru Points: 246960 More actions · Answer 10

Koen Verbeeck (9/7/2016)
Brad Allison (9/7/2016)
Is it as simple as doing this: System.IO.File.Move(@"C:\From.txt", @"C:\TO.txt"); ?
Except I need the "TO.txt" file to be YYYYMMDD.csv
Yes, but be aware that the Move method cannot do an overwrite.

So what happens when the job runs next month?

I suggest adding "using System.IO;" in your Namespaces section, so that you can make your code a bit more snappy (File.Move rather than System.IO.File.Move ...).

If you want to bullet-proof your code a little, add a section to delete the target file if it exists, before doing the File.Move. Something like this:

if (File.Exists(targetFilePath))

{

File.Delete(targetFilePath);

}

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

Brad Allison Hall of Fame Points: 3550 More actions · Answer 11

I guess I don't understand, but each month it runs, it would be a unique file name. So next month it should be something like 20161001.csv. In November it will be 20161101.csv, right? So I shouldn't have that issue of the same filename. I know though that it is better to be safe then sorry

Phil Parkin SSC Guru Points: 246960 More actions · Answer 12

Brad Allison (9/7/2016)
I guess I don't understand, but each month it runs, it would be a unique file name. So next month it should be something like 20161001.csv. In November it will be 20161101.csv, right? So I shouldn't have that issue of the same filename. I know though that it is better to be safe then sorry

Yes of course, my earlier comment was off the mark. But what if the job fails one day and has to be rerun?

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