Post reply

How to Find/Replace Extra "."s in a Filename

  • November 4, 2014 at 9:21 am

    #397606

    I have an SSIS package that identifies incoming Excel spreadsheets that have been dropped into a series of directories overnight and does various ETL activities on them.

    Long story short is that the client where the SSIS package runs doesn't have any say-so on how these incoming Excel spreadsheets are named. I've incorporated that flexibility regarding the filenames into the SSIS package and however those incoming files are named is usually handled without issue.

    After ETL is done on these Excel spreadsheets, and a standardized formatted Excel spreadsheet is output for each incoming Excel spreadsheet, there's a completely separate process that takes places with the recently-output files that's outside of the SSIS environment. This process is throwing errors if any of the output filenames has multiple "."s in the filename.

    The SSIS process basically takes the incoming filenames and keeps those names the same except an extension of "_RFI" is added-on to the end of the file, as shown here:

    Incoming File: "FileFromClient.xls"

    Output File: "FileFromClient_RFI.xls"

    If the incoming/output files are named as follows, then the process that handles the output file after the SSIS package has executed has issues with the extra "."s in there:

    Incoming File: "File.From.Client.xls"

    Output File: "File.From.Client_RFI.xls"

    Is there an easy command that I can use early-on in the SSIS package to identify if more than one "." is present in a filename, replace those extra "." symbols with nothing, and save the renamed file(s)? I of course want the last "." to remain in place, but if there are one/multiple ADDITIONAL "." symbols in place, I want to programmatically identify/remove those.

    Thanks in advance.

  • November 5, 2014 at 9:19 am

    #1756770

    Not easy but very reusable code to learn is to use C# in script task. You can then define the directory in a package variable. Then in the script task loop through the directory. I have not got my examples to hand but this looks good.

    I know its not easy but when you can do this you can copy / move files to archive, rename them with a timestamp etc

    E

  • November 5, 2014 at 10:19 am

    #1756789

    Many thanks for the response

Viewing 3 posts - 1 through 2 (of 2 total)

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