Home » Asp.netRSS

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

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?

 

12 Answers Found

 

Answer 1

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.

 

Answer 2

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.
 

Answer 3

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).
 

Answer 4

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).
 

Answer 5

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.

 

 

Answer 6

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.

 

Answer 7

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/
 

Answer 8

         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?

Thanks for the help.

 

Answer 9

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.

 

Answer 10

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.

 

Answer 11

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.

 

Answer 12

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"?

 
 
 

<< Previous      Next >>


Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter