Category Archives: Talend Open Studio

Learn about Talend Open Studio for Data Integration, how to fix issues with SQL Server SSO and beginning tutorials that help you make your first project. Learn Talend here.

Talend Impossible to Initialize the Connection

Are you trying to add a connection in Talend to Microsoft SQL Server and receiving the “Impossible to Initialize the Connection” error?

In certain versions of Talend Open Studio for Data Integration, there are issues related to Microsoft SQL Server SSO.  Single Sign On (or Windows authentication) does not seem to properly work in Talend Open Studio until version 5.2.1.

In previous versions, you would have to substitute the desired pass through authentication with SQL Server authentication.  Beginning in Talend Open Studio 5.2.1 it appears to finally be corrected.

If you haven’t updated yet and desire to use SQL Server connections in Talend, then now is the best time ever!

Talend Open Studio for Data Integration: Lesson 2 – Creating a MS SQL Connection

The first thing you need to do once you have a Talend Project created is define your connections that data will be extracted from or where it will be written to.

Microsoft SQL Server is a good example of a database that many users will need to work with in ETL, so we will cover how to add that to your project in Talend.

1.  Create the Connection in Talend Repository

In your open project, look for the Repository.  This is where you will find many different objects available within your Talend project.  Specifically for adding a connection, we are going to expand the Metadata category within the repository.  Then you will need to right-click on “Db Connections” and choose “Create Connection”

Step 1 in Creating a Microsoft SQL Connection in Talend ETL
In the Talend project repository, right click on Db Connections under Metadata to add a connection to MS SQL

 

2.  Enter MS SQL Connection Name

You will then be prompted with a dialog box that will walk you through creating your connection.  In the first box which is step 1/2, enter at least a name for your connection and then click Next.

Step 2 in Creating a Microsoft SQL Connection in Talend ETL
Enter a name for your Microsoft SQL connection in Talend ETL

3.  Enter MS SQL Connection Details

Next, enter the details needed to define your connection.  Make sure to use SQL server authetication by entering a Login and Password.  Currently, Windows Authentication with SQL Server 2008 is not consistent.  After you have entered the details click “Finish”

Step 3 in Creating a Microsoft SQL Connection in Talend ETL
Enter the MS SQL connection details and click Finish

 

4.  Verify

You will now see your connection under Metadata in the Talend Repository

Adding a MS SQL connection in Talend Open Studio ETL
You will now see your Microsoft SQL connection created in Talend ETL

Talend Open Studio for Data Integration: Lesson 1 – Creating a Project

The first step in creating an ETL project in Talend Open Studio for Data Integration is to create the actual project definition. The project serves as a container for resources, including database connections, job scripts and contexts. When the application is launched, it will use a default folder in the installation directory named workspace. When a project is created, the project exists in a sub-directory in the workspace directory.

See the screenshot from Talend Open Studio for Data Integration 5.1:

Notice we have typed in a name of “OurFirstProject” in the Create a New Project field.  When we click the Create… button we get a dialog box that comes up and allows us to add a Project Description.  Clicking Finish then saves our project and gives us a new view in Open Studio as seen below.

At this point we can highlight the project we want and click Open, or we can use any of the other features.  When you arrive at this screen as the opening screen, simple click Create… to start a new project.

Talend Open Studio 5.2.0M4

For those awaiting release level 5.2 of Talend Open Studio for Data Integration, there is now a milestone release available from Talend’s website. For those awaiting a fix for the problems with connecting to SQL Server 2008 R2 using Windows Authentication, sorry to say but the problem is not corrected.

A solution remains to simple use SQL Server authentication, and that works, but this seems specifically to be a bug in the adding the connection and retrieving schema only.  If you create the connection and it errors and then manually define a schema for a table stored on your SQL Server 2008 R2 database, it will actually query the data successfully.  This means that they have attempted to make Windows Authentication work, but are not fixing certain bugs associated with it.

Although using SQL Server authentication is a valid work around, it is not an acceptable method to rely on long term.  Hopefully Talend still chooses to address this.

Talend Open Studio – java.sql.SQLException – Native SSPI Library not Loaded

There is a bug in Talend Open Studio for Data Integration version 5.0.2 when trying to connect to SQL Server without passing a user name and password.  Java requires libraries for single signon for this to occur, specifically the ntlmauth.dll file for the system.  However, in Talend Open Studio 5.0.2, having this file does not allow single sign on to Microsoft SQL Server.  This is confirmed on Microsoft SQL Server 2008 R2 64-bit.

When attempting to connect without supplying a username and password, the following error is received:

This issue is fixed in production release 5.1.1 of Talend Open Studio.  A workaround in version 5.0.2 is not available at this time.

Error Adding DB Connection in Talend Open Studio 5.1.1

There is a bug in Talend Open Studio for Data Integration version 5.1.1 when adding a database connection.  It has to do with the jdts component used for Microsoft SQL Server connections.  When adding a new connection you may get one of the following errors:

java.lang.ClassCastException: net.sourceforge.jdts.jdbc.CnnectionJDBC3 cannot be cast to net.sourceforge.jdts.jdbc.CnnectionJDBC2

The above error usually appears the first time you hit the button to “Check” the connection.  The second and subsequent attempts will produce a different error message as follows:

Impossible to initialize the connection !

This is a bug in Talend Open Studio for Data Integration that has a fix already implemented in the future 5.1.2 version.

See details of this bug at:  TDI-21382 SQL Server Connexion Fail

Update on 3/5/2013: The Impossible to Initialize the Connection error has been corrected in a future version.  Check out Talend Impossible to Initialize the Connection.