CODEWITHSUNDEEP
sqlsql-server-2008t-sqlssis
Alireza K.
Alireza K.

Reputation: 29

Finding an associated job with a package

So I have created a simple job in the Sql Server Agent that runs a SSIS package every 5 minutes, however I have been struggling to find the relationship between the Sql Server Agent job and its associated Package, Since there is no job_id in the dbo.sysssislog table that connects the job with a specific package. I either want to retrieve the job_id in the SSIS package so I could log it in the Database somewhere or a simple query in the SQL Server which defines the relationship between the two

Any suggestions or help would be greatly appreciated

Upvotes: 0

Views: 110

Answers (1)

billinkc
billinkc

Reputation: 61269

There is no relationship. I can run an SSIS package via dtexec via xp_cmdshell which will still log to sysssislog but there wouldn't be a job_id associated to it.

You might be able to tie the job step start time to the package's PackageStart event but it's going to be imprecise as there might be a delay in the executable actually starting. Plus, the stupid way they store date and time separately in the sys job tables.

Come 2012 and the project deployment model, you can actually access a system Variable that is the package execution id (they call it differently) when it was ran on the server.

Upvotes: 3

Related Questions