Link to home
Start Free TrialLog in
Avatar of Lhillstrom
Lhillstrom

asked on

SSIS package fails when run from a job

I have a SSIS package that creates a flat file. The package will fail when run as a job when the destination file exists. If I manually delete the file the job will be successful, it only fails when the file exists.

The file destination is a network UNC path. When I log on with the Windows account that SQL server is running under I can browse to the destination and delete the file.

Error message below:

Message
Executed as user: DOMAIN1\SQLSERVERACCOUNT. Microsoft (R) SQL Server Execute Package Utility  Version 9.00.3042.00 for 32-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  12:42:01 PM  Error: 2009-01-15 12:42:01.53     Code: 0xC001401E     Source: Analyst_Export Connection manager "DestinationConnectionFlatFile"     Description: The file name "\\fileserver\Destination\Analyst.txt" specified in the connection was not valid.  End Error  Error: 2009-01-15 12:42:01.53     Code: 0xC001401D     Source: Analyst_Export      Description: Connection "DestinationConnectionFlatFile" failed validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  12:42:01 PM  Finished: 12:42:01 PM  Elapsed:  0.219 seconds.  The package execution failed.  The step failed.
Avatar of Steve Hogg
Steve Hogg
Flag of United States of America image

Have you set the Flat File Destination to "Overwrite data in the file"?
 
ASKER CERTIFIED SOLUTION
Avatar of Steve Hogg
Steve Hogg
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Lhillstrom
Lhillstrom

ASKER

>>Have you set the Flat File Destination to "Overwrite data in the file"?
Yes.

File is not read only.

>>I would lean towards a permission issue
Agreed. How can I troubleshoot this further or get a more approriate error message?
Error messages beyond this detail are hard to come by in SSIS. Is this running from a Proxy account with a credential?
>>Is this running from a Proxy account with a credential?
No.
Executed as user: DOMAIN1\SQLSERVERACCOUNT
This user is the account running SQL Server. Confirmed that the service MSSQLSERVER has the log on set to this account.

When I log on to my domain with this account I can browse to my destination and create/delete files. Permissions on the destination folder are set to "Full Control" which I believe is more than I need.
But remember that when you run a job, that the SQL AGent service is the one that runs it by default. So you have 2 options, you can give permissions to whatever Account runs SQL Server Agent (NOT SQL Server) or you can implement a Proxy Account that has privileges to run SSIS packages. To do the Proxy, you would need to Create a Credential under Security and then in Agent Create a Proxy using that Credential.

Somthing still doesn't seem right, because the package will run without error and create the file in that location when one does NOT exists. It only fails when it DOES exists.
Here is a couple of debug tests. Delete the file and let the package create the file. Go look at the file properties and verify the owner/creator of the file.
Next, try to write a file to another location - preferably on the SQL Server, does that work without error?
>> you can give permissions to whatever Account runs SQL Server Agent (NOT SQL Server)

OK, in my case the same account is used for server and agent.
>>Delete the file and let the package create the file. Go look at the file properties and verify the owner/creator of the file.
Ok, I deleted the file and then ran the job. the summary tab is empty. If I go to security>advanced>owner tab I see my sql server/agent account.
If from advanced I go to effective permissions and select my sql server/agent account everything is checked.

Stand by for next test.

Good, I am adding a couple more tasks to the testing / debugging.
1. Create the file as a different user, then see if the package runs. In other words. Delete the file, then create one with notepad.
2. Add a File System Task to your Control Flow of the Package and set it up to Delete the file before the Data Flow. I am curious if it will find the file to delete?
oh, ps. Copy and paste the full UNC path from the error message into your Windows Explorer. I want to make sure it opens - no wierd characters or other. :-)
This is resolved. So my file path was something like this:
\\fileserver\folder1\folder2\folder3\folder4
When I logged in as my user I could go to \\fileserver\folder1\folder2\folder3\folder4. Folder4 was where I added permissions
 I could not go to \\fileserver\folder1\. I thought that was odd, my network folks didn't think that was so odd.
I added permissions to \\fileserver\folder1\. and it worked. I think fileserver01 is on a NAS/SAN so there may be some wierdness.
Wow you have a lot of points, here are some more!