Solved

SSIS Export job works randomly

Posted on 2010-09-09
7
199 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
ID: 33640981
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
ID: 33641920
I am using a direct path ( E:\data\results ) so I not sure that the above applies.
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 33643030
where is the" E:\data\results" on your PC or on sql server?
can you try UNC path like \\yourserver\ShareWithSqlServerServiceAccountOrSqlAgentProxiAccountAccessPermissions?
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:rgolenor
ID: 33647952
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
ID: 33649808
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
ID: 33725637
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
ID: 38380557
It worked
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

828 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