In this blog post, I will demonstrate a technique to query the status of a SQL Server Agent job that executes the SSIS package. SQL Server Agent scripts are asynchronous in nature. This can make it difficult to make code decisions based on the execution status of a script of this type.
Originally published on Keyholesoftware.com by Josh Green on 12/8/22.
Background
In this example, we’ll be using Java. I’ve been assisting a recent client in renovating their legacy Model 204 (M204) IBM mainframe codebase, building a more elegant distributed package instead. One relic of the mainframe days is a function named Fast Unloads, ironically nicknamed “Funloads.”
These Funloads can best be described as ETL transactions. There may be a file with millions of lines of data that needs to be uploaded to a SQL table or a massive SQL result set that needs to be written to a file. These downstream files may be used by an M204 or COBOL program as input for processing.
In the distributed world, these Funloads have been translated to SQL and wrapped within an SQL Server Agent call to an SSIS package. This process has been baked into a Spring Batch job as a job step to mimic mainframe JCL or job code.
The only drawback to this plan is the asynchronous nature of the SQL Server Agent job. The Java/Spring Batch code could start the process, but the Batch job is not inherently smart enough to wait for the output file before moving on to subsequent steps that may need that file as a dependency.
Let’s begin with the call to execute the SSIS package.
SQL Server Agent Job Call
In the code below we pass in the name of the SQL Server Agent job to the function as funloadName. This kicks off the SQL Server Agent job to then execute an SSIS package. The SSIS package runs a query and then writes a dataset out to a file.
protected void executeSsisFunload(String funloadName) {
LOGGER.info("Funload name [" + funloadName + "]");
try (Connection con = DriverManager.getConnection(establishSsisConnection()); Statement stmt = con.createStatement();) {
String sql = "EXEC msdb.dbo.sp_start_job N'" + funloadName + "'";
LOGGER.info("Expected SQL [" + sql + "]");
stmt.execute(sql);
}
// Handle any errors that may have occurred.
catch (SQLException e) {
LOGGER.info("Funload [" + funloadName + "] failed", e);
}
}
Checking The Status Of The SSIS Package
The next portion of the code is arguably the most important. This code runs an SQL query against an internal SSIS database that contains information related to each package execution.
Using this query, we ask the job control tables for the status of our specific running package. Based on the results of this query we either (a) wait 20 seconds and try again, or (b) continue on to the next step in our Spring Batch job.
The looping mechanism is essential because of the unexpected duration of the SSIS package to run to completion. It was discovered early on that a downstream process was beginning to FTP the output file from our SSIS package before it was finished. This query and looping mechanism prevents any FTP actions before the file is completely written.
protected void checkForFunloadFile(String funloadName) {
StringBuilder ssisStatusString = new StringBuilder();
ssisStatusString.append("SELECT ");
ssisStatusString.append("EXECUTION_ID ");
ssisStatusString.append(", FOLDER_NAME ");
ssisStatusString.append(", PROJECT_NAME ");
ssisStatusString.append(", PACKAGE_NAME ");
ssisStatusString.append(", ENVIRONMENT_NAME ");
ssisStatusString.append(", EXECUTED_AS_NAME ");
ssisStatusString.append(", START_TIME ");
ssisStatusString.append(", END_TIME ");
ssisStatusString.append(", CASE STATUS WHEN 1 THEN 'CREATED'");
ssisStatusString.append("WHEN 2 THEN 'RUNNING' ");
ssisStatusString.append("WHEN 3 THEN 'CANCELLED' ");
ssisStatusString.append("WHEN 4 THEN 'FAILED' ");
ssisStatusString.append("WHEN 5 THEN 'PENDING' ");
ssisStatusString.append("WHEN 6 THEN 'ENDED UNEXPECTEDLY' ");
ssisStatusString.append("WHEN 7 THEN 'SUCCEEDED' ");
ssisStatusString.append("WHEN 8 THEN 'STOPPING' ");
ssisStatusString.append("ELSE 'COMPLETED' END AS STATUS ");
ssisStatusString.append(", CALLER_NAME ");
ssisStatusString.append(", PROCESS_ID ");
ssisStatusString.append(", SERVER_NAME ");
ssisStatusString.append("FROM INTERNAL.EXECUTION_INFO ");
ssisStatusString.append("WHERE PACKAGE_NAME = '" + funloadName + ".DTSX' ");
ssisStatusString.append("AND STATUS = 7");
// Declare integer to
int ssisResultInteger = 1;
if (ssisResultInteger == 1) {
try (Connection con = DriverManager.getConnection(establishSsisConnection()); Statement stmt = con.createStatement();) {
LOGGER.info("Expected SQL [" + ssisStatusString.toString() + "]");
ResultSet ssisStatusResult = stmt.executeQuery(ssisStatusString.toString());
ssisResultInteger = ssisStatusResult.getRow();
LOGGER.info("Result Integer = [" + ssisResultInteger + "]");
if (ssisResultInteger >= 1) {
LOGGER.info("The current funload [" + funloadName + " has not finished. Looping again...");
try {
// Wait 20 seconds
Thread.sleep(20000);
} catch (InterruptedException e) {
e.printStackTrace();
}
}
}
// Handle any errors that may have occurred.
catch (SQLException e) {
LOGGER.info("Funload [" + funloadName + "] failed", e);
}
}
}
Final Steps
The output file from the SSIS package is created in a mounted NFS drop location. Once the query returns no results, we can move to the next step of our job with a fully written file. Nice work!
In Conclusion
The asynchronous nature of some processes can be difficult to wrestle with in a code structure dependent on return codes and exit statuses such as Spring Batch. Above is the process that worked best for me!
I’m sure there are many ways to tackle this problem, so feel free to drop a comment with your thoughts! If you like what you read, be sure to check out the Keyhole Dev Blog for tons of other great articles.
Top comments (0)