need to enable MultipleActiveResultSets in connectionString when i have 2 sql upsert in the same DefineProcess

[githubdre]

hello, im working on a synchronisation between 2 database.
in the DefineProcess i read data from EF and insert it with SqlServerSave method.

if i have only one SqlServerSave in the DefineProcess, it works fine.
Howeever, if i try to update more than one table with multiple SqlServerSave (from different streams)

it throw exception like
There is already an open DataReader associated with this Command which must be closed first
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

after some research on the exception, i end up trying to set MultipleActiveResultSets=true on the connection string that i'm doing the upserts on.

With the MARS attribute, now it works fine with multiple SqlServerSave.

Is there a way to achive the same thing without setting this attribute ?
Or is it supposed to works only with the MARS attribute on ?

thank you

[paillave]

Can you give me a sample of the code you have for me to actually see what is the problem?
From what I understand, when you have several upsert or read in your process, it will be very likely they will occurs in a concurrent manner. Therefore, you connection should be setup with the MARS attribute set to true as it is the SQL driver that needs this setup to permit concurrent operations on the same connection. This attribute can only be setup at the connection string level.
I will update the documentation to make this clear.
Let me know if I got your problem correctly.

[githubdre]

Hello, here's the code sample

basically, i created 2 dll that i scaffold the database from EF.
objects that start with Osusr1gh are the source and the others the destination.

i read from the source with EFCoreSelect and write to destination with SqlServerSave
i use autoMapper for the transformation.

The code sample will merge 2 tables (acheteur and titreContact)

if i only run part 1 alone it works fine, if i only run part 2 alone it's fine too.
but both at the same time, it throws exceptions.

if a setup the MARS attribute, i can run the this method with both parts without exception.

I want to have an atomic transaction for all tables that i merge.. either it's all sucess or it's all failed (no partial merge)

also i looked at this thread to achieve the same thing with a transaction.
#292

at the end of the thread, you said
This workaround is to make 2 ado.net transactions that you set to their respective context and that you manage manually. Am I right?

I'm not really sure how to implement this.. why 2 transactions ?

i tried this option with the following modification

but for this to work, i have to change the original source code in the processItem method (wich i would like to avoid if possible)

this seems to works fine, but i'm not sure if it's the best way to go with the transaction options.

So basically, it looks that i have 2 choice now to do what i'm trying to do.

Option 1

• have all ETL in one DefineProcess method and enable MARS attribute in the connectionString

Option 2

• keep MARS attribute off and implement a custom transaction mecanism and have one operation per DefineProcess method.
• if all ProcessRunner execute sucessfully, commit the transaction.

What do you think is the best way to do ETL between 2 database with ETL.net ?

thank you,
André

[paillave]

You definitely must use the option 1. I actually don't understand why you are reluctant to enable MARS attribute in your connection string.
But... if you want to want to have a 2 phase commit mechanism (updating, in an atomic way, two databases in one transaction), you must use the TransactionScope; that's the only way to do with dotnet, but it will work only on windows platforms.

[githubdre]

great, It's not that im reluctant but was wondering if it was the right way to do it.
I will go with the MARS attribute.

thank you for your answer and for this pretty neat framework

[paillave]

great, It's not that im reluctant but was wondering if it was the right way to do it.
I will go with the MARS attribute.

thank you for your answer and for this pretty neat framework

It is so much the right way to do that I will have to add this detail as an important information in the documentation because it actually is a must have for the whole thing to work properly.