Passing SQL Server Agent tokens to an Integration Services package

By: Koen Verbeeck | Comments | Related: More > Integration Services Development

Problem

I have a SQL Server Integration Services (SSIS) Package that I need to have a record of the history. When the package is started, it should write a row to a log table stating whether the package was started manually or if the package was started by a SQL Server Agent Job. If it was started by a Job, the name of the Job should be logged as well. Is this possible?

Solution

Using SQL Server Agent Job tokens, it is possible to pass information from SQL Server Agent to a scheduled SSIS package. According to the MSDN page Use Tokens in Job Steps:

SQL Server Agent allows you to use tokens in Transact-SQL job step scripts. Using tokens when you write your job steps gives you the same flexibility that variables provide when you write software programs. After you insert a token in a job step script, SQL Server Agent replaces the token at run time, before the job step is executed by the Transact-SQL subsystem.

The definition is a bit misleading, as the Agent tokens can be used in other types of job steps as well, such as the Integration Services job steps. There is a particular token we are interested in: (JOBID). This token holds the ID of the job - which is a unique identifier - and we will send this to the SSIS package.

SSIS Package Setup

We are going to use a very simple package that just logs a simple message to a log table. The package looks like this:

The package contains a single string variable called JobID. It has a default value of “Manually”. If the package is run inside Visual Studio, this value will be written to the log table and the log will thus indicate that the package was started manually. If the package is started by a job, the variable value will be overwritten by the Agent token.

Inside the Execute SQL Task there is - for the moment - a straight forward INSERT statement that writes the variable JobID to the log table.

DECLARE @LogMessage VARCHAR(4000) = ?; -- the JobID variable maps to this input parameter

INSERT INTO Test.[dbo].[LogMessages](LogMessage)
VALUES(@LogMessage);

Running the package gives the following result:

Configure the SSIS token in SQL Server Agent

In SQL Server Agent, a test job is created that will run the package and pass a token to the JobID variable.

A token can be passed to an SSIS package by setting the property path to the value of the variable. In SQL Server 2005 till SQL Server 2008R2 (or higher when using the package deployment model) this can be done in the Set Values tab. This is described in the article Passing Values into an SSIS Package at Runtime from Outside the Package. When using the project deployment model, a property path can be set in the Advanced tab of the Configuration, as can be seen in the screenshot above. The following property path is used:

\Package.Variables[User::JobID].Value

The following value is passed along:

$(ESCAPE_NONE(JOBID))

Since SQL Server 2005 SP1, every job token must be accompanied by an escape macro. For more information, see the MSDN article Use Tokens in Job Steps.

If we now run the package through the Agent job, the following message is logged:

This means the token was successfully passed along to the SSIS package.

Retrieving the job name

As seen in the previous screenshot, a binary representation (in hexadecimal form) of the job’s unique identifier is logged to the table. It would be more useful if the actual job name was logged of course. However, the binary representation of the GUID – written to the SSIS string variable – doesn’t actually match the GUID of the job TestTokens.

Somehow we have to relate the string 0xB98181ACEB15B34AA66CC4DFD1D298F5 with the GUID AC8181B9-15EB-4AB3-A66C-C4DFD1D298F5 (on your system these will be different values of course). The last 16 characters match, but the first 16 characters are mixed together. This means we should convert the string to a unique identifier. A direct cast is however not possible:

Even converting to VARBINARY doesn’t bring us any closer to the desired result.

The only working option is converting the string value without single quotes to a GUID:

But how can we pass the value of the string without single quotes to the CONVERT function in the Execute SQL Task of the SSIS package? With a little dynamic SQL magic we can achieve our result.

So first we "paste" the string value into the string containing the SQL statement. This SQL statement will fetch the job name out of MSDB. Using the system stored procedure sp_executesql, we can execute this string statement. The job name is stored in the variable @JobNameOUT, which is defined as an output variable (the second parameter of sp_executesql). This output variable is then passed on to the @JobName variable.

The final query for the Execute SQL Statement now becomes:

DECLARE @LogMessage VARCHAR(4000) = ?;
DECLARE @JobName  VARCHAR(100);
DECLARE @SQLStatement NVARCHAR(500);

IF (LEFT(@LogMessage,2) = '0x') -- start of the binary representation, so we have a job GUID
BEGIN
 SELECT @SQLStatement = 
 '
  SELECT @JobNameOUT = [Name]
  FROM msdb.dbo.sysjobs  
  WHERE job_id = CONVERT(UNIQUEIDENTIFIER,' +  @LogMessage + '); 
 '
 EXEC sp_executesql @SQLStatement, N'@JobNameOUT VARCHAR(100) OUTPUT', @JobNameOUT = @JobName OUTPUT;

 -- construct log message
 SET @LogMessage = 'The package was started by the job ' + @JobName + '.';
END
ELSE
BEGIN
 -- construct log message
 SET @LogMessage = 'The package was started manually.';
END
-- log message
INSERT INTO Test.[dbo].[LogMessages](LogMessage)
VALUES(@LogMessage);

This script will take the SSIS variable JobID as input. If the value starts with 0x, we assume a job GUID has been passed and we will fetch the job name using the dynamic SQL. If it doesn’t start with 0x we assume the package has been started manually. An appropriate log message is constructed and at the end the log message is inserted into the log table.

When running the TestTokens job, we can observe the result in the log table:

Conclusion

Using SQL Agent job tokens, we can pass runtime values from the SQL Server Agent Job to SSIS variables. In this tip we explored the use case where custom logging was implemented and the job name was logged to a log table. There are other possible tokens available, such as for example the date the job was started.

Next Steps

Try it out yourself.
More tips about SQL Server Agent Job tokens:
- Providing SQL Agent Job Log Data for Developers
- Customized SQL Agent Job Notifications
A useful tip about custom logging in SSIS.

About the author

Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on MSSQLTips.com. Koen has been awarded the Microsoft MVP data platform award for many years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips