SSIS : Move files over 3 months old to another folder

kkran 831 Reputation points
2021-02-07T22:39:51.37+00:00

Hi Everyone :
I already have an SSIS package with user: src_file_path variable with value E:\ETL\Data Load\Act_DrugTool\Part1\DEV\Outgoing\
65061-sourcepath-variable.jpg

In the above path there will be files in the below format excel files :
65053-files-in-source.jpg

I want to create another variable called user: archive_file_path and move the files from Source variable file path if the file is more than 3 months old.

I want to read the date from the file name in the source variable path and if the file is more than 3 months old move to the archive path below : Archive : \Sdcpifil011\etl\Legacy_ETL_Data_Loads\ETL_SSIS_ACTDrugTool\Part1\PROD\Archive

Will it be a script task or file system task ? Thanks in advance for your help.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,851 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,462 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,559 questions
0 comments No comments
{count} votes

Accepted answer
  1. Monalv-MSFT 5,896 Reputation points
    2021-02-08T03:37:12.41+00:00

    Hi @kkran ,

    We can use Foreach Loop Container to find the files, then use empty Sequence Container, Precedence Constraint, File System Task to move the files to archive folder.

    Please refer to the following pictures:
    65108-cf.png

    65127-variables.png

    65109-flc-collection.png65075-flc-variablemappings.png

    65076-precedenceconstraint.png65096-expression3.png

    DATEDIFF( "mm", (DT_DBDATE) (SUBSTRING( RIGHT( TOKEN( TOKEN( @[User::FileName] , ".", 2 ), "_", 1 ) , 8 ) , 1, 4)+"-"+  
    SUBSTRING( RIGHT( TOKEN( TOKEN( @[User::FileName] , ".", 2 ), "_", 1 ) , 8 ) , 5, 2)+"-"+  
    SUBSTRING( RIGHT( TOKEN( TOKEN( @[User::FileName] , ".", 2 ), "_", 1 ) , 8 ) , 7, 2)), GETDATE())>3  
    

    Best Regards,
    Mona

    ----------

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Yitzhak Khabinsky 25,201 Reputation points
    2021-02-08T00:41:41.25+00:00

    Will it be a script task or file system task ? Thanks in advance for your help.

    SSIS File System Task doesn't have functionality sufficient enough for your needs.
    So, it should be SSIS Script Task.

    0 comments No comments