SSIS - How To Create Folder with Date in SSIS Package


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.


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.

Video for this post

No comments:

Post a Comment