SSIS Package Fails only as a SQL Job - help!

Last Post 16 Feb 2011 04:27 PM by SQL_Jr. 9 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
SQL_Jr
New Member
New Member

--
11 Feb 2011 07:03 AM
I built a simple package that appends the date to the name of the file and puts it on a remote unc share.  When I run the package (from another machine) - it works!  When I imported the package and right-click from SSMS "Execute Package" it works!   HOWEVER, when I added it as a job pointing to the package, IT FAILS!!!

- The SQLAgent svc account (I elevated to sysadmin to eliminate permission issue)
- The destination remote folder was granted permissions to the SQLAgent svc account.

What am I missing?  Is this even a permission issue?  PLEASE help.  TIA

Here is the error in the job history.......


"DestinationConnectionFlatFile" Description: The file name "\\My.IP.Address\Folder_1\Folder_2\export_20110211.txt" specified in the connection was not valid. End Error
rm
New Member
New Member

--
11 Feb 2011 11:08 AM
Who's job owner? Is it member of syasdmin role?
gunneyk
New Member
New Member

--
11 Feb 2011 01:09 PM
Use a proxy account for that job step that has the correct permissions (crentials) to reach that share.
SQL_Jr
New Member
New Member

--
14 Feb 2011 06:03 AM

Thanks, but one question - am I mapping the credential to the SQLagent service account or the job owner.

BTW the job owner is 'sa'  Please advise further!  Thanks!

rm
New Member
New Member

--
14 Feb 2011 06:33 AM
Create proxy account for ssis then set 'run as' to that proxy in job step.
SQL_Jr
New Member
New Member

--
14 Feb 2011 07:27 AM
OK, Thx I think I might be beyond the proxy issue, but now get this error:

"DestinationConnectionFlatFile" Description: The file name property is not valid. The file name is a device or contains invalid characters.

Remember, this runs when I execute the package from SSIS engine directly. :-(
SQLNoob
New Member
New Member

--
14 Feb 2011 09:36 AM
I had a similar problem before and I have to modify the share and explicitly give Full permission for the folder to the SQLAgent service account.
If the SQLAgent account is allowed to log-in to the machine where you run the SSIS package from, you can try loging-in and try to reach that remote folder. If you are allowed access and you can see the files in that remote folder, try to create a file, and/or rename a file.

See if that helps.
SQL_Jr
New Member
New Member

--
14 Feb 2011 10:08 AM
Hi, SQLNoob - thx for sharing your experience.

I wanted to test that, but the svc account doesn't have RDP rights to log on remotely.
It does however, have explicit (FULL) permissions to the share/folder.
ANy ideas? thx
rm
New Member
New Member

--
14 Feb 2011 12:21 PM
Ensure proxy account has full permission in that share.
SQL_Jr
New Member
New Member

--
16 Feb 2011 04:27 PM
I'm pretty sure that's the case. The proxy account is the SQL Agent svc account.
Any other ideas? THX
You are not authorized to post a reply.

Acceptable Use Policy