?
Solved

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

Posted on 2009-04-27
10
Medium Priority
?
182 Views
Last Modified: 2013-11-30
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?
0
Comment
Question by:TPBPIT
10 Comments
 
LVL 22

Expert Comment

by:PedroCGD
ID: 24246353
Which erros you have!?
Regards,
Pedro
www.pedrocgd.blogspot.com
0
 

Author Comment

by:TPBPIT
ID: 24246396
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
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 2000 total points
ID: 24247581
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Closing Comment

by:TPBPIT
ID: 31575211
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
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 24255840
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
 

Author Comment

by:TPBPIT
ID: 24255857

Where on the package would I do that and what do I need to turn on?  Also, where does it place the logs?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24256525
Perhaps you should consider posting a new question.
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 24256528
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
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 24256549
Somehow I posted before TPBPIT's question (that I saw), but after acperkins comment (which I did not see). Bizzare.
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 24256551
and again!
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question