SSIS Job Failed

SQL Server Integration Services

renching


I created a simple SSIS package. I build the package. I can run the package with no problem.

However, when I schedule a job, the job always fail. Does anyone know what is the problem

The error message of the job doesn't help at all. Here is the error message I got from job history:

Date 5/19/2006 2:02:21 PM
Log Job History (ssistest)

Step ID 0
Server ABC
Job Name ssistest
Step Name (Job outcome)
Duration 00:00:01
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
The job failed. The Job was invoked by User CHICAGO\ABC. The last step to run was step 1 (ssistest).

I tried to enable logging of my SSIS. I didn't see any additional message.

Does anyone know the problem Thanks.

Renching




Top

Re: SSIS Job Failed

Tianyu Li


Obvious not enough information here.

One suggestion, enable SQL logging. :)



Top

Re: SSIS Job Failed

renching

How do I enable SQL logging I enabled SSIS logging. I see addition information when I run the package. However, I didn't see any additional information when I run the job.

Please let me know how I can see more messages. Thanks

Renching






Top

Re: SSIS Job Failed

Michael Entin - MSFT

Useful new KB article for troubleshooting SSIS packages under SQL Agent

http://support.microsoft.com/kb/918760





Top

Re: SSIS Job Failed

renching

Thanks a lot. It is exactly what I want.



Top

Re: SSIS Job Failed

matilda16

I did all the suggestions here.. but still it doesnt work for me Sad i'm figuring this out the whole day.

It works perfectly fine in the BI Development Studio. It work fine also when I import it under MSDB (File System as package location - right click Run package). When I put in on job... it failed... any other ideas The login account used by my SQL Agent is a member of the domain account .


Top

Re: SSIS Job Failed

jwelch

Are you using Windows Authentication for any of your connections Does the Agent account have permissions on the data sources and files you are accessing



Top

Re: SSIS Job Failed

matilda16

It is just a simple import task from a text file to a table. The agent account is a member of the domain admin, so I believe he has the permission over the text file residing int the folder. When I am using the SSIS to run the package, it works really fine.




Top

Re: SSIS Job Failed

jwelch

What type of authentication are you using for the table connection Does the admin account have access to write to that table

What error are you getting





Top

Re: SSIS Job Failed

matilda16

Windows authentication... the currently logged in account is the domain account, here's the setup:

Account Name: agentserviceacct

Membership: member of a domain account

Use to run SQL Agent Service (MSSQLSERVER)

Currently logged in: {domain}\agentserviceacct

Authentication for table connection: Windows Authentication

Creator Name (on package properiesy under Indentification): {domain}\agentserviceacct


Top

Re: SSIS Job Failed

jwelch

Have you captured the error message from SSIS That would be very helpful in troubleshooting the problem. If not, please see the kb article linked to earlier in this thread.



Top

Re: SSIS Job Failed

Michael Entin - MSFT

matilda16 wrote:

I did all the suggestions here.. but still it doesnt work for me

So what error do you get in the log file The most important suggestion in this KB is to turn on logging, and get detailed information about the error.

That error message would allow me and other users to try to help you. But "It does not work" gives us no information at all (most people would not care to write to forum if everything worked fine Wink - so something does not work is kind of assumed).





Top

Re: SSIS Job Failed

matilda16

Oh no.. not yet.. I was trying to test again all possible solution based from the suggestions to make sure that I don't missed any part of the process. However, it still doesn't work. Here is the latest error message log:

06/05/2007 09:46:00,test ssis package,Error,0,SQL2,test ssis package,(Job outcome),,The job failed. The Job was invoked by User {domaim}\agentserviceaccount. The last step to run was step 1 (ssis package).,00:00:46,0,0,,,,0
06/05/2007 09:46:00,test ssis package,Error,1,SQL2,test ssis package,ssis package,,Executed as user: {domaim}\agentserviceaccount. The package execution failed. The step failed.,00:00:46,0,0,,,,0


Top

Re: SSIS Job Failed

jwelch

We need the SSIS log, not the agent log. The directions for turning on logging for the packages are referenced in the KB article above.



Top

Re: SSIS Job Failed

matilda16

I've been trying to figure out the logging part of SSIS. When I execute the package on BI and Management Studio (under SSIS instance), the execution logs everything. But when I try to run my SSIS package under SQL job, (the job still fail) there was no log on the file I created for it. Sorry, i am not really good in SSIS development, may be I missed a thing here.


Top