Home / server / Questions / 1163497
Accepted
questionto42
Asked: 2024-08-05 21:18:27 +0800 CST

How can I set MARS to "True" in the connection manager if it switches back to "False" right away after closing the properties? Or why is that?

  • 772

The connection manager writes to a table. Perhaps once a week, the error is thrown:

Connection is busy with results for another command

SSIS get deployed to Microsoft SQL Server 2016 (SP3-CU1-GDR) (KB5040944) - 13.0.7037.1 (X64).

I know from MARS (Multiple Active Result Sets) - Microsoft Learn that the missing "MARS = True" leads to the error. But if I change "MARS" to "True" in the properties of the connection manager, the "MARS" setting changes back to "False" as soon as I click "OK" so that the bold "True" is changed to thin "False" again when I open the properties afterwards:

enter image description here

I also checked whether just opening the connection manager's properties triggers the "False": I saved the change without opening the properties and then checked the Git log, which showed that the MARS setting change to "True" vanishes right after the click on "OK". What it saved were the default settings for retries (ConnectRetryCount = 1), and I wonder whether allowing a higher number of retries might help here as well. I did not try it, though.

I also wonder whether forcing the settings by changing it in the xml code instead of Visual Studio might help, but I fear that this might lead to worse things than a package that I need to run by hand once in a while. It might be worth a try, though, but I would rather get some insight from others before I check weird things.

Why does the "MARS" = "True" setting jump back to "False" right away? How can I change it to "True"? Should I do it at all if something like this happens?

ssis

1 Answers

  1. Best Answer

    The whole connection string is made by a project parameter. That is why any change in the menu jumps back to the settings of that parameter.

    You need to change that parameter by setting MARS Connection to True, for example:

    Data Source=my_server;Initial Catalog=my_catalog;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;MARS Connection=True;

    • 0