Issues and fix - Loading MS SQL data using EPM Integration Agent!

At a high level we know to connect to SQL Server using JDBC in the EPM Integration Agent, you need to follow these general steps:

Download the JDBC Driver: First, you need to download the Microsoft JDBC Driver for SQL Server from the official Microsoft website. Make sure to choose the appropriate version of the driver that matches your SQL Server version and Java version.

Install the JDBC Driver: Once downloaded, extract the JDBC driver files from the downloaded package and place them in a location accessible to your EPM Integration Agent. This could be a directory on your system or within the EPM Integration Agent's installation directory.

Configure the EPM Integration Agent: Within the EPM Integration Agent, configure the JDBC driver settings to point to the location where you placed the JDBC driver files. This typically involves specifying the driver class name, JDBC URL, and any additional properties required for the connection.

Provide Connection Details: Specify the connection details for your SQL Server database within the EPM Integration Agent. This includes the server address, port number, database name, authentication credentials (username and password), and any other required connection parameters.

Test the Connection: Once configured, test the JDBC connection to ensure that the EPM Integration Agent can successfully connect to your SQL Server database. This helps verify that the configuration settings are correct and that the JDBC driver is functioning properly.

Define Data Source and Load Process: Proceed to define the data source from which you want to extract data and the process for loading it into the SQL Server database. This involves mapping data fields, specifying transformation logic, and executing the load process within the EPM Integration Agent.

Verify Data: After the data loading process is complete, verify that the data has been successfully loaded into the SQL Server database and that it appears as expected.

It's important to consult the documentation provided with your specific version of the EPM Integration Agent for detailed instructions on configuring JDBC connections and loading data into SQL Server. Additionally, ensure that you have the necessary permissions and access rights to connect to and perform data loading operations on the SQL Server database.

Let’s focus on some of the common issues that might occur and the resolution for the same.

 1. Using an old Integration Agent as compared to the EPM Cloud version.

Error message.

2024-03-11 06:49:52,102 FATAL [AIF]: Error in FileData.extractDataFromSource

Traceback (most recent call last):

  File "<string>", line 155, in extractDataFromSource

  File "<string>", line 447, in executeAdapterScript

  File "<string>", line 488, in executeJythonScript

  File "/u01/test/PBCS_24.03.62/EPMSystem11R1/products/FinancialDataQuality/bin/plugin/OnPremDBExtractData.py", line 47, in <module>

    raise Exception(str(sys.exc_info()[1]).encode("utf-8"))

Exception: java.lang.RuntimeException: EPM agent failed to extract data.

 

Solution:

Install and use the latest version of the EPM Integration Agent.

 

2. Issues related to the encryption & server certificate.

Error message.

Mar-13 07:48:09:135 :: --------------- Executing Custom Event :BefExtract ---------------

Mar-13 07:48:09:230 :: Error: "encrypt" property is set to "true" and "trustServerCertificate" property is set to "false" but the driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption: Error: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target. ClientConnectionId:64c6776d-80b1-654fhgzagfhf-6e9e213be4fa

Mar-13 07:48:09:231 :: Error: com.microsoft.sqlserver.jdbc.SQLServerException: "encrypt" property is set to "true" and "trustServerCertificate" property is set to "false" but the driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption: Error: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target. ClientConnectionId:64c6776d-80b1-4e88-914f-6e9e213be4fa

             at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:4266)

             at com.microsoft.sqlserver.jdbc.TDSChannel.enableSSL(IOBuffer.java:1965)

 

Solution:

Specifically, in the agentparams.ini file:

NTLM_PROXY_AUTH=Y     (Changed to ‘Y’ – this was N by default)

jdbc:sqlserver://ServerName:1433;DatabaseName=DBName;encrypt=true;trustServerCertificate=true; 

add the bold ones to the URL. 

Happy days on the cloud!!!