none
The process cannot access the file because it is being used by another process

    Question

  • I have an Excel Connection using the MS OLE DB provider for Office 12 and a Data Flow task to load the data. The filename is set dynamically with an Expression and some variables. The DFT runs fine, then there are a couple of steps that do NOT reference the Excel connection, then there is a File System Task set to MOVE the file to a \Loaded folder. I keep getting the error "

    The process cannot access the file because it is being used by another process

    There are no other processes that would be access this file other than the SSIS package itself. I even put in a Script task the pauses 30 seconds (just in case the Data Flow hasn't had enough time to release the connection). Still get the error. Once the package finishes with this error and I stop the debugging, I can manually execute the one Move File task and it goes OK.

    How do I get the DFT to release the hold on this file?


    Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
    Monday, September 13, 2010 3:27 PM

Answers

  • Todd,

    You are not releasing the connection properly. Use the following code instead:

    Public Sub Main()
            Dim ConnMGR As ConnectionManager
            For Each ConnMGR In Dts.Connections
                Dim connStr As String = ConnMGR.ConnectionString
                Dim conn As Object
                Try
                    conn = ConnMGR.AcquireConnection(Nothing)
                    Dts.Events.FireInformation(1, "", "Connection ok: " + ConnMGR.Name + " = " + connStr, "", 0, False)
                Catch ex As Exception
                    Dts.Events.FireError(-1, "", "Connection failed on " + ConnMGR.Name, "", 0)

                Finally
                    If Not conn Is Nothing Then
                        ConnMGR.ReleaseConnection( conn )
                    End If

               End Try
            Next

            'Write Version variable
            Dts.Events.FireInformation(1, "", "Package Version = " + Dts.Variables("Version").Value.ToString, "", 0, False)

      Dts.TaskResult = ScriptResults.Success
     End Sub


    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
    • Marked as answer by Todd C Tuesday, September 14, 2010 2:10 AM
    Monday, September 13, 2010 5:38 PM
  • It is - but in this case, it's not particularly Excel's fault.  The Connection Manager for Excel is more "managed" than text files.  There are "degrees" of management depending on the connection.  The Excel provider won't release the file until SSIS destroys the connection manager itself.  In "more managed" connections - like OLEDB for SQL Server, for instance - it's very similar.  Acquiring a connection may not actually create a connection - you may just retrieve an existing connection that some other task created previously, but released and returned to the connection pool.  Similarly, releasing the connection doesn't close it, it just returns it to the connection pool so someone else can (efficiently) reuse it.


    Todd McDermid's Blog Talk to me now on
    • Marked as answer by Todd C Tuesday, September 14, 2010 2:10 AM
    Monday, September 13, 2010 8:16 PM

All replies

  • Hi Todd,

    I faced this issue couple of years back and I did get it resolved. But unfortunately I dont remember how or what did I do to get it resolved.


    Sudeep's Domain   Tweet me..
    Monday, September 13, 2010 3:37 PM
  • I've had a similar problem with the "move" instruction on the File System Task.  You may find better luck using two Tasks - one set to "copy" and the other to "delete" the source (a two-step "move").  That's worked for me in the past.
    Todd McDermid's Blog Talk to me now on
    Monday, September 13, 2010 3:38 PM
  • are you using a for loop to loop the excel sheet names ?

     


    Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).
    Monday, September 13, 2010 3:40 PM
  • check http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/31d23a91-6256-45c9-8b9e-1fec2049d3f5/#fa60df6a-31c6-4eeb-b471-8f1d300959da

    if you use a SCRIP TASK to getthe list of excel sheet names and a SSIS object to save the list into , then use a For each loop to loop the SSIS Object you wont have this problem. and you can use one object to move the file you dont need 2

    the script looks like

     Public Sub Main()
            Try
                '--------------------------------------------------------
                ' Add your code here
                '
                '--------------------------------------------------------
                Dim excelFile As String
                Dim connectionString As String
                Dim excelConnection As OleDbConnection
                Dim tablesInFile As DataTable
                Dim tableCount As Integer = 0
                Dim tableInFile As DataRow
                Dim currentTable As String
                Dim tableIndex As Integer = 0
                Dim excelTables As String()
                Dim LoopForNumnberOfRealTabs As Integer = 0

                Dim flagMultiTab As String
                flagMultiTab = Dts.Variables("uVar_SourceMultiTabExcel").Value.ToString.ToUpper

                If flagMultiTab = "TRUE" Then
                    'uVar_SourceMultiTabExcel, uVar_ExcelActiveTabName


                    connectionString = Dts.Variables("uVar_SourceConStr_ConnectionString").Value.ToString

                    excelConnection = New OleDbConnection(connectionString)
                    excelConnection.Open()

                    tablesInFile = excelConnection.GetSchema("Tables")
                    tableCount = tablesInFile.Rows.Count

                    For Each tableInFile In tablesInFile.Rows
                        currentTable = tableInFile.Item("TABLE_NAME").ToString
                        currentTable = currentTable.Replace("'", "")

                        If Right(currentTable, 1) = "$" Then
                            LoopForNumnberOfRealTabs += 1
                            ReDim Preserve excelTables(LoopForNumnberOfRealTabs - 1)
                            excelTables(LoopForNumnberOfRealTabs - 1) = currentTable
                        End If
                    Next

                    excelConnection.Close()
                    excelConnection = Nothing
                Else ' Else if of ... If flagMultiTab = "TRUE" Then
                    ReDim Preserve excelTables(0)
                    excelTables(0) = Dts.Variables("uVar_ExcelActiveTabName").Value.ToString()
                End If ' end if of ... If flagMultiTab = "TRUE" Then

                Dts.Variables("uVar_ExcelTabObjectName").Value = excelTables
                Dts.TaskResult = Dts.Results.Success

            Catch ex As Exception

                Dim strEX As String
                strEX = ex.Message.ToString
                Dts.TaskResult = Dts.Results.Failure

            End Try
        End Sub


    Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).
    Monday, September 13, 2010 3:45 PM
  • All: Thank you for excellent suggestions. I have narrowed it down to a Script Task at the very beginning of the package and maybe guys can help me out on this as I'm more of a database guy than a VB and C# coder (to me, C# = D-flat <\sarcasm>)

    The script reads all the connection Managers (one of them being the Excel OLE DB connecitn) and writes to my log file the basic Connection String info. This is helpful when debugging to let me know EXACTLY what resources are being referenced and the 'Version' user variable (derived from Version Major.Minor.Build). Here's my code:

    Public Sub Main()
            Dim ConnMGR As ConnectionManager
            For Each ConnMGR In Dts.Connections
                Dim connStr As String = ConnMGR.ConnectionString
                ConnMGR.AcquireConnection(Nothing)
                Try
                    Dts.Events.FireInformation(1, "", "Connection ok: " + ConnMGR.Name + " = " + connStr, "", 0, False)
                    ConnMGR.Dispose()
                Catch ex As Exception
                    Dts.Events.FireError(-1, "", "Connection failed on " + ConnMGR.Name, "", 0)
                End Try
            Next

            'Write Version variable
            Dts.Events.FireInformation(1, "", "Package Version = " + Dts.Variables("Version").Value.ToString, "", 0, False)

      Dts.TaskResult = ScriptResults.Success
     End Sub

    Any suggestions? Thanks in advance.

     


    Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
    Monday, September 13, 2010 4:49 PM
  • Got it. Typical of ME, I deal with stuff by just ignoring it:

    I simply added

     

    If InStr(connStr, "Excel") = 0 Then

    in the logic so it does NOT try to open any Excel files. Not elegant, maybe not best practices, but it works.

    Still open to any suggestions to imporve the code as I bet it makes some hard-core coders cringe.

    Thanks everybody.


    Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
    Monday, September 13, 2010 5:15 PM
  • Todd,

    You are not releasing the connection properly. Use the following code instead:

    Public Sub Main()
            Dim ConnMGR As ConnectionManager
            For Each ConnMGR In Dts.Connections
                Dim connStr As String = ConnMGR.ConnectionString
                Dim conn As Object
                Try
                    conn = ConnMGR.AcquireConnection(Nothing)
                    Dts.Events.FireInformation(1, "", "Connection ok: " + ConnMGR.Name + " = " + connStr, "", 0, False)
                Catch ex As Exception
                    Dts.Events.FireError(-1, "", "Connection failed on " + ConnMGR.Name, "", 0)

                Finally
                    If Not conn Is Nothing Then
                        ConnMGR.ReleaseConnection( conn )
                    End If

               End Try
            Next

            'Write Version variable
            Dts.Events.FireInformation(1, "", "Package Version = " + Dts.Variables("Version").Value.ToString, "", 0, False)

      Dts.TaskResult = ScriptResults.Success
     End Sub


    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
    • Marked as answer by Todd C Tuesday, September 14, 2010 2:10 AM
    Monday, September 13, 2010 5:38 PM
  •          Finally
                    ConnMGR.ReleaseConnection()


    This results in the line being underscored by the 'blue waves'. I can make that go away with this:

                Finally
                    ConnMGR.ReleaseConnection(ConnMGR)

    but it (the file system task) still fails. Am I missing something?

    Whats the difference between ConnMgr.ReleaseConneciton()

    and ConnMgr.Dispose() ?

    Thanks for the help.


    Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
    Monday, September 13, 2010 5:50 PM
  • Todd,

    I did fix the code. There was a mistake I made. Check the code again.

    When you dispose the connection manager using ConnMgr.Dispose you are not disposing the connection, but simply freeing the underlying object held by the ConnectionManager wrapper object. So in fact your original code never freed any of the connections. All of them stayed active.


    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
    Monday, September 13, 2010 6:22 PM
  • The code is now good, but for one little problem: It doesn't like to release Excel connections.

    I tried a simple test. I created a new package with just the Script Task and a File System (Move File) task. The Script was edited to jsut MsgBox the Connection String. When the Connection Manager is just a regular old "File Connetion", then it works fine. But when that conneciton is an Excel file using the Office 12.0 OLE DB provider, it fails with the error noted in the title of the post.

    Same script, same file to move, but the Connection TYPE seems to matter to somebody.

    So I'm back to my If/Then block to check for "Excel" in the Connection String.

     

    Excel is a four-letter-word.

    Thanks for your help.


    Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
    Monday, September 13, 2010 7:01 PM
  • It is - but in this case, it's not particularly Excel's fault.  The Connection Manager for Excel is more "managed" than text files.  There are "degrees" of management depending on the connection.  The Excel provider won't release the file until SSIS destroys the connection manager itself.  In "more managed" connections - like OLEDB for SQL Server, for instance - it's very similar.  Acquiring a connection may not actually create a connection - you may just retrieve an existing connection that some other task created previously, but released and returned to the connection pool.  Similarly, releasing the connection doesn't close it, it just returns it to the connection pool so someone else can (efficiently) reuse it.


    Todd McDermid's Blog Talk to me now on
    • Marked as answer by Todd C Tuesday, September 14, 2010 2:10 AM
    Monday, September 13, 2010 8:16 PM
  • My list of Excel complaints relating to SSIS is long and verbose!

    But the Data Flow tasks (of which there are three against this particular Excel file) all release the connection when finished so it can be moved. So why should AcquireConnection keep it hung, even if you issue a Dispose() or ReleaseConnection() method against it?

    Is this a bug? or will Microsof tell me it is "by design"?


    Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
    • Marked as answer by Todd C Tuesday, September 14, 2010 2:10 AM
    • Unmarked as answer by Todd C Tuesday, September 14, 2010 2:10 AM
    Tuesday, September 14, 2010 1:44 AM