Post reply

Rename file in SSIS package when the file name is constantly changing.

  • December 9, 2021 at 8:23 pm

    #3962164

    Hi,

    I am trying to rename a file that is currently in a network share location and the file name is not the same all of the time so I can't use the standard rename file system task.

    When the file is run the first, let's say 10 characters remain the same, example: abcdefghij_20211209_1111.csv, I need to rename the file to be just abcdefghij.csv no matter what the date/time stamp is at the end of the file. I will be moving the file to a new network share location for processing after the name has been completed.

    Any help with this would be appreciated.

    Thanks.

  • December 9, 2021 at 9:49 pm

    #3962182

    So the issue is that you don't know the filename?

    If you can loop through the directory then you can put the full path of each file in a variable (on each loop).

    You can then inspect the filename, change it using an expression task then use the File System task to do the rename via the move operation using variables as the from and to.

    Not knowing the name ahead of time doesn't impact the use of the File System Task unless you're using a connection manager but even then it doesn't matter because you can have a dynamic conn string using an expression for the connection.

    In fact its sort of a policy for us that every file connection uses an expression when a config may or may not have a backslash at the end of a directory path.

    Does that help at all?

  • December 9, 2021 at 10:15 pm

    #3962184

    TangoVictor,

    Thanks for the help, do you happen to have an example where this has been done, I can't find clear example of how to setup the process up? I do know the beginning of the file name and the file type but it is the date/time stamp that is causing me the problem.

  • December 9, 2021 at 10:56 pm

    #3962189

    Attached a PDF with an example. Hope this helps.

    Ok apparently PDFs are not allowed. Here's some screenshots instead.

     

    Attachments:
    You must be logged in to view attached files.

  • December 9, 2021 at 11:10 pm

    #3962205

    I've also attached the package, just download and rename to something.dtsx

    I'm using 2019 hopefully that works.

    Attachments:
    You must be logged in to view attached files.

  • December 9, 2021 at 11:20 pm

    #3962207

    Last thing to note, this is just one possible method, there are other ways. One caveat of this method is having to loop through an entire directory that could contain thousands of files.

    I just thought of something as well, you can setup a filename mask in the foreach loop. The part that has *.* I think you can probably use some expression in there, maybe regex I'm not sure offhand, but at a minimum you can use *.csv to limit the list.

    cheers.

  • December 10, 2021 at 11:09 am

    #3962340

    As TangoVictor mentions, there are other possibilities.

    One is to keep the filename exactly as it is and instead modify your 'subsequent processing' tasks to use a variable file name. This has the benefit (assuming that you archive the file somewhere at the end of your process) of allowing easy identification of original files, if that is ever required.

    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

  • December 10, 2021 at 3:53 pm

    #3962428

    Tango, SSChampion, and Phil, thank you so much for the help on this and I learned something new. Hope you have a good weekend.

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

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