Connection with Azure sql server database issues

[Anonymous]

Hello All,

I can able to connect azure sql server database and also retrieving schema in Talend open studio for DI 6.3.

But during running the job it is saying "Connection reset by peer: socket write error, Exception in the tmssql component.

I referred talend community to get solutions but still, It's not working for me. 

I have attached the error screenshot below 

are there any settings to modify?

can someone help me to resolve this?

[vapukov]

is it correct - You have tMSSQLConnection - After tMSSQLOutput? or it other connection?

[Anonymous]

Hi,

My job flow is 

Excel -------------> tmap-----------------> tmssqloutput------------>mssqlconnection

                                                                                                   |

                                                                                                   |

                                                                                                 Mssqlcommit.

AM I doing ant mistake?

[vapukov]

correct must be

tPreJob -> -->mssqlconnection

than all other:

Excel -------------> tmap-----------------> tmssqloutput------------>Mssqlcommit. 

tPostJob ----> tMSSQLClose

No connections from and to:

tPreJob -> -->mssqlconnection and tPostJob ----> tMSSQLClose

it separate parts of Job - Before Job and After Job

You try to insert data before define connection                                                                                               

[Anonymous]

Socket reset by peer is an error when the server shutdown the connection in a forceful mode. Usually happens when a firewall in the middle drop the connection.

I will check the infrastructure to connect to the cloud and if other application has similar issue (I'm thinking e.g. SSMS) or better some Java based SQL IDE (sql workbench, Oracle SQLDeveloper, etc.) that use the same set of drivers.

So I wish to investigate the properties on Azure and better if there is also a firewall between you and Azure (usually happens).

I think more a network error than a problem in the design of the flow, in that case I'm expecting or a compilation error or an error in connection.

Regards,

Mirko

[Anonymous]

Hi everyone,

In mssqloutput changed batch size from advanced settings as 50 and the data got loaded into the destination table.

I guess this might be temporary solution.

When we have Lakhs of records then this batch size of 50 will take huge time.

I even tried by changing various batch size but the job fails when the batch size is 100 and more.

I guess we need to set timeout settings in the database!

@msciack: I guess the Permanent solution might be setting the properties on AzureSQL database and also a firewall between Talend and Azure.

I request if anyone resolved this issues by changing the properties on AzureSQLDatabse kindly please share here.

Thanks in advance,

[Anonymous · Accepted solution]

Hi @Sara,

Except reducing batch size, did you get any resolution to the problem ?

My solution : 

Setting JDBC provider as Microsoft instead of open source JTDS solved the problem.

Another problem:

There is one issue that I'm getting Null for Date field with Microsoft SQL JDBC when using tMSSqlInput 

[MikeBender27]

I have been fighting this problem by trying different batch sizes for a while but nothing was really fast AND reliable.  After switching to the Microsoft driver it seems to be really fixed and it works with any batch size I specify.  The problem does not occur when connecting to a SQL Server 2016 database we have installed on an Azure VM. That setup works with the default driver no problem.  If you are running Azure SQL though, the Microsoft driver works much better.  Write performance using the batch mode is now reliable and much more in line with my expectations.  Thanks for the solution.

[Anonymous]

Thank you!