Sample package written in SSIS 2012 using C # and VB.NET
Here is an example package written in SSIS 2012 that does what you are looking for using a script task. You do not need to use SSIS. You can even do this with a simple C # or VB.NET console application, but SSIS provides the flexibility to log information and schedule tasks.
Folder Structure (Initial)
Assume the folders are structured as shown below:
There is a source file that you want to copy.
Source |- Sample_File.txt
Here is the structure of the destination folder.
Target |- Target_1 | |- Archive | |- Sample_File.txt |- Target_2 |- Target_3 |- Sample_File.txt
Create an SSIS package and create variable folders:
Variable name Data type Value ------------------ ---------- ---------------------- Backup_FolderName String Archive Source_FileName String Sample_File.txt Source_FilePath String Source_Folder String D:\SSIS\Files\Source\ Target_Folder String D:\SSIS\Files\Target\
Select the Source_FilePath variable and press F4 to view the properties. Change the EvaluateAsExpression property to true. Click the ellipsis button next to the Expression property to open the expression builder. Set the expression to @[User::Source_Folder] + "\\" + @[User::Source_FileName] .
You can have only one variable to store the path to the source file. Usually I prefer to keep the source folder and file name separately.
Drag the script task to the control flow tab. Double-click the script task to open the script task editor. In the script tab, click the ellipsis button next to ReadOnlyVariables and select the following variables, because we will use these variables in the script task code.
User::Source_FilePath User::Target_Folder User::Backup_FolderName
Click the "Edit" Script ... button and enter the code as shown below.
Script Task code in C # only for SSIS 2008 and higher:
The script task code does the following:
It checks if the path of the source file is correct or not. If it is invalid, it issues a message and terminates the process.
It checks if the destination folder is valid or not. If it is invalid, it issues a message and terminates the process.
If the source file path and destination folder are valid, the logic will go through all the corresponding locations of the source file name in subfolders in the destination folder. If there are corresponding files, it will copy the target file to the backup folder and then overwrite the target file with the source file.
The script task will provide the relevant information so that you can track the status on the run / run results tab in SQL Server Data Tools (SSDT) in SSIS 2012 or Business Intelligence Development Studio (BIDS) in SSIS 2005 - SSIS 2008 R2.
Namespace area
using System; using System.Data; using Microsoft.SqlServer.Dts.Runtime; using System.Windows.Forms; using System.IO;
endregion
namespace ST_523853dfbc0d4123be43383671f8a6c6 {[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute] open partial ScriptMain class: Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectgainBaseMainBoolModelain boldModelourceBaseModelain string backupFolder = string.Empty; string backupFilePath = string.Empty;
string sourcFilePath = Dts.Variables["User::Source_FilePath"].Value.ToString(); string targetFolder = Dts.Variables["User::Target_Folder"].Value.ToString(); string backupFolderName = Dts.Variables["User::Backup_FolderName"].Value.ToString(); if (String.IsNullOrEmpty(sourcFilePath) || !File.Exists(sourcFilePath)) { // Check if a valid source file path was specified on the package variable Dts.Events.FireError(101, "Source path error", String.Format("You need to set a valid source file path in the package variable 'Source_FilePath'. Invalid path: '{0}'", sourcFilePath), string.Empty, 0); Dts.TaskResult = (int)ScriptResults.Failure; } else if (String.IsNullOrEmpty(targetFolder) || !Directory.Exists(targetFolder)) { // Check if a valid target folder was specified on the package variable Dts.Events.FireError(102, "Target folder error", String.Format("You need to set a valid target folder location in the package variable 'Target_Folder'. Invalid folder: '{0}'", targetFolder), string.Empty, 0); Dts.TaskResult = (int)ScriptResults.Failure; } else { FileInfo sourceInfo = new FileInfo(sourcFilePath); // Loop through each file that matches the name of the source file foreach (string targetFilePath in Directory.EnumerateFiles(targetFolder, sourceInfo.Name, SearchOption.AllDirectories)) { FileInfo targetInfo = new FileInfo(targetFilePath); backupFolder = Path.Combine(targetInfo.Directory.FullName, backupFolderName); backupFilePath = Path.Combine(backupFolder, backupFolderName); // If the backup folder does not exist in the folder within root target folder, create the backup folder. if (!Directory.Exists(backupFolder)) { Directory.CreateDirectory(backupFolder); Dts.Events.FireInformation(401, "Backup folder created", String.Format("Backup folder '{0}' was created.", backupFolder), string.Empty, 0, ref fireAgain); } // Archive the target file to the backup folder. File.Copy(targetFilePath, backupFilePath, true); Dts.Events.FireInformation(402, "Target file archived", String.Format("Target file '{0}' was archived to the backup folder '{1}'.", targetFilePath, backupFolder), string.Empty, 0, ref fireAgain); // Overwrite the target file with the source file. File.Copy(sourcFilePath, targetFilePath, true); Dts.Events.FireInformation(403, "Target file overwritten", String.Format("Target file '{0}' was overwritten with the source file '{1}'.", sourcFilePath, targetFilePath), string.Empty, 0, ref fireAgain); } Dts.TaskResult = (int)ScriptResults.Success; } } catch (Exception ex) { Dts.Events.FireError(100, "Unhandled exception", ex.ToString(), string.Empty, 0); } }
}
Script Task code in VB.NET for SSIS 2005 and higher:
#Region "Imports" Imports System Imports System.Data Imports System.Math Imports System.IO Imports Microsoft.SqlServer.Dts.Runtime
If an invalid path to the source file is specified, the package displays the following error message:
If an invalid destination folder is specified, the package displays the following error message:
When the source and destination locations are valid, the package will succeed. In this example
Target_1 was a backup folder in the Target_1 folder, so the folder was not created, but the file was copied to the backup folder.- The corresponding file could not be found in
Target_2 , so there was no action. - A backup folder was created in
Target_3 , the file was copied to the destination location and then overwritten with the original file.
Folder Structure (Final)
The destination location will look as shown below after the package is executed.
Target |- Target_1 | |- Archive | |- Sample_File.txt | |- Sample_File.txt |- Target_2 |- Target_3 |- Archive |- Sample_File.txt |- Sample_File.txt