Renaming A File Source

I've been developing an SSIS package from a flat file source. The file comes daily and the file name has datetime indication like this: Filename_20190509042908.txt I was wonderin

Solution 1:

Remove the time part from the full file path

Assuming that the full file path is stored within a variable named @[User::FilePath]

You have to add a variable of type string (example: @[User::Filename]), Before the data flow task add an Expression Task and use the following expression:

@[User::Filename] =SUBSTRING(@[User::FilePath], 1, LEN(@[User::FilePath]) - 
FINDSTRING(REVERSE(@[User::FilePath]), "\\", 1)) + "\\" +LEFT(TOKEN(@[User::FilePath],"\\",TOKENCOUNT(@[User::FilePath],"\\")),
LEN(TOKEN(@[User::FilePath],"\\",TOKENCOUNT(@[User::FilePath],"\\"))) -10) + ".txt"

Example:

If the value of @[User::FilePath] is

C:\New Folder\1\Filename_20190503001221.txt

Then @[User::Filename] will be:

C:\New Folder\1\Filename_20190503.txt

---

If You have only the file name as

filename_20190503001221.txt

and the folder path is stored in another variable, just use the following expression:

@[User::Filename] = @[User::Folderpath] + "\\" +LEFT(TOKEN(@[User::FilePath],"\\",TOKENCOUNT(@[User::FilePath],"\\")),
LEN(TOKEN(@[User::FilePath],"\\",TOKENCOUNT(@[User::FilePath],"\\"))) -10) + ".txt"

Read File Source from Variable

Click on the flat file connection manager used to read the source file, press F4 to show the properties tab, Click on the expression property and assign the following expression to the connectionstring property:

@[User::Filename]

Now change the Data Flow TaskDelay validation property value to True.

• Dynamic Flat File Connections in SQL Server Integration Services

Solution 2:

I have to assume you are using a foreach loop already as the filename is changing but here is how to change the fully qualified name to what you want:

TOKEN(character_expression, delimiter_string, occurrence)

Your usage:

this will get you the full filename:

exp = TOKEN(@filename,"\",LEN(@filename)-LEN(replace(@filename,"\",""))

then from there you need use left plus add .txt

left(exp,LEN(exp)-10) + ".txt"