Job Execution Issue - SQL Server 2005

All,
I have a job set up to automatically execute an SSIS package. The
package itself, when ran, works fine. But when the job runs, it fails.
I looked at the sysjobhistory table in the 'msdb' database to see what
the error was. There were two entries:
1) The job failed. The Job was invoked by User DOMAIN\Administrator.
The last step to run was step 1 (Execute LBL Package).
2) Executed as user: DOMAIN\System. The package execution failed. The
step failed.
Ok, so then I went into services and changed the SQLSERVERAGENT to run
as "Administrator" instead of "System".
So, I ran the job again. Got an error. Here's what the sysjobhistory
table said this time:
1) The job failed. The Job was invoked by User DOMAIN\Administrator.
The last step to run was step 1 (Execute LBL Package).
2) Executed as user: DOMAIN\Administrator. The package execution
failed. The step failed.
WTF'!?
Thanks in advance,
JeremyProbably best asked over in the dts forum.
You need some more detail, turn on logging, but could it have something to
do with the "protection levels" of SSIS packages? Can you run it from the
command line on the same machine where it fails? Was it developed on a
different machine from where it fails? Look into how SSIS encrypts
"sensitive information" (mostly passwords), and how that has to be handled
for deployment. Also look to see who owns the job, there's something about
job owner and cmd steps, or something like that.
I've been having the same fun over the past few days. Got mine working, but
not at all certain I could enumerate all the hurdles passed.
Josh
"jrcapp@.cre8iveweb.com" wrote:
> All,
> I have a job set up to automatically execute an SSIS package. The
> package itself, when ran, works fine. But when the job runs, it fails.
> I looked at the sysjobhistory table in the 'msdb' database to see what
> the error was. There were two entries:
> 1) The job failed. The Job was invoked by User DOMAIN\Administrator.
> The last step to run was step 1 (Execute LBL Package).
> 2) Executed as user: DOMAIN\System. The package execution failed. The
> step failed.
> Ok, so then I went into services and changed the SQLSERVERAGENT to run
> as "Administrator" instead of "System".
> So, I ran the job again. Got an error. Here's what the sysjobhistory
> table said this time:
> 1) The job failed. The Job was invoked by User DOMAIN\Administrator.
> The last step to run was step 1 (Execute LBL Package).
> 2) Executed as user: DOMAIN\Administrator. The package execution
> failed. The step failed.
>
> WTF'!?
> Thanks in advance,
> Jeremy
>|||Thanks for the reply, Josh! I'll play around w/some of your
suggestions and get back to you.
Thanks,
Jeremy

Job Execution Issue - SQL Server 2005

All,
I have a job set up to automatically execute an SSIS package. The
package itself, when ran, works fine. But when the job runs, it fails.
I looked at the sysjobhistory table in the 'msdb' database to see what
the error was. There were two entries:
1) The job failed. The Job was invoked by User DOMAIN\Administrator.
The last step to run was step 1 (Execute LBL Package).
2) Executed as user: DOMAIN\System. The package execution failed. The
step failed.
Ok, so then I went into services and changed the SQLSERVERAGENT to run
as "Administrator" instead of "System".
So, I ran the job again. Got an error. Here's what the sysjobhistory
table said this time:
1) The job failed. The Job was invoked by User DOMAIN\Administrator.
The last step to run was step 1 (Execute LBL Package).
2) Executed as user: DOMAIN\Administrator. The package execution
failed. The step failed.
WTF'!?
Thanks in advance,
JeremyProbably best asked over in the dts forum.
You need some more detail, turn on logging, but could it have something to
do with the "protection levels" of SSIS packages? Can you run it from the
command line on the same machine where it fails? Was it developed on a
different machine from where it fails? Look into how SSIS encrypts
"sensitive information" (mostly passwords), and how that has to be handled
for deployment. Also look to see who owns the job, there's something about
job owner and cmd steps, or something like that.
I've been having the same fun over the past few days. Got mine working, but
not at all certain I could enumerate all the hurdles passed.
Josh
"jrcapp@.cre8iveweb.com" wrote:

> All,
> I have a job set up to automatically execute an SSIS package. The
> package itself, when ran, works fine. But when the job runs, it fails.
> I looked at the sysjobhistory table in the 'msdb' database to see what
> the error was. There were two entries:
> 1) The job failed. The Job was invoked by User DOMAIN\Administrator.
> The last step to run was step 1 (Execute LBL Package).
> 2) Executed as user: DOMAIN\System. The package execution failed. The
> step failed.
> Ok, so then I went into services and changed the SQLSERVERAGENT to run
> as "Administrator" instead of "System".
> So, I ran the job again. Got an error. Here's what the sysjobhistory
> table said this time:
> 1) The job failed. The Job was invoked by User DOMAIN\Administrator.
> The last step to run was step 1 (Execute LBL Package).
> 2) Executed as user: DOMAIN\Administrator. The package execution
> failed. The step failed.
>
> WTF'!?
> Thanks in advance,
> Jeremy
>|||Thanks for the reply, Josh! I'll play around w/some of your
suggestions and get back to you.
Thanks,
Jeremy

Job Execution Issue - SQL Server 2005

All,
I have a job set up to automatically execute an SSIS package. The
package itself, when ran, works fine. But when the job runs, it fails.
I looked at the sysjobhistory table in the 'msdb' database to see what
the error was. There were two entries:
1) The job failed. The Job was invoked by User DOMAIN\Administrator.
The last step to run was step 1 (Execute LBL Package).
2) Executed as user: DOMAIN\System. The package execution failed. The
step failed.
Ok, so then I went into services and changed the SQLSERVERAGENT to run
as "Administrator" instead of "System".
So, I ran the job again. Got an error. Here's what the sysjobhistory
table said this time:
1) The job failed. The Job was invoked by User DOMAIN\Administrator.
The last step to run was step 1 (Execute LBL Package).
2) Executed as user: DOMAIN\Administrator. The package execution
failed. The step failed.
WTF?!?
Thanks in advance,
Jeremy
Probably best asked over in the dts forum.
You need some more detail, turn on logging, but could it have something to
do with the "protection levels" of SSIS packages? Can you run it from the
command line on the same machine where it fails? Was it developed on a
different machine from where it fails? Look into how SSIS encrypts
"sensitive information" (mostly passwords), and how that has to be handled
for deployment. Also look to see who owns the job, there's something about
job owner and cmd steps, or something like that.
I've been having the same fun over the past few days. Got mine working, but
not at all certain I could enumerate all the hurdles passed.
Josh
"jrcapp@.cre8iveweb.com" wrote:

> All,
> I have a job set up to automatically execute an SSIS package. The
> package itself, when ran, works fine. But when the job runs, it fails.
> I looked at the sysjobhistory table in the 'msdb' database to see what
> the error was. There were two entries:
> 1) The job failed. The Job was invoked by User DOMAIN\Administrator.
> The last step to run was step 1 (Execute LBL Package).
> 2) Executed as user: DOMAIN\System. The package execution failed. The
> step failed.
> Ok, so then I went into services and changed the SQLSERVERAGENT to run
> as "Administrator" instead of "System".
> So, I ran the job again. Got an error. Here's what the sysjobhistory
> table said this time:
> 1) The job failed. The Job was invoked by User DOMAIN\Administrator.
> The last step to run was step 1 (Execute LBL Package).
> 2) Executed as user: DOMAIN\Administrator. The package execution
> failed. The step failed.
>
> WTF?!?
> Thanks in advance,
> Jeremy
>
|||Thanks for the reply, Josh! I'll play around w/some of your
suggestions and get back to you.
Thanks,
Jeremy

Job Does Not Execute Correctly

Hi everyone,
I have a management job which runs every minute automatically. However recently I discovered that the result was not executed correctly even the job history tells me it runs successfully. On the other hand, the result was executed correctly (i.e. what I expect) when I try to start the job manually.
Any idea what issue does it related to? I doubt if it is related to any security context but cannot prove it yet. Thanks for any idea.Hi,
As you say u execute job every 1 minute, What does the job
do and what is the expected time period of the job
completion.
Regards
Thirumal
www.thirumal.com
>--Original Message--
>Hi everyone,
>I have a management job which runs every minute
automatically. However recently I discovered that the
result was not executed correctly even the job history
tells me it runs successfully. On the other hand, the
result was executed correctly (i.e. what I expect) when I
try to start the job manually.
>Any idea what issue does it related to? I doubt if it is
related to any security context but cannot prove it yet.
Thanks for any idea.
>.
>|||Thirumal,
Thanks for your reply. The job executes within just few seconds, wont' be too long.
The job retreives several records from tables and then insert records into another table based on a if-else condition.
The code is like this:
DECLARE @.thisMin varchar(25)
DECLARE @.table_no varchar(2), @.table_tmp varchar(2)
SET @.thisMin = GETDATE()
SET @.table_no = DATEDIFF(dd, ''01/01/01'', @.thisMin) % 36
SET @.table_tmp = DATEDIFF(dd, ''01/01/01'', DATEADD(dd, -1, @.thisMin)) % 36
EXEC(''
DECLARE temp_cur CURSOR FOR
SELECT DISTINCT alertId FROM z_EventHistoryD''+@.table_no+'' WHERE
DATEDIFF(mi, endTime, '''+@.thisMin+''') = 0 OR
DATEDIFF(mi, endTime, '''+@.thisMin+''') = 1
GROUP BY alertId, severitySortOrder
HAVING COUNT(alertId) = 1
DECLARE @.alertId int, @.thisSSO int, @.lastSSO int, @.auditTime datetime
OPEN temp_cur
FETCH NEXT FROM temp_cur INTO @.alertId
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.thisSSO = NULL
SET @.lastSSO = NULL
SET @.auditTime = NULL
IF DATEPART(hh, '''+@.thisMin+''')=0 AND DATEPART(mi, '''+@.thisMin+''')=0
SELECT @.lastSSO = severitySortOrder, @.auditTime = endTime
FROM z_EventHistoryD''+@.table_tmp+'' WHERE alertId = @.alertId
AND DATEDIFF(mi, endTime, '''+@.thisMin+''') = 1
ELSE
SELECT @.lastSSO = severitySortOrder, @.auditTime = endTime
FROM z_EventHistoryD''+@.table_no+'' WHERE alertId = @.alertId
AND DATEDIFF(mi, endTime, '''+@.thisMin+''') = 1
SELECT @.thisSSO = severitySortOrder, @.auditTime = endTime
FROM z_EventHistoryD''+@.table_no+'' WHERE alertId = @.alertId
AND DATEDIFF(mi, endTime, '''+@.thisMin+''') = 0
IF (@.lastSSO IS NULL)
--PRINT ''''-- NEW EVENT''''
INSERT INTO alertAudit VALUES (@.alertId, NULL, @.thisSSO, 1, @.auditTime)
ELSE
IF (@.thisSSO IS NULL)
--PRINT ''''-- ENDING EVENT''''
INSERT INTO alertAudit
SELECT alertId, @.lastSSO, NULL, -1, clearTime FROM alertHistory WHERE alertId = @.alertId
ELSE
IF (@.thisSSO <> @.lastSSO)
--PRINT ''''-- CHANGE SEVERITY EVENT''''
INSERT INTO alertAudit VALUES (@.alertId, @.lastSSO, @.thisSSO, 2, @.auditTime)
ELSE
IF (@.thisSSO = @.lastSSO)
PRINT ''''-- UNCHANGED SEVERITY''''
ELSE
PRINT ''''-- UNKNOWN ERROR''''
/*--DEBUG
PRINT @.alertId
PRINT @.auditTime
PRINT @.lastSSO
PRINT @.thisSSO
--DEBUG*/
FETCH NEXT FROM temp_cur INTO @.alertId
END
CLOSE temp_cur
DEALLOCATE temp_cur
'')
Thanks again
"Thirumal" wrote:
> Hi,
> As you say u execute job every 1 minute, What does the job
> do and what is the expected time period of the job
> completion.
> Regards
> Thirumal
> www.thirumal.com
> >--Original Message--
> >Hi everyone,
> >
> >I have a management job which runs every minute
> automatically. However recently I discovered that the
> result was not executed correctly even the job history
> tells me it runs successfully. On the other hand, the
> result was executed correctly (i.e. what I expect) when I
> try to start the job manually.
> >
> >Any idea what issue does it related to? I doubt if it is
> related to any security context but cannot prove it yet.
> Thanks for any idea.
> >.
> >
>