topic Re: Connection with Azure sql server database issues in Talend Studio

Connection with Azure sql server database issues

Anonymous — Sat, 16 Nov 2024 09:47:35 GMT

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?

Re: Connection with Azure sql server database issues

vapukov — Fri, 12 May 2017 07:57:14 GMT

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

Re: Connection with Azure sql server database issues

Anonymous — Fri, 12 May 2017 08:49:43 GMT

Hi,

My job flow is

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

Mssqlcommit.

AM I doing ant mistake?

Re: Connection with Azure sql server database issues

vapukov — Fri, 12 May 2017 09:48:38 GMT

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

Re: Connection with Azure sql server database issues

Anonymous — Fri, 12 May 2017 22:01:17 GMT

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

Re: Connection with Azure sql server database issues

Anonymous — Tue, 16 May 2017 12:39:35 GMT

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,

Re: Connection with Azure sql server database issues

Anonymous — Thu, 05 Oct 2017 08:38:11 GMT

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

Re: Connection with Azure sql server database issues

MikeBender27 — Thu, 26 Oct 2017 16:43:51 GMT

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.

Re: Connection with Azure sql server database issues

Anonymous — Thu, 31 Oct 2019 19:30:31 GMT

Thank you!