4

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?

Hadi
  • 36,233
  • 13
  • 65
  • 124
lije
  • 420
  • 2
  • 15
  • What do you mean how can you pass until the date part? Pass what, to where? – Thom A May 09 '19 at 16:05
  • Do you mean you want to remove the last 6 characters from the file name before the extension? If so, with what, an SSIS expression? – Thom A May 09 '19 at 16:07
  • Edited the question. I want the package to read the file dynamically. But to do so, it should be without the last 6 digits. – lije May 09 '19 at 16:09
  • Use a Foreach Loop Container with a wildcard? – Thom A May 09 '19 at 16:12
  • @Larnu I know 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. – lije May 09 '19 at 17:44

2 Answers2

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

Hadi
  • 36,233
  • 13
  • 65
  • 124
0

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"
KeithL
  • 5,348
  • 3
  • 19
  • 25
  • thanks though. 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. – lije May 09 '19 at 17:46