Making Dynamic Backup Folders In SSIS

To make dynamic backup folders from SSIS you need to do is to follow the steps.
Over all View


I am sure that you can find other ways to do this and maybe much simpler, but the concept of the blog is to give you the idea

First step is to define few variables in the system


1. “uVar_BackupActiveFolder” when the package runs it points to a new folder each time which is the active backup folder and that is where we keep the backup and error files for that transaction for that ETL run.
2. “uVar_BackupMainFolderPath” some customers prefer to have the backup folder in another server so basically we allow them to have a choice, by adding this variable (See the script section for more details).
3. “uVar_MainFolderPath” This is where your main Folder path is basically it’s the main root of your package and its other folders (e.g. backup, Error folder, sample folder, etc...).Once adding the objects in the control flow...

First ---


Second – go to the Design Scripts and add the script setting


As you can see, the IF statement checks that if the variable is not empty, change the backup folder from the MainFolderPath to the New BackupMainFolderPath, this is when the customer wants to change the backup folder from the main root of the ETL to somewhere else. Check the Testing Scenarios.

Next step the FSYS


Testing Scenarios
Once finishing the setting run the package, you will see....

Assuming that the ETL is been running for months and you customer decided to change the backup folder, you won’t need to change anything in the package just simply add the new backup path (e.g. C:\NewBackUp) to the “uVar_BackupMainFolderPath” Variable and run the package.


In this case remember to add the package or ETL name in the variable
e.g. C:\NewBackup\MyETLname