Linux WPA feeding a MS SQL Server Warehouse

With the release of ITM FP2, we gained the capability to run the Warehouse Proxy Agent (WPA) on Linux. This article will focus on configuring a Linux WPA to connect to a Microsoft SQL Server Data Warehouse but the steps can be applied to Oracle and DB2 as well.

As you may have guesed, the Linux WPA uses JDBC. While many are aware of JDBC drivers for Oracle and DB2, they may have missed the fact the Microsoft has released Unix/Linux JDBC Drivers.

Download and install (default install location is /opt/msSQLjdbc) the drivers.

Next install the WPA on Linux using the install.sh script from the installation package and configure using the MTEMS application. No, there is no CLI method short of hacking the files.

You will notice that the configuration GUI looks just like the SPA config. Good example of Code Reuse ;-)

Add the SQL Server jar files to the JDBC Drvers section:
/opt/msSQLjdbc/libmsbase.jar
/opt/msSQLjdbc/libmssqlserver.jar
/opt/msSQLjdbc/libmsutil.jar

Next edit the Warehouse URL section being sure to set the hostname, port (default 1433), and the database name (Warehous).

Test the connection.

If it works, start the WPA. If it fails, well figure out what you did wrong.