February 19, 2013 at 8:48 am
I am working on windows server 2003.
I have a file 'target.file' at local. There is a 'target' folder on network server. There are several hundred folders under 'target'. There are 'backup' folders under some of those folders. Now I need to copy 'target.file' to those folders under the 'target' folder. But only copy/replace if there is already a 'target.file' existing. If the 'target.file' exists, copy and replace the file to the backup folder if the backup folder exists. If not, create the backup folder first.
I am not sure how to achieve this in SSIS 2005. I know there is a file system task but never used before or maybe a script task in SSIS? I haven't done any scripting in SSIS before. SSIS is not my major task in this company, but I would like to learn. But I don't have time to learn the language now. So a simple guide will be beneficial. Thanks.
February 20, 2013 at 10:53 am
Are you open to using PowerShell? This could be done in a single line.
Run this at a PowerShell prompt and you'll see how simple it is to identify which files you'll be to overwriting:
Get-ChildItem -Path "\\servername\sharename\target" -Recurse -Filter "target.file" | Select-Object FullName
From there, it's just one more command in the pipeline to copy your local target.file to overwrite the existing ones.
If target.file might exist in directories not named backup and you want to leave those alone you may need one additional command in the pipeline to filter those out.
edit: fixing quoted text, forum ate a backslash n.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 20, 2013 at 1:00 pm
Thanks for the reply. I was open to any solution actually. I heard about power shell but never tried before. Anyway I already solved it using a script task in SSIS.
February 20, 2013 at 1:16 pm
Good deal. Happy you found a way forward. If you don't mind posting your .NET code others may benefit from it down the line. I am also curious to see how much code was required in the Script Task to do the same.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 20, 2013 at 2:07 pm
Sure, I will post it tomorrow when I go to work. But it is a lot of code:-D
February 21, 2013 at 3:11 am
The code for the script task.
The script task has three variables as read only parameters:
sourceFile: name of the file. E.g: target.txt
sourcePath: e.g. c:\workfolder
targetPath: e.g. \\server\targetFolder
bankupFolder: e.g backup
The VB code
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.IO
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
Dim sourceFile As String = Path.Combine(Dts.Variables("sourcePath").Value.ToString(), Dts.Variables("sourceFile").Value.ToString())
'MsgBox(sourcePathFile)
Dim targetPath As String = Dts.Variables("targetPath").Value.ToString()
If String.IsNullOrEmpty(sourceFile) OrElse Not File.Exists(sourceFile) Then
' 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}'", sourceFile), String.Empty, 0)
Dts.TaskResult = Dts.Results.Failure
ElseIf String.IsNullOrEmpty(targetPath) OrElse Not Directory.Exists(targetPath) Then
' Check if a valid target path 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}'", targetPath), String.Empty, 0)
Dts.TaskResult = Dts.Results.Failure
Else
Process(sourceFile, targetPath)
'get all sub folders under target
'for each folder under target,
'if exists source file
'if exists backup folder, replace/copy source file to backup folder
'else, create backup folder first, then replace/copy source file to backup folder
'replace source file
Dim subdirectoryEntries As String() = Directory.GetDirectories(targetPath)
Dim subdirectory As String
For Each subdirectory In subdirectoryEntries
'MsgBox(subdirectory.ToString)
Dim sd As FileInfo = New FileInfo(subdirectory)
'do not process folder named 'backup'
If sd.Name = Dts.Variables("bankupFolder").Value.ToString() Then
Continue For
End If
Process(sourceFile, subdirectory.ToString)
Next subdirectory
Dts.TaskResult = Dts.Results.Success
End If
End Sub
'if exists source file under target path
'if exists backup folder, replace/copy source file to backup folder
'else, create backup folder first, then replace/copy source file to backup folder
'replace source file under target path.
Public Sub Process(ByVal sourceFile As String, ByVal targetPath As String)
Dim targetFile As String = Path.Combine(targetPath, Dts.Variables("sourceFile").Value.ToString())
'if source file is target file, don't process
If sourceFile = targetFile Then
Return
End If
If File.Exists(targetFile) Then
Dim targetBankupFolder As String = Path.Combine(targetPath, Dts.Variables("bankupFolder").Value.ToString())
' MsgBox(targetBankupPath)
' If the backup folder does not exist in the folder specifed, create the backup folder.
If Not Directory.Exists(targetBankupFolder) Then
'MsgBox("bankup folder NOT exists!")
Directory.CreateDirectory(targetBankupFolder)
Dts.Events.FireInformation(401, "Backup folder created", String.Format("Backup folder '{0}' was created.", targetBankupFolder), String.Empty, 0, False)
End If
Dim targetBankupFile As String = Path.Combine(targetBankupFolder, Dts.Variables("sourceFile").Value.ToString())
'if file already exists in backup folder, rename the file with timestamp
If File.Exists(targetBankupFile) Then
FileSystem.Rename(targetBankupFile, targetBankupFile & DateTime.Now.ToString("yyyyMMddHHmmss"))
End If
' Archive the existing file to the backup folder.
File.Copy(targetFile, targetBankupFile, True)
Dts.Events.FireInformation(402, "Target file archived", String.Format("Target file '{0}' was archived to the backup folder '{1}'.", targetFile, targetBankupFolder), String.Empty, 0, False)
' Overwrite the target file with the source file.
File.Copy(sourceFile, targetFile, True)
Dts.Events.FireInformation(403, "Target file overwritten", String.Format("Target file '{0}' was overwritten with the source file '{1}'.", targetFile, sourceFile), String.Empty, 0, False)
End If
End Sub
End Class
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply