Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
alterego55
Starting Member
2 Posts |
 Posted - 2010-03-29 : 19:15:57
|
| I have an SSIS package that reads ISA Logs from a proxy server. I created the package using the For Next Loop to pick up two files daily. The file names are structured include the date and server the file came from and they have a file extension of ".iis". I am able to use an expression to pick up the files. Each SQL command The steps are as follows:1. SQL Command - start batch2. SQL Command - Truncate staging table3. Fornext Loop - loads two files into the staging table4. SQL Command - various data scrubbing routines5. SQL Command - insert any new dimension rows6. SQL Command - inserts the fact rows.7. SQL Command - complete batch.In development, the entire package runs fine. If I deploy it (on the exact same server) and run it as a job, step #3 seems to get bypassed entirely. I have logging turned on and it doesn't get logged like the other steps do. It doesn't raise any errors in the log or to the SQL job. It normally takes about an hour to run, but running as a job takes seconds (because no data is getting loaded).What could be going on here?Thanks. |
|
|
aureolin
Starting Member
3 Posts |
Posted - 2010-03-30 : 12:26:07
|
| When you run SSIS packages manually, they run under your account with your permissions to the database and file system. When you run SSIS packages from a job, they run under the SQL Agent account. My initial guess is that the SQL Agent account does not have the necessary file permissions to read your data files.Steve G.________________________________________"It's more complicated than that" |
 |
|
|
alterego55
Starting Member
2 Posts |
Posted - 2010-03-31 : 12:54:38
|
| Yes, the answer was permissions. Before I started deployment, I asked the IT department to provide read privileges for the SQL Agent account on the share where the job would be reading files. They swore up and down that they did.Following my instinct, I had the IT department logon to the server with the SQL Agent account and guess what? They couldn't read the share. Although they gave permissions to the share, they didn't give permissions to the folder at the file system level (or something like that).I do wish SSIS would throw an error when an "Access denied" error is thrown by the OS, as opposed to just treating the step as if the file didn't exist.Thanks. |
|
|
|
|
|
|
SSIS Package Skips a Step