October 2, 2015 at 7:43 am
I have a simple package that is run from SQL Server agent. It needs to access the jobid of the job running the package. So, I declared a variable in the package called JobID and set its type to Object (there is no uniqueidentifier or GUID variable type). In the SQL Server Agent job step, I set up the values so that the arguments passed to DTEXEC look like this:
/SQL "\Test\TestParentVariables" /SERVER "CRSDDVCP01CRU.d2-tdbfg.com,3341" /CHECKPOINTING OFF
/SET "\Package.Variables[User::JobID].Properties[Value]";"$(ESCAPE_NONE(JOBID))" /REPORTING E
However, when I run my job, it fails with:
DTExec: Could not set \Package.Variables[User::JobID].Properties[Value] value to 0x2015CE514B78B64CB950B7504659E313.
The value in the error message is indeed the job id of the SQL Server Agent job. Any ideas what I can do to get the jobid passed to the package?
Gerald Britton, Pluralsight courses
October 2, 2015 at 7:45 am
Here you go:
Passing SQL Server Agent tokens to an Integration Services package[/url]
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 2, 2015 at 8:01 am
Thanks Koen! dunno where I went wrong the first time or second. Third time lucky!
Gerald Britton, Pluralsight courses
October 2, 2015 at 8:05 am
g.britton (10/2/2015)
Thanks Koen! dunno where I went wrong the first time or second. Third time lucky!
Great, glad you got it fixed 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 2, 2015 at 8:25 am
The real drag is that there is no token (JOBNAME). That would be handy. If you want the job name in several packages called from different jobs, you have to include the lookup code in each of those packages. Even if you embed the lookup in a utility package or store procedure, you still have to invoke it in each package that wants the job name.
DRY violation!
Gerald Britton, Pluralsight courses
October 5, 2015 at 4:12 am
g.britton (10/2/2015)
The real drag is that there is no token (JOBNAME).
That would be awesome indeed.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply