ssmafororacleconsole "losing" connection

Question

Hello

w, folks.

Here I am again, with another question/problem.

So, I developped a full ETL for our company to migrate some of our clients from Oracle to SQL Server. Long story short, I'm using SSMA only and exclusively to perform data copy across DBMS and nothing more. This operation is performed with the console line commands, and for quite some time it worked like expected, but today I got an extremelly weir behavior.

So in my code (a batch file) I get the executable with a WHERE command, like this:

for /F "tokens=*" %%f in ('where ssmafororacleconsole.exe') do (
    set ssma="%%~f"
)

Please don't criticize me; I'm fully aware that this is not the best thing to do, but that's the best I can do and it works like a charm (or used to).

So, the ssma variable would get the full path for the executable, usually something like:

"c:\program files\microsoft sql server migration assistant for oracle\bin\SSMAforOracle.exe"

Like I said, for quite some time this always worked, but suddenly, in a new machine, the ETL program got stuck today and investigating it I concluded what looks like to be a connexion simply lost by SSMA. It's kind of hard to perfectly explain the situation, but let me try:

Once the program launches the line

ssma -s myscript.xml

SSMA proceeds until this step:

[2021-03-08 20:50:02] START connect-source-database
[2021-03-08 20:50:02] MESSAGE generic Establishing connection to oracle server 'ora5'
[2021-03-08 20:50:04] MESSAGE output-window Connection to Oracle established successfully.

So OK, the connection with the Oracle database is perfectly stablished. But then it simply freezes; I can wait hours and it simply does not resume. I checked the Task Manager and concluded that there's absolutely no activity, the process sits idle at 0% CPU. I logically thought it might be something in the database, maybe the Oracle connection was blocked by some other session?

Upon investigating the in the Oracle side I could not find any connection from the SSMA, nothing. There was absolutelly no sessions from the ssmafororacleconsole on the database. I wonder why the program was stuck like waiting something from the Oracle end?

I did this exercice a couple of times, changing my XML script thinking that it could be some parameter or incorrect configuration without luck, always the same stuck execution.

So just out of curiosity I tried manually launching SSMA from a cmd prompt in the old fasion, relying on the path:

ssmafororacleconsole -s myscript.xml

And voilà, the migration was sucessfuly; I inspected the oracle server at the same time and could see the SSMA connection the whole time. So, the only real difference between calling SSMA on the prompt and on my program was the full path? OK, I just changed the program and took out the variable with the the full path and called the executable directly. And once more the migration job was sucessful, it did not get stuck and I could see the connection and the operations on the Oracle side.

To rule out it was a the program error, I tried the opposite: in a cmd prompt I called the ssmafororacleconsole executable with its full path and reproduced the same problem, the process sit idle on Task Manager and no activity on the database.

So, my conclusion: calling the ssmafororacleconsole with a full path command makes it, sor some reason, lose the connectivity with Oracle but not acknowledging it, resulting in the process getting stuck. It works only when calling its executable directly, relying on its entry on the PATH.

This is a rather weird situation and I don't even believe it belongs here, but I wonder why the calling method gives this behavior. Do you have any ideas, mr. experts? How come the program starts and make a initial connection? Why it simply loses it aftwer a while? And why is there no error nor warning? For my situation specifically I believe I can go with the single call from the path (I don't really remember why I decided for the full path to be honest) and I cannot see an environment where it wouldn't be possible this way.

Thank you in advance. Your insight on the matter would be much appreciated.