locked
forcing the rename of a file RRS feed

  • Question

  • I have a data flow flow task for loading a flat text file into a database table, in between the source and the destination there are some components to transform the data. If there is an error I'd like the source text file renaming, however when I try and attempt to rename the file via a File System Task linked to the data flow via an on error precedence constraint, I get an error message stating that the file is already in use. I cannot see any options in the file system task to force the move to take place, nor have any ways of doing this via a script task occurred to me. Can someone suggest some ways by which I might be able to work around this ?. 
    Monday, March 5, 2012 3:44 PM

Answers

  • Hi Chris,

    1st you need to stop processing the file, then rename it.

    If I remember right, I did it last time inside a sequence container linked to an OnError event handler, so as soon as there is an error, the container stops executing reporting an error, and then the OnError event handler fires (in my case I sent the file back to FTP), in your case rename using the very FST.


    Arthur My Blog

    • Proposed as answer by Eileen Zhao Wednesday, March 7, 2012 6:25 AM
    • Marked as answer by Eileen Zhao Friday, March 9, 2012 3:22 AM
    Monday, March 5, 2012 3:50 PM
  • You need a small delay so that the file is released and can be moved . what i do is

    1 - from your DFT use a script task to set a variable so rename the file, use the Constraint Failure (  red line )

    ' Microsoft SQL Server Integration Services Script Task
    ' Write scripts using Microsoft Visual Basic 2008.
    ' The ScriptMain is the entry point class of the script.
    Imports System
    Imports System.Data
    Imports System.Math
    Imports Microsoft.SqlServer.Dts.Runtime
    <System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
    <System.CLSCompliantAttribute(False)> _
    Partial Public Class ScriptMain
    	Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    	Enum ScriptResults
    		Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
    		Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    	End Enum
    	
    	Public Sub Main()
    		'
    		' Add your code here
            '
            'System::PackageName,User::uVar_BackupDestinationFolder,User::uVar_PathFileName
            'User::uVar_BackupRejectedFileToFolder
            Dim strFileName As String = Dts.Variables("uVar_FileName").Value.ToString()
            Dim strFileExtension As String = Dts.Variables("uVar_FileExtension").Value.ToString()
            Dim strTemp As String ' making the final backup string  'uVar_BackupRejectedFileToFolder()
            strTemp = Dts.Variables("uVar_BackupRejectedFileToFolder").Value.ToString
            strTemp = strTemp & "\"
            ' The SYSTEM package name is replaced by uVar_PackageName to make the child and parent full name (i.e : "pkgETLParentExcel -ChildPkg- pkgETLExcelChild - " )
            'strTemp = strTemp & Dts.Variables("PackageName").Value.ToString & " - "
            strTemp = strTemp & Dts.Variables("uVar_PackageName").Value.ToString & " - "
            strTemp = strTemp & DateTime.Now.ToString("yyyy-MM-dd-HHmmssff") & " -RejectedFile- "
            strTemp = strTemp & strFileName & "." & strFileExtension
            Dts.Variables("uVar_BackupRejectedFileToFolder").Value = strTemp.ToString
            Dts.TaskResult = ScriptResults.Success
          
    	End Sub
    End Class

    2 -use a FSYS to move file


    Sincerely SH -- MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

    • Proposed as answer by Eileen Zhao Wednesday, March 7, 2012 6:26 AM
    • Marked as answer by Eileen Zhao Friday, March 9, 2012 3:22 AM
    Monday, March 5, 2012 5:03 PM
  • I have a data flow flow task for loading a flat text file into a database table, in between the source and the destination there are some components to transform the data. If there is an error I'd like the source text file renaming, however when I try and attempt to rename the file via a File System Task linked to the data flow via an on error precedence constraint, I get an error message stating that the file is already in use. I cannot see any options in the file system task to force the move to take place, nor have any ways of doing this via a script task occurred to me. Can someone suggest some ways by which I might be able to work around this ?. 
    You could use a Script Task to check whether the file is still locked and retry after X seconds:
    http://microsoft-ssis.blogspot.com/2011/05/wait-until-file-is-unlocked.html I also made a custom task for it: File-in-use Task

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    • Proposed as answer by Eileen Zhao Wednesday, March 7, 2012 6:26 AM
    • Marked as answer by Eileen Zhao Friday, March 9, 2012 3:22 AM
    Monday, March 5, 2012 8:16 PM

