FileStream ctor throws sharing violation in SSIS script task, works in debug mode

Question

I have an Integration Services package (VS 2012) that loads values from an Excel workbook and stores them in SQL Server. I'm using a 3rd party library to extract the workbook data in a C# script task, but before that, in the task, I create a Filestream:

MessageBox.Show("Step 1");
using (FileStream fsWorkbook = new FileStream(strWkbkFilePath, FileMode.Open,
       FileAccess.Read, FileShare.ReadWrite))
{
    MessageBox.Show("Step 2");
    //...
}

The FileStream construction crashes with the error "The process cannot access the file ... because it is being used by another process." I know that this is where the crash occurs, because I see the "Step 1" message box, but not "Step 2".

What's frustrating is that, if I set a breakpoint in the the script task, before the line causing the error, and start stepping through the code, I'm able to proceed and access the workbook file without error. At first I thought this might be due to a race condition, but then I added the message box shown above, which also pauses execution before the file stream is created. The pausing due to the MessageBox doesn't prevent the error when running in non-debug mode.

I've tried using System.IO.File.Open and the FileStream constructor, both with FileShare.ReadWrite, as per this S.O. thread, so why do I get the sharing error? And why does it not happen when debugging?

Not sure if helps but when I get this error, I usually add a try catch around the script and log the real exception elsewhere since this exception is not helpful. — jones6, Apr 23 '15 at 19:14
@jones6 - I am handling exceptions and throwing some of my own. How do you log them from within your script? — Buggieboy, Apr 23 '15 at 19:26
I just wrote them to a log file I created but maybe that is not the best way. Maybe you could try Dts.Log method [link](https://msdn.microsoft.com/en-us/library/ms136131.aspx) — jones6, Apr 23 '15 at 19:33

score 0 · Accepted Answer · answered Apr 27 '15 at 21:03

Okay, this turns out to have been due to an OleDbConnection to the workbook having been left open in a previous script task in the package.

What I find odd is that the SSIS script debugger manages to resolve this before the attempted FileStream open in the subsequent package.

Take note, if you encounter weirdness when debugging a file permission issue in SSIS.

FileStream ctor throws sharing violation in SSIS script task, works in debug mode

1 Answers1