CODEWITHSUNDEEP
sqlssissql-server-data-toolssqlpackage
TD2013
TD2013

Reputation: 97

SSIS Job , passing the value in job steps

I have a SSIS job with multiple steps, each steps is from different package or project. When I execute the job , I write log in db with a guid, but each guid is created in its package or project. I need to update the same value for all the steps , kind of link all the steps together with one value/ID. Any suggestions how to do this?

Upvotes: 0

Views: 860

Answers (1)

billinkc
billinkc

Reputation: 61231

SQL Agent jobs do not allow for values to be passed in to job steps.

But, you can rethink how your current invocation of SSIS packages works to meet your goals.

What if you added a precursor step to your SQL Agent job? Type of SQL Task and use that to generate the GUID you'd like for your packages to share. Store it into either a 1 row table or create a key/value style historical table.

CREATE TABLE dbo.CorrelateAgentToSSIS
( 
    jobid uniqueidentifier NOT NULL
,   runid uniqueidentifier NOT NULL
,   insert_date datetime NOT NULL CONSTRAINT DF__CorrelateAgentToSSIS__insert_date DEFAULT (GETDATE())
);

4 columns there. The first will be the guid an instance of SQL Server Agent generates. The second column is your tracking guid.

Step 0 would look something like

declare @jobid uniqueidentifier =  CONVERT(uniqueidentifier, $(ESCAPE_NONE(JOBID)))
-- Populate this however it needs to be done
,    @myguid uniqueidentifier = newid()

Your job steps for SSIS will change a bit. Instead of using the native SSIS jobstep type, you're going to use the TSQL type and do something like this.

DECLARE @execution_id bigint
, @jobid uniqueidentifier = CONVERT(uniqueidentifier, $(ESCAPE_NONE(JOBID)));
DECLARE  @runid uniqueidentifier = (SELECT TOP 1 runid FROM dbo.CorrelateAgentToSSIS AS CATS WHERE CATS.jobid = @jobid);


EXEC SSISDB.catalog.create_execution
    @package_name = N'SomePackage.dtsx'
,   @execution_id = @execution_id OUTPUT
,   @folder_name = N'MyFolder'
,   @project_name = N'MyProject'
,   @use32bitruntime = False
,   @reference_id = NULL;

-- ddl left as exercise to the reader
INSERT INTO dbo.RunToSSIS
SELECT
    @run
,   @execution_id;

DECLARE @var0 smallint = 1;

EXEC SSISDB.catalog.set_execution_parameter_value
    @execution_id
,   @object_type = 50
,   @parameter_name = N'LOGGING_LEVEL'
,   @parameter_value = @var0;

-- This assumes you have a parameter defined in SSIS packages to receive the 
-- runid guid
EXEC SSISDB.catalog.set_execution_parameter_value
    @execution_id
,   @object_type = 50
,   @parameter_name = N'RUN_ID'
,   @parameter_value = @runid;

EXEC SSISDB.catalog.start_execution
    @execution_id;
GO

Finally, while you're collecting metrics, you might also want to think about linking a job run to the data the packages collect in the SSISDB. You can bridge that gap by recording the jobid/runid to a bigint of execution_id. If you're running packages from the SSISDB, you can plumb in System variable ServerExecutionID. I do this in the first step of every package with an Execute SQL Task. In packages run from VS, the value is 0. Otherwise, it's the value you see in SSISDB.catalog.operations Knowing those three things will allow you to see how the Agent job did, correlate it to your custom guid and whatever metrics you collect and you can pull apart performance data from the SSIS catalog.

https://dba.stackexchange.com/questions/13347/get-job-id-or-job-name-from-within-executing-job https://dba.stackexchange.com/questions/38808/relating-executioninstanceguid-to-the-ssisdb

Upvotes: 1

Related Questions