All replies

  • Hi Chris,

    1st you need to stop processing the file, then rename it.

    If I remember right, I did it last time inside a sequence container linked to an OnError event handler, so as soon as there is an error, the container stops executing reporting an error, and then the OnError event handler fires (in my case I sent the file back to FTP), in your case rename using the very FST.


    Arthur My Blog

    • Proposed as answer by Eileen Zhao Wednesday, March 7, 2012 6:25 AM
    • Marked as answer by Eileen Zhao Friday, March 9, 2012 3:22 AM
    Monday, March 5, 2012 3:50 PM
  • You need a small delay so that the file is released and can be moved . what i do is

    1 - from your DFT use a script task to set a variable so rename the file, use the Constraint Failure (  red line )

    ' Microsoft SQL Server Integration Services Script Task
    ' Write scripts using Microsoft Visual Basic 2008.
    ' The ScriptMain is the entry point class of the script.
    Imports System
    Imports System.Data
    Imports System.Math
    Imports Microsoft.SqlServer.Dts.Runtime
    <System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
    <System.CLSCompliantAttribute(False)> _
    Partial Public Class ScriptMain
    	Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    	Enum ScriptResults
    		Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
    		Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    	End Enum
    	
    	Public Sub Main()
    		'
    		' Add your code here
            '
            'System::PackageName,User::uVar_BackupDestinationFolder,User::uVar_PathFileName
            'User::uVar_BackupRejectedFileToFolder
            Dim strFileName As String = Dts.Variables("uVar_FileName").Value.ToString()
            Dim strFileExtension As String = Dts.Variables("uVar_FileExtension").Value.ToString()
            Dim strTemp As String ' making the final backup string  'uVar_BackupRejectedFileToFolder()
            strTemp = Dts.Variables("uVar_BackupRejectedFileToFolder").Value.ToString
            strTemp = strTemp & "\"
            ' The SYSTEM package name is replaced by uVar_PackageName to make the child and parent full name (i.e : "pkgETLParentExcel -ChildPkg- pkgETLExcelChild - " )
            'strTemp = strTemp & Dts.Variables("PackageName").Value.ToString & " - "
            strTemp = strTemp & Dts.Variables("uVar_PackageName").Value.ToString & " - "
            strTemp = strTemp & DateTime.Now.ToString("yyyy-MM-dd-HHmmssff") & " -RejectedFile- "
            strTemp = strTemp & strFileName & "." & strFileExtension
            Dts.Variables("uVar_BackupRejectedFileToFolder").Value = strTemp.ToString
            Dts.TaskResult = ScriptResults.Success
          
    	End Sub
    End Class

    2 -use a FSYS to move file


    Sincerely SH -- MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

    • Proposed as answer by Eileen Zhao Wednesday, March 7, 2012 6:26 AM
    • Marked as answer by Eileen Zhao Friday, March 9, 2012 3:22 AM
    Monday, March 5, 2012 5:03 PM
  • I have a data flow flow task for loading a flat text file into a database table, in between the source and the destination there are some components to transform the data. If there is an error I'd like the source text file renaming, however when I try and attempt to rename the file via a File System Task linked to the data flow via an on error precedence constraint, I get an error message stating that the file is already in use. I cannot see any options in the file system task to force the move to take place, nor have any ways of doing this via a script task occurred to me. Can someone suggest some ways by which I might be able to work around this ?. 
    You could use a Script Task to check whether the file is still locked and retry after X seconds:
    http://microsoft-ssis.blogspot.com/2011/05/wait-until-file-is-unlocked.html I also made a custom task for it: File-in-use Task

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    • Proposed as answer by Eileen Zhao Wednesday, March 7, 2012 6:26 AM
    • Marked as answer by Eileen Zhao Friday, March 9, 2012 3:22 AM
    Monday, March 5, 2012 8:16 PM