ADO.NET Schema Rowset Enumerator Locking Excel file
-
Hello my internet peers,
As I know many of you have, I've grown quite fond of using the ADO.NET Schema Rowset Enumerator Foreach Loop when working with Excel workbooks whose worksheets contain the same metadata. I've used this approach for quite some time without issues until recently. For some reason, I've been encountering more and more "locked file errors" due to this approach. Has anyone else experienced this and found a solution? Please see below for an example:
What we have here is a Foreach File Enumerator which loops through a directory. Within this loop, we have 3 ADO.NET Schema Rowset Enumerators which loop through the worksheets of 3 separate Excel workbooks. These ADO.NET enumerators successfully load the desired worksheets, but then [sometimes] fail to close the connection to the workbook. Thus, when the ADO.NET loop succeeds and I want to delete the current file, my Delete File Task fails with the following error:
[File System Task] Error: An error occurred with the following error message: "The process cannot access the file 'example.xlsx' because it is being used by another process.".
I've tried a few solutions including adding a script task following the ADO.NET loop which connects to the current workbook, and closes the connection to the workbook. All to no avail. Might I add that I encounter this error much more when the job is scheduled on a SQL Agent in comparison to running the SSIS solution manually on one's local. However, I encounter the issue both ways. Please, my intelligent internet peers, give me some guidance to solving this most frustrating of frustrations.
Sincerely yours,
Me -
Not sure whether it is relevant, but what is the value of the RetainSameConnection property on the Excel Connection Manager?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
I have tried it as both TRUE and FALSE -- all to no avail. Might I also add that I am not using an Excel Connection to connect to the file, but rather an ADO.NET connection which allows me to loop through the workbooks using the ADO.NET Schema Rowset Enumerator. I've also attempted this approach:
Basically, instead of deleting the files as they are loaded, a "LoadedFileList" is built within the "01b - Build FileList" task. At the end of the job, we create an object to loop through built from the "LoadedFileList". At this point, we loop back through our Load folder, meanwhile looping through our "LoadedFileList" object. When the names match, we delete the current file -- this has been built simply so we don't delete files unintentionally. Still, I am experiencing the same error:
[File System Task] Error: An error occurred with the following error message: "The process cannot access the file 'example.xlsx' because it is being used by another process.".
Previously, I've added a script in place of the "01b - Build FileList" task which opens and closes, or simply closes, a connection to the given workbook before attempting to delete the file (again all to no avail). Again, I receive the error most of the times, but it also succeeds in deleting the file sometimes. I tend to receive this error more often when executed via a SQL Agent as opposed to manually running the solution on a desktop.
Your help and experience is greatly appreciated!
-
I can only imagine how annoying that this is. I'm running out of ideas, but I think I would do the following:
1) As well as scripting the close of the ADO.NET connections, add a .Dispose too.
2) Trying doing the file deletions in a C# script task, rather than using a FST.
3) If (2) does not work, try adding a delay in your C# script task before attempting the deletions.The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Hi,
I have had the same issue many times, and after a lot of googling and experimenting, I discovered that its the combination of using an FELC with ADO Net that seems to cause the issues. One site I was browsing mentioned using the GetOleDbSchemaTable command to get around the issue, although did not provide an example. So I googled and experimented further, and found a method that works using your existing ADO connection. You just use it in a script task instead of the FELC, and force it to close and disconnect the connection after each Excel file. Here is the code I used, which I put together rather haphazardly, using code copied from several other sources to get what I wanted in the simplest way possible:
Dim sqlConn As OleDb.OleDbConnection
sqlConn = DirectCast(Dts.Connections("ADO Worksheets").AcquireConnection(Dts.Transaction), OleDb.OleDbConnection)
Dim dtSheets As DataTable = sqlConn.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Tables, Nothing)
Dim listSheet As New ArrayList
Dim drSheet As DataRow
For Each drSheet In dtSheets.Rows
listSheet.Add(drSheet("TABLE_NAME").ToString())
Next
sqlConn.Close()
sqlConn.Dispose()
Dts.Variables("User::AllSheets").Value = listSheet
Dts.TaskResult = ScriptResults.SuccessMy ADO connection manager was called "ADO Worksheets", and "AllSheets" was a global variable defined as an object. This script was embedded in a FELC that processed the Excel files, and was immediately followed by an inner FELC to loop though the worksheet names stored in the "AllSheets" variable. Inside that FELC was another script task that filtered out the sheets I wanted to process by setting a boolean variable, followed by a 2nd script task that saved each worksheet as a txt file. This may seem a little too modular, and the code could have been combined, but the Save As Txt script was a reusable component I use in a number of places, and it made it easier for me to find out where my code was failing, but they could easily be combined into a single script task. That inner FELC was then followed by the FST Archive task, which will now work, thanks to (I believe) the close and dispose commands in the above code. If you wanted to, you could probably perform all those steps in a single script task with internal loops (but that would be a debugging nightmare IMHO).
I hope someone can find this useful.
Graeme
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply