OleDbConnection to Excel fails on the second attempt to connect to a file

I'm submitting a...


[ ] Regression (a behavior that used to work and stopped working in a new release)
[ x] Bug report  
[ ] Performance issue
[ ] Feature request
[ ] Documentation issue or request
[ ] Support request => Please do not submit support request here, instead go to https://varigence.com/Forums?forumName=BimlStudio or email support@varigence.com
[ ] Other... Please describe:

Current behavior

The first time that the ExcelOleDbConnects to the database (usually from GetDatabaseSchema()) everything works as fine, but any subsequent calls to the excel file will cause BimlStudio to crash.

The underlying error message is:

AccessViolationException: 'Attempted to read or write protected memory"

Expected behavior

We should be able to connect and reconnect to excel files multiple times

Minimal reproduction of the problem with instructions

See package included.

What is the motivation / use case for changing the behavior?

In order to be able to connect to Excel files as desired when using BimlStudio

Environment


BimlStudio version: X.Y.Z

BimlStudio 2018 (this issue has been observed in both 32b and 64b)

Target SSIS Version:

- [ ] 2005
- [ ] 2008
- [ ] 2008 R2
- [ ] 2012
- [ ] 2014
- [ ] 2016
- [ ] 2017

Others:

Upon further investigation, this is a known issue with OleDbConnection objects in the .NET framework. These objects cannot connect to the same Excel file multiple times without an unpredictable amount of time passing beforehand. This means finding an immediate fix is unlikely, however we have some workarounds that may help.

In order to prevent any connections from happening before we are ready to execute a build, we need to add this line of code to any sections that we want to avoid from getting called:

<#
if (!IsBackgroundCompilation) { //add this to prevent the call from happening in the preview window, it will now only execute when building
AstExcelOleDbConnectionNode conn = (AstExcelOleDbConnectionNode)RootNode.Connections["Excel"];
var meta = conn.GetDatabaseSchema();
foreach (AstTableNode t in meta.TableNodes) {
    t.Name = "Test1";
#> 
<#}
} /*note the extra closing brace */#>

This prevents any connections from happening on the opening of BimlStudio, on UpdatePreview, or Saving when there are pending file changes.

If you cannot presently open the project in BimlStudio without it crashing, simply open the BimlScript file in your favorite text editor (ie Notepad++), and add theif (!IsBackgroundCompilation) check. Alternatively, you can delete your .muo settings file (in the project that you sent me, it was called BimlSamples.v50.muo) from your project folder, and should be able to open BimlStudio and add that line of code from there.

In the event that you are physically calling GetDatabaseSchema() multiple times on that connection inside of your BimlScript, there are workarounds for that as well. We want to only call GetDatabaseSchema() the one time, and pass that object around to the various places that you need it.

There are many ways to do this:

You can store the value in the ObjectTag Dictionary on the RootNode RootNode.ObjectTag["ExcelImportResults"] = conn.GetDatabaseSchema(); And now you can refer to RootNode.ObjectTag[“ExcelImportResults”] at any other place in your code.
You can store it in a static variable inside of a static class that you create. It can then be called anywhere statically at anytime.
You can pass it around using CallBimlScript(). Catherine Wilhelm has a fantastic article about passing data through CallBimlScript() that can be found here: https://www.cathrinewilhelmsen.net/2015/02/23/dont-repeat-your-biml-callbimlscript/

So while this is a bug in the underlying .NET Framework code, that we cannot fix. I hope that these workarounds are helpful.

varigence / BimlPit