I created a mall SSIS package that takes data from text files and loads it into a data warehouse. I can run the package in Visual Studio and it runs fine. I build the solution and installed it on the server that runs SQL Server (SQL Server 2005, Windows 2008V2 64bit). I installed it as a SQL Server deployment package using windows authentication. I then set up a SQL Agent job to execute the package. The package fails. Event Viewer on the server is very helpful. It tells me: "Package "DatamartLoad" failed." There is nothing in the SQL Server Agent Error Logs. Any suggestions on how to debug this???

Setup logging in the package: http://msdn.microsoft.com/en-us/library/ms138020.aspx SQL Agent will not capture connection, control flow or data flow object errors in it's event viewer. I'd be willing to hazard a guess that your SQL Agent account doesn't have credentials for read access either to the folder that the txt file resides in or write access to the sql DW db. Have you tried running the package from the sql server store using dtexec or SSMS, doing it that way will use the connected user credentials rather than the SQL Agent account.My Blog "Karl Beran's BI Mumble"

The SQL Agent is using the NT Authority/NETWORK SERVICE account. I thought it had access to everything it needed. Ran the package from SMS. I got a lot of: Warning: "Precompiled script failed to load. Attempting to recompile..." but the package appeard to successfully complete. I guess NETWORK SERVICE account doesn't have enough access. It is the same account SQL Server runs under so I'm not sure what changes to make to the Agent account to gain access. Any suggestions? I'll set up logging and see what I get... OK....I'm to dumb to breath....I set up logging using SSIS log provider for Text files...set up logging for most of the package OnError OnTaskFailed. Configured it to create a file named SSIS Logging on my desktop...package ran...package failed...no SSIS logging file...

to simplify things, try creating a SQL agent job with a trasact-sql job step with "SELECT getdate()" run the job and check to see if it suceeds, this proves you can connect to the db. Then in a test db, create a test table with one integer column and create a job as above but with "INSERT INTO testtable VALUES (1)" and see if this succeeds. This proves the accound has write access.My Blog "Karl Beran's BI Mumble"

I run a daily maintenace plan with Agent that backs up all the databases on the server, shrinks the log files & db, updates statistics & reorganizes indexes. It always runs with no problem. I should think that would require both read and write access to vairous databases...

Actually you'll require more than read and write priveledges to perform those tasks have a read of http://articles.techrepublic.com.com/5100-10878_11-1061781.html http://msdn.microsoft.com/en-us/library/ms188659.aspx http://msdn.microsoft.com/en-us/library/ms189121.aspx Additionally if you are having to shrink files on a daily basis there is something wrong with your configuration. File growth is an expensive operation so the less frequently it occurs the better. You might want to review your file growth settings and investigate what is making them grow in the first place.My Blog "Karl Beran's BI Mumble"

I don't have to shrink the files every night....it is part of the maintence plan that runs on autopilot...files are only shrunk if they grow beyond a limit. The only point I was trying to make is that SQL Agent seems to have the ability to run my maintenance plan so I'm not sure why I won't run the SSIS package.

If you don't have to shrink them every night, it may be best to avoid it. Can cause all sorts of problems with disk fragmentation etc. The cause of your issue is insufficient privleges for the SQL Agent system account.My Blog "Karl Beran's BI Mumble"

did you changed sql authentication recently? did you changed sql user or password? or you use windows authentication in your connections?http://www.rad.pasfu.com

No changes. We have some roles in SQL Server but 99% of what we do is through windows authentication. A few months ago we chaged to Windows 2008R2 64 bit servers...still using SQL 2005 R3. I successfully ran a different SSIS packages on the old server with Agent so I don't think it's a permission problem. Other threads talk about precompiled script problems. I got dozens of warnings when I ran the package through SMS but it completed. I wonder if they blew up under the Agent for some reason. Any idea why the logging file wasn't created?

what happen if you run the maintenance plan directly from SSMS under maintenance plan directory? what error do you receive?http://www.rad.pasfu.com

It doesn't show up under the maintenance plan directory. I added it as a job under SQL Agent. Running that job fails. I connectecd to SSIS and ran it as a stored package. It gave a bunch of precompiled script warnings but completed without error.

If you can run the package from stored packages directory in SSMS successfully, and you have problem on sql agent job running, create a proxy account for SSIS and try with that, for how to create proxy account look here: http://support.microsoft.com/kb/912911http://www.rad.pasfu.com

failed again...crated a proxy account with system administrator credentials. Still can't get logging to work.

Well, I finally got logging to work and my problem is: Precompiled script failed to load. I don't have any Script tasks in my package. I have many Execute SQL tasks. I've seen other threads where you have to set some sort of precompiled option but there is no such option on Execute SQL tasks. I also saw other thread where there is a bug in the 64 bit OS. My previoius SSIS packages ran OK under win 2003, 32 bit. What do I do now???

Precompiled script failed to load. Is it warning or Error? if this is warning, overlook it and paste error message herehttp://www.rad.pasfu.com

When I run it from SMS it is a WARNING. When I run it with SQL Server Agent it is an ERROR: OnError,JEC,LLC\john.church,Stub,{dbd7d1df-eb5e-4f90-8c86-d3694844763c},{9D451684-3750-49C9-894B-CCD4E4075AC9},11/9/2010 7:14:41 AM,11/9/2010 7:14:41 AM,3,0x,Precompiled script failed to load. The computer that is running the Script Task does not have a compiler capable of recompiling the script. To recompile the script, see the Microsoft Knowledge Base article, KB931846 (http://go.microsoft.com/fwlink/?LinkId=81885).

what is your sql server version? could you install last service pack and try again?http://www.rad.pasfu.com

SQL Server 2005 with SP3 installed. Everything else works fine. SP 3 has been installed for months. I'd rather not reinstall it.

Hi John, I got twice the same issue. Only fix I found is : Open the package on the same platform as the one it is expected to run on Make an unsignificant change you just to be able to save the package again Try to run it from an sql job It should work. I'm still trying to find a better solution. The problem is that developers cannot be asked to test dtsx run from sql jobs! You may look at http://www.bigresource.com/Tracker/Track-ms_sql-qza8fYE7/ Let us know, Regards, Thomas