archive file which is older than week

• coool_sweet

  SSCommitted

  Points: 1892

  More actions

  June 5, 2017 at 5:06 pm

  #343097

  hi

  i am getting files like file181203 , file170225,basically last part is a date , which i am getting as a file name.

  now i want to archive all files which is older than 1 week, based on checking last part of files, so for example if i am getting parameter
  in my ssis package like 181210. then i need check whatever files are less than 7 days , then i need to archive those file which is 1 week less then given parameter.

  also i need to archive 2 types of files, example file181203.txt, file_data_181203.txt.zip.
  based on input parameter,how to just get date from file name do calculation based on that

  please advise

• Ed Wagner

  SSC Guru

  Points: 287015

  More actions

  June 5, 2017 at 5:38 pm

  #1945506

  I can give you the SQL to translate your filenames into dates.  You may be able to use this in SSIS, but I'm not sure.

  You have different formats for your filenames, so they all have to be handled. 

  Let's approach this using a divide and conquer approach:
  1. The cteData is the data different filenames.
  2. In cteRightEdges, we'll calculate the position of the first . or the end of the string.
  3. The cteFiles takes the 6 characters before the Pos and converts it into a date.
  4. The outer SELECT statement queries the files older than 7 days.

  WITH cteData AS (
SELECT x.name
  FROM (VALUES('file181203'),
      ('file170225'),
      ('file181203.txt'),
      ('file_data_181203.txt.zip')) x (name)
),
cteRightEdges AS (
SELECT name, Pos = ISNULL(NULLIF(CHARINDEX('.', name), 0), LEN(name) + 1)
  FROM cteData
),
cteFiles AS (
SELECT name, pos, numeric_date = SUBSTRING(name, pos - 6, 6),
  FileDate = CONVERT(Date, SUBSTRING(name, pos - 6, 6))
  FROM cteRightEdges
)
SELECT name, FileDate
FROM cteFiles
WHERE FileDate < DATEADD(day, -7, GETDATE())
ORDER BY FileDate;


  I hope this can be of some help in SSIS.

  Tally Tables - Performance Personified
  String Splitting with True Performance
  Best practices on how to ask questions

• Jeff Moden

  SSC Guru

  Points: 1000539

  More actions

  June 5, 2017 at 5:51 pm

  #1945508

  If the file named file_data_181203.txt.zip contains the file named file181203.txt, then it's pound foolish to copy the txt file along with the zip file.  Gross duplication of data.  Just drop the txt file into the bit bucket after you've imported it.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
  "Change is inevitable... change for the better is not".
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  Intro to Tally Tables and Functions