Create FTP Task to receive file with dynamic name

• lklein 83808

  Grasshopper

  Points: 23

  More actions

  February 20, 2012 at 11:37 am

  #255939

  I am trying to create a SSIS package to receive a file from an FTP source with a dynamic filename and load into a sql table. I am having problems with the expression builder for the dynamic filename. The filename should look like this:

  TDBANKNORTH_GARDEN_671_20111202.txt

  With 20111202 being the dynamic part. It should be set to yesterdays date in that format (yyyymmdd).

  Can anyone assist with the property and expression?

  Thanks in advance for any help.

• rlswisher

  SSC-Addicted

  Points: 439

  More actions

  February 23, 2012 at 3:29 pm

  #1451135

  I'm assuming you already have your variables set up.

  @[User::SourceFileName] is equal to "TDBANKNORTH_GARDEN_671.txt"

  Create - @[User::RunDate] Where it is set to GETDATE()-1

  Create - @[User::DestFileName]

  Then you want the @[User::DestFileName] variable expression to read like so:

  REPLACE(@[User::SourceFileName],".txt","_") +

  LEFT(@[User::RunDate],4) + SUBSTRING(@[User::RunDate],6,2)

  + SUBSTRING(@[User::RunDate],9,2)

  + ".txt"

  This should give you : TDBANKNORTH_GARDEN_671_20111202.txt

  I may have the substring order off, but you can play with that.