Creating a Dynamically Named Folder Structure with SSIS
There may be instances where you need to create a folder at runtime for backup or archiving purposes. I frequently see the requirement where the archive folder needs to be stored by year and the current date, for example, C:/Archive/2021/012621. I will demonstrate how to create the folder using SSIS. In a subsequent post, I will demonstrate how to populate the archive folder with a file.
As a matter of practice, I create a script task to verify a file exists before taking action on the file. Creating a file existence task is covered in a previous post.
Step 1: Create File System Task
Drag a File System task from the toolbox onto the SSIS Canvas.
Step 2: Connect the File System Task (if needed)
You will need to connect the File System task using a precedence constraint if you have prior tasks within the workflow. Otherwise, you can skip this step.
Step 3: Create and Populate the Archive Folder Variable
Create a variable for the archive folder. In this example, the name of the variable is archiveFolder. The three variables fileExistsFlag, file Name, and filePath were used in a prior posting demonstrating how to verify a file's existence. You can omit them for this exercise.
Populate the variable value using an expression. Click on the ellipses to enter the expression that will generate the file name.
Enter the following code into the expression builder. This code will provide the structure and name of the archive folder, i.e. C:\Archive\Year\DDMMYY\.(Text version of code can be found just below the image)
In this example, I am demonstrating creating the archive on your local C: drive. The text for the expression is as follows:
Local Archive
"C:\\Archive\\"+(DT_WSTR,4)YEAR(GETDATE())+"\\"+(LEN((DT_STR,2,1252)(DATEPART("MM",GETDATE()))) == 2 ? (DT_STR,2,1252)(DATEPART("MM",GETDATE())) : "0" + (DT_STR,2,1252)(DATEPART("MM",GETDATE()))) +(LEN((DT_STR,2,1252)(DATEPART("dd",GETDATE()))) == 2 ? (DT_STR,2,1252)(DATEPART("dd",GETDATE())) : "0" + (DT_STR,2,1252)(DATEPART("dd",GETDATE()))) +(DT_STR,4,1252)(DATEPART("yyyy",GETDATE())%100)+"\\"
However, you can set up your variables to create archive folders on servers within your network. You will need permissions to the server. Another option is establishing a connection to the server, via an execute process task. The credentials you use should have access the server folder you are attempting to access.
Network Archive
- Replace “Server” and “Path” with the resource and path you require.
"\\\\SERVER\\PATH\\Archive\\"+(DT_WSTR,4)YEAR(GETDATE())+"\\"+(LEN((DT_STR,2,1252)(DATEPART("MM",GETDATE()))) == 2 ? (DT_STR,2,1252)(DATEPART("MM",GETDATE())) : "0" + (DT_STR,2,1252)(DATEPART("MM",GETDATE()))) +(LEN((DT_STR,2,1252)(DATEPART("dd",GETDATE()))) == 2 ? (DT_STR,2,1252)(DATEPART("dd",GETDATE())) : "0" + (DT_STR,2,1252)(DATEPART("dd",GETDATE()))) +(DT_STR,4,1252)(DATEPART("yyyy",GETDATE())%100)+"\\"
Click on the Evaluate Expression button. The value of the variable shouldbe: "C:\Archive\Year\DDMMYY\".
Click on the "OK" button. The variable is now populated by the expression.
Step 4: Configure File System Task
Double click on the file system task. This action will produce the File System Task Editor window.
Update the Operation option so that “Create Directory” is the selected value. This selection instructs SSIS to create the folder.
Update the UseDirectoryIfExists option so that “True” is the selected value. This instructs SSIS to use the folder if the folder name already exists. In short, do not recreate the folder if it already exists.
Update the Name value for the File System Task. We will be entering FST-Create Archive Folder for the name for this task.
Update the description field and provide a brief explanation of the task. This is not required, but it has proven to be helpful for identifying the purpose of the task. For example: “Create archive folder". Path C:\Archive\YYYY\MMDDYY\”
Update the IsSourcePathVariable value so that “True” is the selected value. This instructs SSIS to use a variable to define the path and name of the folder.
Update the SourceVariable value so that the variable defining the path and folder name is selected. In this example, the variable is “User::archiveFolder”. The user prefix informs us that this is a user defined variable and not a system variable.
Click the “OK” button on the editor window. This will close out the task. You have completed the configuration of the File System Task.
Step 5: Test the Package
We will be executing the task we just created. Note, that the archive folder does not currently exist in C:\. Execute the create directory task. If it works as expected, you should see a green check mark upon completion.
Inspect C:\ to see if the folder structure was created as expected.
As you can see the folder structure was created as expected: YYYY/MMDDYY. I will demonstrate how to populate this folder in a subsequent post.