How to create a dynamic folder using SSIS?

8
2738

Aim :-  Agenda of this article will be to create a dynamic folder followed with current day name. In simple words, the situation demands to create a dynamic folder on daily basis with the desired name say RX followed by current day name.

Description :-  I am sure your white cells must be stimulated and searching for the answer. Have you ever heard of File System Task in SSIS? Yes, you are thinking in the right direction. With use of File System Task in SSIS, we will create a dynamic folder followed with the name of current day. This is a simple requirement but pretty interesting thing.

File System Task says – I am used to configure the properties required to perform file system operations such as Creating, Moving, or Deleting files/directories.

In our Question Laboratory (Q Lab), we always stress more on practical explanation rather than making you bore by theoretical concepts. This article will show you a simple way to create a dynamic folder followed with current day name. Let’s configure things to approach our requirement.

Steps to create dynamic folder using SSIS

Step 1 :  Create a SSIS Package

  • Open BIDS (Business Intelligence Development Studio – 2008R2 and below versions) or SSDT (SQL Server Data Tool – 2012 and above versions).
  • Create a new SSIS project. Now, create a new package (.dtsx extension) and give it a desired name say “Folder”.

Step 2 :  Create a Variable in SSIS

  • Drag and drop “File System Task” to the control flow pane.

Create folder followed with current day name in SSIS

  • Create one variable and name it as “Filepath”. Give the path as below screenshot i.e. – C:\Users\am1000\Desktop\RX Reports\RX
Varaible creation in SSIS
Setting up Varaible in SSIS

NOTE I have created a folder called RX Reports on my desktop. Within that folder (RX Reports), I want to create a new folder on daily basis with the name RX then followed by today name.

Step 3 :  Configure File System task in SSIS

  • It’s time to configure SSIS File System Task. Double click on “File System Task”.
  • Once you double click on File System task, below wizard will pop up

Configure File System Task in SSIS

  • In the File System Task Editor, choose operation as “Create Directory”.
  • Next, Inside Source connection –
  • (a).  Set “IsSourcePathVariable” property to “True”.
  • (b).  Go to Source Variable and choose the created variable (Step 2) from drop down list i.e. “User::Filepath”.
  • Finally to end with this File System Task configuration, click on OK button.

Step 4 :  Configure Variable (Filepath) properties

  • Select created variable “Filepath” (Step 2) and press F4 for the properties of variable. Once you come up with above step the below window will pop up on right hand side.

Configure variable properties in SSIS

  • Set EvaluateAsExpression to “True”.
  • For Expression, click on Ellipse button (…). Once you click on Ellipse button the below window will come up where we will build our expression.

Expression Builder window in SSIS

  • Below is the expression which I have used in the Expression editor. Copy and paste below expression in Expression Editor.

Expression

“C:\\Users\\am1000\\Desktop\\RX Reports\\RX”+”_”+(DATEPART(“dw”, GETDATE() ) == 1 ? “Sunday” : (DATEPART(“dw”, GETDATE() ) == 2 ? “Monday” : (DATEPART(“dw”, GETDATE() ) == 3 ? “Tuesday” : (DATEPART(“dw”, GETDATE() ) == 4 ? “Wednesday” : (DATEPART(“dw”, GETDATE() ) == 5 ? “Thursday” : (DATEPART(“dw”, GETDATE() ) == 6 ? “Friday” : “Saturday”))))) )

NOTE To check whether this expression is “True or False”, you can click on Evaluate Expression. If the expression is true, then only it will give the output like “C:\Users\am1000\Desktop\RX Reports\RX_Thursday”. For this instance, you can see that, we got a day name i.e. “Thursday” (Current Day name) at the end.

  • We are done with variable configuration step, Click on OK button.

Expression builder in SSIS for dynamic folder name

Step 5 : SSIS Package execution

  • Now to validate our package configuration, we can execute the package. I am confident that we will achieve desired results.
  • Below is the screen shot of RX folder before package execution? As you can see, we do not have any folders yet. The RX Reports folder is completely empty.

Empty folder before SSIS package execution

  • Now, I am executing our SSIS package and then will analyze the results.

File System Task in SSIS

Analysis –  By observing above pic we can concluded that our package is executed successfully without any issue.

  • It’s time to go and see whether the folder is dynamically created followed with today day name or not. Today is 19/06/2014 which is “Thursday”. Now, let’s see the dynamic folder is created or not with the same day name I.e. “Thursday”.

SSIS package output for dynamic folder name

  • Yikes, we nailed it !!! The package is executed successfully and the folder is also dynamically created successfully with today name.

Step 6 :  Testing and Fun

  • Now, I am going to change my date to 20/06/2014. So it’s Friday. Again, I am going to execute my package and will see what is going to happen at output folder.

Change system current date and time

  • I hit f5 key to execute our SSIS package and In the below picture we can clearly observe that the folder has been created with extension name (“Friday”). Awesome !!!

Dynamic folder name in SSIS

With this we finish up with this fun session on how to create dynamic folder followed by current day name using SSIS. We at Question laboratory ( Q Lab) will try to bring up more scenarios. If you enjoyed our post or have questions on which you need to have practical explanation, kindly post in comments below or on our facebook page. We would love to hear feedback from you.

VN:F [1.9.22_1171]
Your Feedback hels to Improve
Rating: 9.8/10 (9 votes cast)
VN:F [1.9.22_1171]
Rating: +6 (from 6 votes)
How to create a dynamic folder using SSIS?, 9.8 out of 10 based on 9 ratings
  • Aditya

    Thank you for clear explanation Avinash 😀

  • jeevan

    Hi Avinash,

    Very useful Article. Superb.

    Regards,
    Jeevan