One Star

Trouble in setting up proxy in metadata

I have Talend Open Studio for Data Integration 6.2.1 installed on a Windows desktop. 
I am able to setup and run a project locally to connect my database with SalesForce host.  However, to deploy the project to Prod environment, I will need to enable and configure a proxy server to the SalesForce connection component.  The proxy server is not accessible from my desktop.
The problem is that the SalesForce metadata component tests the connection before saving the configuration. Since the connection cannot be made via the proxy server from my desktop, I am not able to save the proxy server information to the project.
I have tried to use context to save the proxy server but cannot find a way to enable proxy from context.
Any suggestion on how to resolve this problem?
Thanks for the help.
8 REPLIES
One Star

Re: Trouble in setting up proxy in metadata

Hi,
Add a boolean variable to your context (let say ProxyNeeded) then test the variable when your job starts.

If the variable is set to True (for Production), call a tSetProxy component for which all fields are populated using dedicated context variables.

That's it!
Regards,
TRF
One Star

Re: Trouble in setting up proxy in metadata

Thanks very much for the information.  I will try it out and let you know how it works out.
One Star

Re: Trouble in setting up proxy in metadata

Hi TRF,
Sorry for taking me so long for this update since I was sidetracked by other issues.
I am still having trouble to use the tSetProxy component. I added a tSetProxy component in my project and the SalesForce input component can be seen to be enabled to use the proxy (see the image below).  However, the project still is not sending the request to the proxy.  I must be doing something wrong.  Can you advise?
Thanks so much for your help.

One Star

Re: Trouble in setting up proxy in metadata

Hi Kelvinli,
In your job you must ensure the tSetProxy is finished before the tSalesforceInput starts. 
If you have to set the proxy for all cases, start with tSetProxy and rely it to Account_Dev by a "On Subjob OK" trigger:

If you need to be more flexible, depending on your context (home work with no proxy, office with proxy for example), you have to define a boolean context variable (lets say ProxyNeeded) with deferent value depending on the context you choose when launching the job.
As you need a starting component, you have to arrange your job with a tSleep or anything else to go to the tSetProxy depending on the ProxyNeeded value using a "Run if" trigger:

Here is the story: sleep 0; if ProxyNeeded is set to true, set the proxy; on subjob ok start the next subjob.
Is it clear for you?
Regards,
TRF
One Star

Re: Trouble in setting up proxy in metadata

Hi TRF,
Thanks so much for your valuable advice.  After adding the trigger, the job works!
There is one issue that is kind of puzzling.  In the example above, when I set all three SalesForce components to use the proxy, the job would fail to connect database (see the error message below). However, when I disable using proxy in any of the component, the job would succeed. So I end up creating a fake fourth component that is not using proxy.
This is no longer blocking my work but I would like to know what caused the problem if possible.
Thank you again for your great help.
Kelvin
Error Message
Exception in component tMSSqlOutput_1
java.sql.SQLException: Network error IOException: Malformed reply from SOCKS server
        at net.sourceforge.jtds.jdbc.JtdsConnection.<init>(JtdsConnection.java:436)
        at net.sourceforge.jtds.jdbc.Driver.connect(Driver.java:184)
        at java.sql.DriverManager.getConnection(DriverManager.java:664)
        at java.sql.DriverManager.getConnection(DriverManager.java:247)
        at local_project.sf_to_bars_dev_0_1.SF_To_BARS_Dev.tSalesforceInput_3Process(SF_To_BARS_Dev.java:9433)
        at local_project.sf_to_bars_dev_0_1.SF_To_BARS_Dev.tSetProxy_1Process(SF_To_BARS_Dev.java:1615)
        at local_project.sf_to_bars_dev_0_1.SF_To_BARS_Dev.runJobInTOS(SF_To_BARS_Dev.java:17977)
        at local_project.sf_to_bars_dev_0_1.SF_To_BARS_Dev.main(SF_To_BARS_Dev.java:16454)
Caused by: java.net.SocketException: Malformed reply from SOCKS server
        at java.net.SocksSocketImpl.readSocksReply(SocksSocketImpl.java:129)
        at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:459)
        at java.net.Socket.connect(Socket.java:589)
        at net.sourceforge.jtds.jdbc.SharedSocket.createSocketForJDBC3(SharedSocket.java:288)
        at net.sourceforge.jtds.jdbc.SharedSocket.<init>(SharedSocket.java:251)
        at net.sourceforge.jtds.jdbc.JtdsConnection.<init>(JtdsConnection.java:331)
        ... 7 more
Function that failed:
conn_tMSSqlOutput_1 = java.sql.DriverManager.getConnection(
url_tMSSqlOutput_1, dbUser_tMSSqlOutput_1,
dbPwd_tMSSqlOutput_1);
All parameters are the same with and without the last component using proxy.  But when all are using the proxy, this function would run for a long time and throws exception.
One Star

Re: Trouble in setting up proxy in metadata

Hi,
As you have more than one tSalesforce component, I strongly recommend you to use a tSalesforceConnection and to refere to this connection into the tSalesforceInput components. Notice that if you use tSalesforceOutputBulk (or BulkExec) you need 2 tSalesforceConnection, one for read and another for bulk operation.
However, I see the error comes from the tMSSQLOutput component which need to connect to SQL Server when the job arrives at this stage. Here again I recommend you to use a separate tMSSqlConnection. In this way, you can connect to SQL Server before the proxy has been defined. I think it should solve your problem because you probably do not have to pass the proxy to touch the database server.
Here is how your job should looks like:

Define some global variables (if needed), connect to SQL Server, select some data (if needed).
Only if ProxyRequired == true, set the proxy parameters, then connect to Salesforce.
Then the job continue and reuse the pre opened connections when needed.
Also, take care how the proxy is configured (refer to the previous capture).
Hope this helps.
TRF
One Star

Re: Trouble in setting up proxy in metadata

Hi TRF,
As you suggested, I used tSalesForceConnection and tMSSqlConnection components and lined them up using triggers. Now it works like a charm.
Thank you so much for your time and detailed suggestions.  It is greatly appreciated.
Kelvin
One Star

Re: Trouble in setting up proxy in metadata

You're welcome.
Don't forget to mark the subjectg as solved.