July 6, 2012 at 11:43 am
We kick off DTSx packages using a master/child concept. The packages are also run in SQL-jobs as steps, because we are mostly using DTEXEC, data is missing when some jobs ran.
For instance after we kick off a group of "Child" packs outside of the agent we need to tell a job it was ran successfully on this day/time Does anyone know how to add job history via T-SQL..?
July 6, 2012 at 12:09 pm
I don't know how you can add an entire job history, but you can check the success / failure of a job and its individual steps by querying against sysjobs, sysjobsteps,and sysjobhistory.
Test your code thoroughly when doing this, though. If a job runs multiple times a day, then you need to get the max run time and compare it to the max step run at that point, rather than grabbing the max step and hoping you got the correct run time.
July 6, 2012 at 1:31 pm
While I belive you can insert into the job history tables (they're not system tables), it would probably be better if you created your own history tables for the packages.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 9, 2012 at 4:21 am
GilaMonster (7/6/2012)
While I belive you can insert into the job history tables (they're not system tables), it would probably be better if you created your own history tables for the packages.
I would definitely not recommend inserting into the job history tables. Gail is right, create your own if you actually need to insert the history somewhere.
July 9, 2012 at 11:22 am
Thanks for the replies. The main issue is we have executives who, for politcal reasons have access to create/run DTS(x) packs, and are now SSIS savy, running them on schedules. We are moving away to calling packages within packages(master/child) because of how many there are.
If we call the packs then jobs dont know if/when they last ran, and users could force run them when "job monitor" doesnt show runtimes, we have tables that log this but the exec's dont know how to query directly, and we wont grant access to the database controlling automation.
We do need some alternative that bridges the two worlds exposing histories to both groups, and was hoping to find an unpublished sproc to add records to the history table, or some other alternative thats seemeless. If you have any alternate ideas I'm all eyes 🙂
July 9, 2012 at 11:37 am
SSRS and reports that the execs can run that show them history from the custom history tables?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 10, 2012 at 7:44 am
Are your execs logging into Integration Services and executing the package from the Stored Packages folder? Or are they running them from the child jobs?
July 13, 2012 at 6:42 pm
Hello, sorry for the late reply.
They have jobs they kick off from the "Sql Agent" which run thier packages, sometimes they run one pack, or the whole job(a bunch of packs). We are moving to master calling children. The jobs Execs normally run are the children packs.
If we schedule the jobs to start those jobs will only know they are running a "Master" job, the Sql Agent tables wont know that any of these child packs ran unless we find an alternative to log them, or cram them into Sysjobhistory. I do know they check the agent if the job ran, if it didnt the Execs will run them again. Problem is, if we ran the job thru automation they wont see it, and can start the job not knowing our automation successfully ran all thier packages already in some cases that would be distastrous. Easiest route is to log each child into the agent history because they already check there normally.
July 16, 2012 at 7:23 am
Don't have the packages kick off child packages. Have the SQL Job run each individual package (even the "child packages") as individual steps that only run if the "parent" package succeeded.
EDIT: Or, if I completely misunderstood what you are saying about this, have the parent job kick off a child job. Then the execs should be able to look at the individual job history for their pet packages and see that it was run today.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply