Skip to main content
Mitratech Success Center

DataWarehouse on SQL Server for TCE 4.0 U3 or newer / DWH 4.0 U1 or new

1. For SQL Server instances running TCE 4.0 U3 or newer, you MUST install DWH 4.0 U1.

2. You must use the sqljdbc4.jar from this site: the old one will throw errors.
http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=11774

3. Use the below Shared.XML.  Note the change in "type?" from MSSQL to MSSQLNATIVE and the new attribute MSSQLUseIntegratedSecurity


<?xml version="1.0" encoding="UTF-8"?>
<sharedobjects>
  <connection>
    <name>Target_TeamConnect_Warehouse</name>
    <server>${jdbc.target_TC_hostname}</server>
    <type>MSSQLNATIVE</type>
    <access>Native</access>
    <database>${jdbc.target_TC_dbname}</database>
    <port>${jdbc.target_TC_portnumber}</port>
    <username>${jdbc.target_TC_username}</username>
    <password>${jdbc.target_TC_userpassword}</password>
    <servername/>
    <data_tablespace/>
    <index_tablespace/>
    <attributes>
      <attribute><code>PORT_NUMBER</code><attribute>${jdbc.target_TC_portnumber}</attribute></attribute>
      <attribute><code>SQL_CONNECT</code><attribute>SET TRANSACTION ISOLATION LEVEL READ COMMITTED;</attribute></attribute>
      <attribute><code>MSSQLUseIntegratedSecurity</code><attribute>false</attribute></attribute>
    </attributes>
  </connection>

  <connection>
    <name>Source_TeamConnect_data</name>
    <server>${jdbc.source_TC_hostname}</server>
    <type>MSSQLNATIVE</type>
    <access>Native</access>
    <database>${jdbc.source_TC_dbname}</database>
    <port>${jdbc.source_TC_portnumber}</port>
    <username>${jdbc.source_TC_username}</username>
    <password>${jdbc.source_TC_userpassword}</password>
    <servername/>
    <data_tablespace/>
    <index_tablespace/>
    <attributes>
      <attribute><code>PORT_NUMBER</code><attribute>${jdbc.source_TC_portnumber}</attribute></attribute>
      <attribute><code>SQL_CONNECT</code><attribute>SET TRANSACTION ISOLATION LEVEL READ COMMITTED;</attribute></attribute>
      <attribute><code>MSSQLUseIntegratedSecurity</code><attribute>false</attribute></attribute>
    </attributes>
  </connection>

</sharedobjects>

Troubleshooting
1. If you see this error, you are not using the correct sqljdbc4.jar file.

ERROR 29-05 14:43:04,923 - Find the new refresh time 2 - org.pentaho.di.core.exception.KettleValueException:
Date : Unable to compare with value [Date]
[B cannot be cast to java.util.Date

2. If you see this error, you did not properly update the shared.xml file with the third attribute above.

ERROR 29-05 13:18:03,676 - Source DB Type - An error occurred executing this step:
Error occured while trying to connect to the database

Error connecting to database: (using class com.microsoft.sqlserver.jdbc.SQLServerDriver)
The property integratedSecurity does not contain a valid boolean value. Only the values true or false can be used. 

  • Was this article helpful?