Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Passing SQL Server Agent tokens to an Integration Services package


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

Attend these FREE MSSQLTips webcasts >> click to register


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:

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)
VALUES(@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:

 \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:

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

The logging is complete

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. You can download a .zip file with the sample package and with the scripts to create the log table and the Agent job here. The script to create the job uses the project deployment model of SSIS and expects the package in project called MSSQLTips in the MSSQLTips folder in the SSIS catalog on the localhost server. Please modify these values to your environment.
  • More tips about SQL Server Agent Job tokens:
  • A useful tip about custom logging in SSIS.


Last Update:


signup button

next tip button



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools