SSIS - How To Create Folder with Date in SSIS Package

SSIS - How To Create Folder with Date in SSIS Package

Scenario:

We load data from different type of files on daily basis. We want to archive files after load. To manage processed files by date, we need to create a folder with Date every day and move the files to it once processed.

Solution:

We will be creating the Folder with Date if not exists. We will be using File System Task and expressions to achieve this.

Step 1:

Create your new SSIS Package and then create three variables as shown below.

Fig 1: Create Variables for Archive Folder

I created the variables so you can change the value of these according to the environments in which your package is going to run by using SSIS Configuration.

VarFolderPath: It is path to the main folder where we want to create Archive Folder.

VarFolderName : This will hold the value of folder name that we want to create.

VarFolderFullPath: This will combine Folder path, folder name and date added to the end. We will write expression on this variable as shown below.

Step 2: 

Select VarFolderFullPath and then hit F4 to go to properties of variable.

Fig 2: Write Expression on Variable in SSIS Package

Set EvaluateAsExpression=True and then Click on the button right front of Expression. Under Expression , write the below expressions as shown below

@[User::VarFolderPath]+ @[User::VarFolderName]+"_"+REPLACE(SUBSTRING((DT_STR,50,1252)GETDATE(),1,10),"-","_")

Fig 3: Build Expression in SSIS Package for Folder with Date

Step 3: 

Bring File System Task to the Control Flow Pane and then configure as shown below.

Fig 4: Create Directory With Date By Using File System Task In SSIS Package

Let's run our SSIS Package and see if the directory is created in required folder.

Fig 5: Folder Created with Date by Using SSIS Package

Now you have the folder ready to move your processed files to it.