Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

windows file system operation Expand / Collapse
Author
Message
Posted Tuesday, February 19, 2013 8:48 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 8, 2014 3:23 AM
Points: 48, Visits: 236
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.
Post #1421688
Posted Wednesday, February 20, 2013 10:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:12 AM
Points: 7,135, Visits: 12,746
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
Post #1422216
Posted Wednesday, February 20, 2013 1:00 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 8, 2014 3:23 AM
Points: 48, Visits: 236
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.
Post #1422262
Posted Wednesday, February 20, 2013 1:16 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:12 AM
Points: 7,135, Visits: 12,746
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
Post #1422274
Posted Wednesday, February 20, 2013 2:07 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 8, 2014 3:23 AM
Points: 48, Visits: 236
Sure, I will post it tomorrow when I go to work. But it is a lot of code
Post #1422295
Posted Thursday, February 21, 2013 3:11 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 8, 2014 3:23 AM
Points: 48, Visits: 236
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

Post #1422470
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse