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?
Solved! Go to Solution.
My job flow is
Excel -------------> tmap-----------------> tmssqloutput------------>mssqlconnection
AM I doing ant mistake?
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
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.
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,
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.