Job run ok and in SSISDB, but error in Agent

Question

Post reply

Job run ok and in SSISDB, but error in Agent

Henrik Schütze

Ten Centuries

Points: 1004
More actions
January 6, 2015 at 11:43 am

#294059

Hello,
i'm really new on SQL Server 2012.
I created an SSIS package to transfer Data from DB1 on Server A to DB2 on Server A (yes, same server).
This package run very well in BIDS.
The package comes to an error in SQL Server Agent.
But SSISDB say all is perfect.
I see in the DB2 that the data from DB1 is there.
What can i do, to find the error ?
Its horrible the have an job that run into error.
thanx

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 1

What error does the job report when it runs?

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

KenpoDBA SSCrazy Eights Points: 9811 More actions · Answer 2

If you're running it from SSISDB then you can find the errors by right-clicking on the report or project and running the report. It'll show you errors for all runs.

Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE:

Henrik Schütze Ten Centuries Points: 1004 More actions · Answer 3

Alvin Ramard (1/6/2015)
What error does the job report when it runs?

Date07.01.2015 04:48:41

LogJob History (AA1_DDS_03_Transfer_2_RPF)

Step ID17

Serverxxxx

Job NameAA1_DDS_03_Transfer_2_RPF

Step NameDDS_Schnittstelle

Duration00:00:23

Sql Severity0

Sql Message ID0

Operator Emailed

Operator Net sent

Operator Paged

Retries Attempted0

Message

Microsoft (R) SQL Server Execute Package Utility

Version 11.0.3402.0 for 32-bit

Started: 04:48:41

Execution ID: 91728.

To view the details for the execution, right-click on the Integration Services Catalog, and open the [All Executions] report

Started: 04:48:41

Finished: 04:49:04

Elapsed: 22.719 seconds

Date07.01.2015 04:48:41

LogJob History (AA1_DDS_03_Transfer_2_RPF)

Step ID17

Serverxxx

Job NameAA1_DDS_03_Transfer_2_RPF

Step NameDDS_Schnittstelle

Duration00:00:23

Sql Severity0

Sql Message ID0

Operator Emailed

Operator Net sent

Operator Paged

Retries Attempted0

Message

Executed as user: ECS\srv_SQL_Agent_RPF. The step failed.

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 4

What does the All Executions report say?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Henrik Schütze Ten Centuries Points: 1004 More actions · Answer 5

KenpoDBA (1/6/2015)
If you're running it from SSISDB then you can find the errors by right-clicking on the report or project and running the report. It'll show you errors for all runs.

I think. Yes. But there are no errors in the list.

Henrik Schütze Ten Centuries Points: 1004 More actions · Answer 6

Koen Verbeeck (1/7/2015)
What does the All Executions report say?

he say all is perfect. In the logging i found no errors...

I use the "all executions" report t fint the id and use this with the ssisdb itself:

select * from [SSISDB].[internal].[operation_messages]

where operation_message_id > 13110176

and operation_id not in (91289,91285,91290,91291,91288,91284)

and operation_id = 91871

and message_type not in (90,10,20,70,60,200,40,30,400,140)

But i get no errors.... 🙁

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 7

How did you schedule it in the Agent job step? Did you use the SSIS catalog?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 8

Are you sure it failed? It ran for 22 seconds, so it must have done something. Maybe it succeeded and somehow the Agent job says it failed.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Henrik Schütze Ten Centuries Points: 1004 More actions · Answer 9

Koen Verbeeck (1/7/2015)
Are you sure it failed? It ran for 22 seconds, so it must have done something. Maybe it succeeded and somehow the Agent job says it failed.

I see that the step did was expected. The data was transfered.

I have rebuild the whole SQL Server Agent Job. And now it run.

I compare the old and the new job and both are the same.. its strange.. but now it works...

Thanx

greetings

Henrik

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 10

OK, glad you got it resolved and thanks for posting back.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP