I am using the SQL Loader connector source in IIQ version 8.3p3 to provision accounts to a CSV file. During the remove operation, I am using a DELETE SQL query, which is working as expected for removing data from the CSV file. However, immediately after the delete operation, a number of unnecessary new lines are randomly inserted into the file, despite no carriage return being included in the rule.
I referred to SailPoint’s official documentation at SQL Loader Troubleshooting to resolve the issue(Troubleshooting). The documentation suggests adding a query to the corresponding rule:
String query1 = “pack table table_name”;
stmt.execute(query1);
When I added this query to the rule, it stopped the table from creating unnecessary new lines, but it also removed all existing data in the “Responsibility” column for other identities.
Here is a sample of the issue:
Before running the delete query my csv file was:
Global ID, Responsibility
id1, ent1
id2, ent2
id3, ent3
id4, ent4
If I run the delete query for id3, ent3, the table output after executing the delete and pack queries is as follows:
Global ID, Responsibility
id1,
id2,
id4,
So, while the specified identity and responsibility (id3, ent3) are correctly removed and no extra lines are added, all remaining responsibilities for other identities are also being removed.
Here’s my code snippet for the remove operation which I am performing through Modify Provision Rule:
else if (attributeRequest.getOperation().toString().equalsIgnoreCase("Remove")) {
myLogger.error("Inside operation Remove");
try {
myLogger.debug("Inside Remove operation :");
stmt = connection.createStatement();
myLogger.debug("entitlementName: " + entitlementName);
String query = "DELETE FROM table_name " +
"WHERE [Column1] = '" + accountId + "' " +
"AND Column2 = '" + entitlementName + "';";
myLogger.debug("Executing query: " + query);
stmt.executeUpdate(query);
provResult.setStatus(ProvisioningResult.STATUS_COMMITTED);
myLogger.error("Operation Remove completed");
} catch (SQLException e) {
myLogger.error("SQL exception occurred: ", e);
provResult.setStatus(ProvisioningResult.STATUS_FAILED);
provResult.addError(e);
}
try {
String query1 = "pack table table_name";
stmt.execute(query1);
} catch (SQLException e) {
myLogger.error("SQL exception occurred while removing empty rows: ", e);
provResult.addError(e);
} finally {
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
myLogger.error("Error closing statement: ", e);
}
}
}
}
Has anyone encountered a similar issue or have suggestions to prevent both extra lines and data loss?