Tips and Tricks to Growing Your SQL Server Estate from SMB to Enterprise Scale
Thursday, June 28, 2018 - click here to learn more
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:
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:
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:
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:
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.
- 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: 2015-03-17
About the author
View all my tips