Parse the file name in expression

• christi1711

  SSC-Addicted

  Points: 441

  More actions

  May 26, 2022 at 12:17 pm

  Go to Answer

  #4039612

  Hi all,

  I have SSIS Package in which we uploads files and fetch date based on the file name.

  The file name was ImportFile_XXX_20220501.

  We used such expression (DT_STR,8,1252)SUBSTRING(FILE_NAME,FINDSTRING(FILE_NAME,"_",3) + 1,8) to receive int date.

  Currently, the file name was changed to  the ImportFile_UK_DumpXX_20220501.

  Also, I have one variable in which I pass some specific parameter - FileCode. And I need to parse the new file name only when FileCode =1 .

  Could you please help me to change the existing expression?

  Thank you.

  • This topic was modified 2 years, 10 months ago by  christi1711.

• Phil Parkin

  SSC Guru

  Points: 247125

  More actions

  May 26, 2022 at 12:41 pm

  #4039621

  If the file name is "ImportFile_UK_DumpXX_20220501", what exactly do you want the expression to return?

  If FileCode <> 1, what do you want the expression to return?

  ---

• christi1711

  SSC-Addicted

  Points: 441

  More actions

  May 26, 2022 at 1:16 pm

  #4039626

  Hi Phil,

  I need to return this number 20220501 if FileCode <>1.

  Also I forgot  indicated the correct file name.

  It should be ImportFile_UK_DumpXX_20220501_XX.

• Phil Parkin

  SSC Guru

  Points: 247125

  More actions

  May 26, 2022 at 2:05 pm

  Answer

  #4039675

  The following expression returns 20220501 if FileCode = 1, and an empty string otherwise.

  @[User::FileCode]==1?token(@[User::FileName], "_", 4) : ""

  ---

• christi1711

  SSC-Addicted

  Points: 441

  More actions

  May 26, 2022 at 3:27 pm

  #4039717

  Phil,

  it works great but without FileCode.

  File code is string value, so I wrote @[User::FileCode]=='1'. Destination column is int.

  I received an error that it can't convert unicode to non-unicode...

• christi1711

  SSC-Addicted

  Points: 441

  More actions

  May 26, 2022 at 3:52 pm

  #4039726

  Phil,

  I have already fixed it.

  Thanks for your help!