0

Remove the circled part of the filename

There are two types of files in folder x:\abc\sales\ one is a compressed .gz file and the other is uncompressed .txt file My aim is to rename all the ".txt" files:(leaving the .gz files untouched) Eg. "accn_adjustments_201802020147.txt"

The part after the underscore is basically date and time, the time is not always "0147", it could be "0148" or "0210" or anything else, but always 4 digits... My task is to remove the last 4 digits so the end result would look something like "accn_adjustments_20180202.txt"

I would think either for-each loop or script task can help me solve this issue but I don't know how to go about it.

Any help is appreciated, thanks in advance...

Dhananjay Rele
  • 13
  • 1
  • 2
  • 7

1 Answers1

1

Step 1) Add a Foreach Loop Container to the package (Drag Foreach Loop Container from the Containers in the SSIS Toolbox on to the package work area).

Step 2) Double click the Foreach Loop to open the Editor

Step 3) Click Collection on the left menu, then change the Enumerator property to Foreach File Enumerator

Step 4) Choose your Folder: x:\abc\sales\

Step 5) Change the property of Files: to *.txt

Additionally you will need:

Step 6) Drag the File System Task from the toolbox into the Foreach Loop Container

Step 7) Double click the File System Task and change the Operation to Rename File

Step 8) Set the source and target connection on the task (I assume you know how to create those).

Now the container will only loop through text files in the folder.

This can be extended for your purposes by using Variables, the Variable Mappings section of the Foreach Loop and Expressions to dynamically modify the file names and change the connection property of the text file data source. Although I'm not 100% clear exactly what else you need, this should at least answer this question to get you started.

Some additional reading to help with the renaming process:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/6c6e4fba-be40-4c53-ab8e-348ccd39c687/rename-file-using-file-system-task-editor?forum=sqlintegrationservices

Jacob H
  • 2,455
  • 1
  • 12
  • 29
  • 1
    Make two sources. And use a variable to change the connection property of the second one (the "destination"). The link I posted has a good explanation. – Jacob H Feb 02 '18 at 20:15
  • Hey Jacob, I had a ForeachLoop already but it wouldnt work because I was using the same variable in source and destination. This was causing the variable to lock error out. I was using var(UnzFile) as source and REVERSE(REPLACE(REVERSE( var(UnzFile)), LEFT(REVERSE( var(UnzFile) ), 8), "txt.")) as destination Instead I just created a new variable var(UnzFileDateRemoved) and put the formula as REVERSE(REPLACE(REVERSE( var(UnzFile) ), LEFT(REVERSE( var(UnzFile) ), 8), "txt.")) Then used var(UnzFile) as Source and var(UnzFileDateRemoved) as Destination variable – Dhananjay Rele Feb 02 '18 at 20:18
  • Nice! Glad you got it sorted out :) – Jacob H Feb 02 '18 at 20:20