Category Archives: Talend

Talend DI

Talend

Talend Calling Stored Procedures on SQL Server

Calling stored procedures on SQL Server with parameters

In order to call stored procedures in Talend we need to configure a source for the various parameters e.g. with a tFixedFlowInput as shown below

tfixedflowinput -> sp call -> get results

tfixedFlowInput details

In the tFixedFlowInput we need to create a schema matching the parameters on the stored procedure and then use tick “Use Single Table”

Create a column for each input parameter and set the value you wish to use (note that this can actually be sourced from e.g. an iteration loop instead of a fixed flow to get the details from the database)

Connect the input source to a tdbSP connection

tDBSP Connection Details

Drag a component from the Db Connections meta data for the database / server that you wish to execute the stored procedure on and configure as follows :-

SO Name = Name of the stored procedure to be executed enclosed in quotes

Add parameters using the + button under the parameters section of the tDBSP connection and map them to the schema column name from the input (tFixedFlowInput in this example)

Define the direction of the parameter in the Type column e.g. IN / OUT / RECORD SET

The example above just dumps the parameters and results into the log output

Talend

Integrated security connections

How to connect Talend to SQL Server using integrated Security

The default setup of Talend doesn’t allow integrated security connections to SQL server. In order to connect to sql server with integrated security you must have ntlmauth.dll installed – it should be in your BIN and JRE\BIN folders e.g. c:\program files\Zulu\zulu-8\bin and c:\program files\Zulu\zulu-8\jre\bin. I also prefer to have a copy in windows\system32 just in case.

If that doesn’t work I also use -vm argument to ensure that the correct version of zulu\jre\bin is loaded e.g. – vm ” c:\program files\Zulu\zulu-8\jre\bin”

Then its just a matter of adding IntegratedSecurity=SSPI to your connection in the Additional parameters section of your Database connection .

oh and don’t forget to add your port if sql is running as an instance