Changing destination csv filename

  • Brad Allison

    Hall of Fame

    Points: 3473

    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.

  • Phil Parkin

    SSC Guru

    Points: 243688

    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.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • Brad Allison

    Hall of Fame

    Points: 3473

    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: 243688

    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.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • Koen Verbeeck

    SSC Guru

    Points: 258941

    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: 3473

    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: 3473

    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: 258941

    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: 3473

    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

    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: 243688

    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);

    }

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • Brad Allison

    Hall of Fame

    Points: 3473

    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: 243688

    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?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

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

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