November 17, 2008 at 11:51 am
I tried to use File System task to rename a file. I set up to use expression to name the destination file so I can change the name of the destination file with the date attached at the end of the file. However when I tried to run the package, it kept telling me the file was not created.
What did I do wrong?
November 17, 2008 at 12:06 pm
Hard to say. I find debugging SSIS packages difficult based solely on a desription of what is going on. Is it possible to save the package to file system, zip it, and attach it to a post? That may help.
November 17, 2008 at 1:03 pm
I got it. I make the destination into user variable and make it as expression. Thanks
November 17, 2008 at 1:07 pm
Glad to see you solved the problem.
November 17, 2008 at 2:14 pm
Here's a sample where I archive move and I think rename files if it helps.
' 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()
'Three cases:
'1. The file does not exist so just copy the file
'2. The file exists with the same name and an older date overwrite the old file
Dim filename As String = Dts.Variables("PDF_filename").Value.ToString
Dim sourceFilePath As String = Dts.Variables("SourceFilePath").Value.ToString
Dim destFilePath As String = Dts.Variables("DestinationFilePath").Value.ToString
Dim destFilePathArchive As String = Dts.Variables("DestinationFilePathArchive").Value.ToString
Dim purgeSourceFile As Boolean = CBool(Dts.Variables("PurgeSourceFile").Value.ToString)
Dim ArchiveFilePath As String = Dts.Variables("removedFilePath").Value.ToString
Dim sourceFileName As String = sourceFilePath & "\" & filename
Dim destFileName As String = destFilePath & "\" & filename
Dim destFileNameArchive As String = destFilePathArchive & "\" & filename
If ValidateArchiveFileLocation(destFilePath, ArchiveFilePath) _
And ValidateArchiveFileLocation(destFilePathArchive, ArchiveFilePath) Then
'move to production folder
If File.Exists(destFileName) Then
File.SetAttributes(sourceFileName, FileAttributes.Normal)
'exact match check the modification date and update
If File.GetLastWriteTime(sourceFileName) > File.GetLastWriteTime(destFileName) Then
'newer file of the exact same name
Dim CopyDest As String = destFilePath & ArchiveFilePath & "\" & filename
Try
File.SetAttributes(destFileName, FileAttributes.Normal)
'Make sure that if the file exists at the destination it is not read only
If File.Exists(CopyDest) Then
File.SetAttributes(CopyDest, FileAttributes.Normal)
End If
File.Copy(destFileName, CopyDest, True)
File.SetLastWriteTime(CopyDest, Now())
Catch ex As Exception
MsgBox(ex.Message)
End Try
Try
File.Copy(sourceFileName, destFileName, True)
Catch ex As Exception
MsgBox(ex.Message)
End Try
End If
ElseIf Not File.Exists(destFileName) Then
File.Copy(sourceFileName, destFileName)
End If
File.SetAttributes(destFileName, FileAttributes.ReadOnly)
'move to archive folder
If File.Exists(destFileNameArchive) Then
'exact match check the modification date and update
If File.GetLastWriteTime(sourceFileName) > File.GetLastWriteTime(destFileNameArchive) Then
'newer file of the exact same name
Dim CopyDest As String = destFilePathArchive & ArchiveFilePath & "\" & filename
Try
File.SetAttributes(destFileNameArchive, FileAttributes.Normal)
'Make sure that if the file exists at the destination it is not read only
If File.Exists(CopyDest) Then
File.SetAttributes(CopyDest, FileAttributes.Normal)
End If
File.Copy(destFileNameArchive, CopyDest, True)
File.SetLastWriteTime(CopyDest, Now())
Catch ex As Exception
MsgBox(ex.Message)
End Try
Try
File.Copy(sourceFileName, destFileNameArchive, True)
Catch ex As Exception
MsgBox(ex.Message)
End Try
End If
ElseIf Not File.Exists(destFileNameArchive) Then
File.Copy(sourceFileName, destFileNameArchive)
End If
File.SetAttributes(destFileNameArchive, FileAttributes.ReadOnly)
'final verification statuses
Dim ProdSuccess As Boolean = File.Exists(destFileName)
Dim ArchiveSuccess As Boolean = File.Exists(destFileNameArchive)
'add to mail
CreateMail(filename, sourceFilePath, ProdSuccess, ArchiveSuccess)
'Delete only if there is a fiel in both the prod and destination locations
If ProdSuccess And ArchiveSuccess And purgeSourceFile Then
'successful copy delete the original
File.Delete(sourceFileName)
End If
Dts.TaskResult = Dts.Results.Success
Else
Dts.TaskResult = Dts.Results.Failure
End If
November 17, 2008 at 2:14 pm
Sorry didn't see you had solved it I really should hit refresh before posting
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply