job fails when running a dtsx package
hi all, I am trying to solve the following problem: I have an SSIS package developed with VS2008 that simply dumps a text file into a table. The file is on a network. 1. From the VS 2008, running the project doesn't give any errors - it fills the table OK. 2. I added the dtsx package to the Data Collector folder under Stored Packages/MSDB - I executed the package from there, again NO ERROR. 3. I created a job and added one step that is the dtsx package - it fails saying The file name "\\myFileServer\myData\myFolder\H29102010.txt" specified in the connection was not valid. End Error Error: 2010-10-29 10:03:11.83 Code: 0xC001401D Source: myJOB Description: Connection "c1" failed validation. End Error Error: 2010-10-29 10:03:11.84 Code: 0xC001401E Source: myJOB Connection manager "c1" The SSIS service is not running under NT ADMIN\Network Service but I changed to an account that can read/write to the above network location. Also the package has been saved with password encryption (the user key encryption would raise erorrs, too). What I can't understand is why would the package run from SSIS and VS2008 and not from the Job Agent? Thanks, kowalsky
October 29th, 2010 11:32am
Two main reasons things fail between development environment and server environment: 32 Bit vs. 64 bit. Security. My guess is the latter. Check the credentials of the SQL Agent account, not only the SSIS Service account. Make sure SQL Agent account can access the share. FYI: Even though you execute a package stored on the SSIS instance, you are executing it with YOUR credentials, not those of the SSIS Service account. Oh, and don't store things in the "Data Collector" folder under MSDB. Leave that for special SQL features to mess with. Create your own folder hierarchy under MSDB and put all your stuff there. you can thank me later.Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
October 29th, 2010 11:38am
Todd, Thanks a bunch, I reviewed the error message and indeed it said that it executed under myMachine\SYSTEM which is the local system account under which the SQL Agent service was running. As soon as I changed this account to a domain account (that has proper rights to read/write to that network location) everything turned out just right. I will also make my own folder under MSDB, Thanks a gain, kowalsky
October 29th, 2010 12:09pm