Problem:
I’m requesting access for an account on SQL Loader source from ISC. The SQL loader source is connected to a text file on a remote server. For the first request where the account is created, the file looks perfect where a new entry is added with the user record. However, when requested additional access or make changes to the account from SailPoint, below issues are being identified:
The connector writes the account data in the same line in continuation with the previous record.
The account updates cause the connector to write extra spaces into the file leading to the file having unnecessary spaces.
I already went through the below documentation and discuss post, but they were not so useful:
Resolution – To avoid adding spaces into the CSV or text files while performing provisioning operations, add the following query in the corresponding rule:
Hey @RobBuskens Thanks for the response. I saw this statement in the troubleshooting section of the connector document as well.
Can you help me understand where exactly this needs to be added in the JDBC Provision Rule? I tried adding it but it kept saying “Unspecified Connector Failure” so I think I’m missing the information on when and where this snippet must be placed in the rule.
Probably a sample rule where you have these ‘pack table’ related statements might help us a lot!
@RAKRHEEM, I know exactly how to write a JDBC Provision rule and the rule code that I’ve written works perfectly fine for create account/add entitlement/remove entitlement/disable accounts.
To re-iterate my question, I wanted to see how the PACK TABLE <table_name> related code snippet is incorporated into the rule because once I have statement.executeUpdate() in my code for provisioning related operations and I place the snippet there, the rule simply doesn’t work. Versus when I don’t have PACK TABLE <table_name> in my rule, all the use cases work fine but the extra spaces get added into the text file in the remote server.
Sure Rob, will try this and see how it goes. I tried couple of places where I placed this code snippet inside a method call where the account updates were being done and it said “Unspecified Connector Failure”.
Let me try something different and keep you posted.
@RobBuskens, Below is the blueprint of my rule. I have defined a method executePackQuery to handle the pack statements related execution. When I run this rule, there is no issue with the provisioning to the file. However, the spaces still do exist in the file after provisioning. Due to these spaces, it is causing me issues during deprovisioning where I want to delete the user records from the file, the connector is unable to determine that the records do exist towards the end of the file after the spaces, resulting in incorrect deprovisioning actions on the file.
If you see the above code, I have placed executePackQuery(connection); to call the execute pack statement related method which is right before returning back to rule. Still does not help in cleaning up the garbage spaces in file.
Can you review and let me know of any issues here?
@Arshad I have been using Sailpoint IIQ and we have written below code in After Provisioning rule to handle blank lines that are added during update or delete operation.
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.util.ArrayList;
import java.util.List;
import sailpoint.tools.Util;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
//Code to remove emptylines caused in delete operation
String filePath;
String url = (String) application.getAttributes().get("url");
String[] parts = url.split("////");
if (parts.length >= 1) {
filePath = parts[1];
}
try {
if(Util.isNotNullOrEmpty(filePath))
{
log.debug("filePath"+filePath);
List lines = Files.readAllLines(Paths.get(filePath));
List nonEmptyLines = new ArrayList();
for (String line : lines) {
if (!line.trim().isEmpty()) {
nonEmptyLines.add(line);
}
}
Files.write(Paths.get(filePath), nonEmptyLines);
log.debug("Empty lines removed successfully.");
}
} catch (IOException e) {
e.printStackTrace();
}
//End of code to remove empty line