Solved

SSIS Export job works randomly

Posted on 2010-09-09
7
197 Views
Last Modified: 2012-09-09
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.
0
Comment
Question by:rgolenor
  • 4
  • 3
7 Comments
 
LVL 42

Expert Comment

by:EugeneZ
Comment Utility
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
 

Author Comment

by:rgolenor
Comment Utility
I am using a direct path ( E:\data\results ) so I not sure that the above applies.
0
 
LVL 42

Expert Comment

by:EugeneZ
Comment Utility
where is the" E:\data\results" on your PC or on sql server?
can you try UNC path like \\yourserver\ShareWithSqlServerServiceAccountOrSqlAgentProxiAccountAccessPermissions?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:rgolenor
Comment Utility
The  (E:\data\results) is on the sql server ... I tried the \\server\data\results  and it fails 100% of the time.
0
 
LVL 42

Expert Comment

by:EugeneZ
Comment Utility
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
 

Accepted Solution

by:
rgolenor earned 0 total points
Comment Utility
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
 

Author Closing Comment

by:rgolenor
Comment Utility
It worked
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now