Pentaho Data Integration 3.2.0 stable: Connection (ODBC and JDBC) with M $ SQL Server 2000

Good.
Because concurrent questions about this issue and connections in general, along with my colleague Mr. Mariano García Mattio have made ​​a small manual, which will explain with examples and graphics set from Pentaho Data Integration 3.2.0 stable connections ODBC and JDBC with M $ SQL Server 2000.

SQL Server Enterprise Manager:

Service Manager SQL Server:

ODBC Connection

1) Establish ODBC data source:

• Go to Control Panel → Administrative Tools → Data Sources (ODBC)
• Select the tab "System DNS" and press "Add ...".
• Select the "SQL Server" and click on "Finish."
• Complete the form to create the data source:
  • Name: datawarehouseODBC
  • Description: Connecting to ODBC data warehouse
  • Server: TGX
• Once the tab press "Next>".
• Complete information on this screen they see fit under and press "Next>".
• Tick ​​the "Set the following as the default database" and select "data warehouse." Press the "Next>".
• Complete information on this screen they see fit under and press "Finish."

Finally we should have something like this:

UHCjIYaMof39qXw3w2u5GxT5VfylCsF7jQn5zsL4FDXsup9VHZrJjdf6ivLA5Uwo4zqb/origenODBC.png "/>

2) Establish ODBC connection in Pentaho Data Integration 3.2.0 stable:

• In the "Transformation" or "Work" that we use it, go to "View" tab:

• Press the right button on "Database Connections" and select "New."
• Then fill only the fields necessary to establish the connection, ie fields tab & ldquo; General "
  • Connection Name: Connection to ODBC data warehouse
  • Connection Type: MS SQL Server
  • Access: ODBC
  • ODBC Source DNS Name: datawarehouseODBC

• It should press the "Test" to ensure proper connection.
• Finally click "OK."

JDBC Connection

1) Configure JDBC Driver:

• Copy the driver "sqljdbc.jar" in "[data-integration_home] \ libext \ JDBC"
• Pentaho Data Integration restart if already running.

2) Enable the authentication standard in M $ SQL Server 2000:
This step is carried out so that when we try to connect to the database we do not throw the following error: user login 'sa'.Reason: Not associated with a connection to SQL Server trusted.

• Open SQL Server Enterprise Manager.
• Right click on the server, in our case: (local) (Windows NT).
• Select "Properties."
• Go toto Security tab and choose "SQL Server and Windows".
• Press the "OK" and restart the service.

3) Set up JDBC connection in Pentaho Data Integration 3.2.0 stable:

• In the "Transformation" or "Work" that we use it, go to "View" tab:

• Press the right button on "Database Connections" and select "New."
• Then fill only camment necessary to establish the connection, ie the fields in the General tab:
  • Connection Name: Connection JDBC datawarehouse
  • Connection Type: Generic database
  • Access: Natie (JDBC)
  • Custom Connection URL: jdbc: sqlserver: / / localhost: 1433; databasename = datawarehouse; responseBuffering = adaptive
  • Custom Driver Class Name: com.microsoft.sqlserver.jdbc.SQLServerDriver
  • User Name: sa
  • Password: sa

• It should press the "Test" to ensure proper connection.
• Finally click "OK."

We hope you find it useful.
Greetings.