SSIS Export job works randomly

I have an SSIS export package used as a step in a job.  The job is called up through a stored procedure.  The job completes the first step (gather required data) and then fails 75% or more of the time on the export step.  In the app log the failure is listed as event 12291.  Package "xxx" failed.   The failure email notifications state that the export step was the last step to run.  

I would better understand the problem if it failed all the time.. but it just fails most of the time.  I'd like it to export the data 100% of the time as this is part of a complex setup I've established so that users can obtain these results.
rgolenorAsked:
Who is Participating?
 
rgolenorAuthor Commented:
I'm still not sure what to make of this... I've resolved it (sort of) by setting the retry on the step to 5 and the export fails (exports a blank file) typically and then will export the file with results on one of the retries.  Very odd.  Btw, all the share/security settings allow for access to the export folder.  
0
 
Eugene ZCommented:
check your SSIS design:
see if it is your case:
" UNC versus mapped drives!
The manual process uses your credentials, which has a mapped drive reference.  The job uses the SQL Server Agent account, which does not"
more
http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/b203daa3-cafc-4df9-9712-a8724b8aa910 
 
0
 
rgolenorAuthor Commented:
I am using a direct path ( E:\data\results ) so I not sure that the above applies.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Eugene ZCommented:
where is the" E:\data\results" on your PC or on sql server?
can you try UNC path like \\yourserver\ShareWithSqlServerServiceAccountOrSqlAgentProxiAccountAccessPermissions?
0
 
rgolenorAuthor Commented:
The  (E:\data\results) is on the sql server ... I tried the \\server\data\results  and it fails 100% of the time.
0
 
Eugene ZCommented:
does sql service (sql agent ) account has Share\NTFS permissions on the \\server\data\results? Read\write?
--
if you login on the box as sql server service NT account - can you run the SSIS? What errors did you get?the same one?
0
 
rgolenorAuthor Commented:
It worked
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.