Renaming a file source

Question

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 wondering how I can pass until the date part; I want the package to read the file dynamically, but it should pass without the last 6 digits I just don't need the last 6 digit numbers as it is not consistent.

I want to pass Filename_20190509.txt

I have figured out how to take the filename until date removing the time part. Hence, I've trouble to let the package read the file name dynamically by ignoring the last 6 digits before file extension.

Can anyone help me with this please?

Answer 1

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"

Answer 2

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 Task Delay validation property value to True.

• Dynamic Flat File Connections in SQL Server Integration Services