Problem running an SSIS Package in SQL Server Agent in SQL 2005

I'm not sure what is going on.  I have created a package in Visual Studio which uses a query to extract some data from one of my databases and puts the data in a flat file.  You can run debug and the package executes without issue.  I then using the Save Copy of Package As .dtsx, I save the package into SQL.  I can then log into the Integration Services part of the Management Studio and browse to the saved package, right click and run the package with no issue.

The issue comes into play here.  When I go into Management Studio into the Database Engine and I try to schedule it to run SQL Server Agent the package will never run.  It just errors out.  I have configured it to use Windows Authenication, just as all my other working jobs are setup, but it still doesn't work.  And while it say to see the history log, I'm not exactly sure what it is speaking of, but I haven't seen anything to speak of in what I think are the logs.

Does anyone have any idea as to what is happening?
TPBPITAsked:
Who is Participating?
 
nmcdermaidConnect With a Mentor Commented:
You could turn on logging inside the package to get more information. The fundamental difference is the SQL Agent Service account. It probably doesn't have rights to create/write to the file
0
 
PedroCGDCommented:
Which erros you have!?
Regards,
Pedro
www.pedrocgd.blogspot.com
0
 
TPBPITAuthor Commented:
That's the thing, I'm not seeing errors in the what I think is the log.  All I can tell is that it fails when I Run at Step.  The eventviewer doesn't show anything either.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
TPBPITAuthor Commented:
Bingo.  The problem was that the share where the file was being written to didn't have the correct permissions therefore the file was beign created, but the SQL Server Agent couldn't write it to the share.

Thanks for opening my eyes.
0
 
nmcdermaidCommented:
Glad you sorted it. In future you might want to turn on logging. Its difficult to troubleshoot an issue without an error message. Your log would have told you straight away this is what the issue is.
0
 
TPBPITAuthor Commented:

Where on the package would I do that and what do I need to turn on?  Also, where does it place the logs?
0
 
Anthony PerkinsCommented:
Perhaps you should consider posting a new question.
0
 
nmcdermaidCommented:
In BIDS (BI Development Studio), press SSIS then Logging.
Then pick a provider (windows event log, text file, database etc.). I don't suggest file as you could run into the same problem - can't write to the log file!
Then pick all of the components that you want to log.
The SSIS logs are VERY verbose (like everything nowadays). If you go ahead and pick everything your log will fill up quickly.
0
 
nmcdermaidCommented:
Somehow I posted before TPBPIT's question (that I saw), but after acperkins comment (which I did not see). Bizzare.
0
 
nmcdermaidCommented:
and again!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.