Passing SQL Server Agent tokens to an Integration Services package

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


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?


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:

Our simple test package

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.

There can only be one variable

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)

Running the package gives the following result:

Log message

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.

Configuring the job token

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:


The following value is passed along:


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:

Log message

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.

The GUIDs of the existing jobs

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:

Conversion error

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

Varbinary seems to be useless

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

Converting 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.

Dark magic happens here

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) = ?;

IF (LEFT(@LogMessage,2) = '0x') -- start of the binary representation, so we have a job GUID
 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 + '.';
 -- construct log message
 SET @LogMessage = 'The package was started manually.';
-- log message
INSERT INTO Test.[dbo].[LogMessages](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:

The logging is complete


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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Koen Verbeeck 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 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

Comments For This Article

get free sql tips
agree to terms