TPBPIT
asked on
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?
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?
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
Thanks for opening my eyes.
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.
ASKER
Where on the package would I do that and what do I need to turn on? Also, where does it place the logs?
Perhaps you should consider posting a new question.
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.
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.
Somehow I posted before TPBPIT's question (that I saw), but after acperkins comment (which I did not see). Bizzare.
and again!
Regards,
Pedro
www.pedrocgd.blogspot.com