[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Errors while executing SSIS package as a SQL agent JOB

Posted on 2009-04-16
2
Medium Priority
?
9,203 Views
Last Modified: 2013-11-30
Hello,

I have a simple SSIS master package ,whose main task is to run other packages through the execute package tasks. The problem Iam facing is this. It runs fine when I run the package via BIDS but I get the following errors when I set up a job to run the Master Package.

Here is the error that I got the first time I ran this Master Package.

. Error 0xC0012050 while loading package file "D:\Data\AMF_DW_Incr_SSIS_Solution\AMF_DW_Incr_SSIS\AMF_DW_Incr_SSIS\FactBowlingCenterSales_Incr_v2 (1).dtsx". Package failed validation from the ExecutePackage task. The package cannot run.  .  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  4:38:27 PM  Finished: 4:39:00 PM  Elapsed:  32.25 seconds.  The package execution failed.  The step failed.


I got a couple of soultions on the internet which basically asked me to change a few settings in the Host (Master package in my case). Here is the link

http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/43c84491-66f8-4a61-bd97-54a824d56be7/

I changed the DelayValidation=TRUE on the Master package and made it false in the child package , but I still kept getting the same error. I also changed the DelayValidation=TRUE  on Execute package task in the Master package, but it kept failing at the same point.

Now Since we have a proxy account set up for the Data warehouse task , I changed the Job to use the proxy account instead of the default sql server account.

Now the master package failed even before it reached the place it was reaching before. and here is the error.

Executed as user: AMF\DWSSISProxy. ...on 9.00.3042.00 for 64-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  4:49:27 PM  Error: 2009-04-16 16:49:38.28     Code: 0xC001404B     Source: Master_Daily Log provider "SSIS log provider for Text files"     Description: The SSIS logging provider has failed to open the log. Error code: 0x80070005.  Access is denied.  End Error  Error: 2009-04-16 16:49:38.28     Code: 0xC001404B     Source: Master_Daily Log provider "SSIS log provider for Text files"     Description: The SSIS logging provider has failed to open the log. Error code: 0x80070005.  Access is denied.  End Error  Error: 2009-04-16 16:49:38.45     Code: 0xC0202009     Source: dimAccount_Incr Log provider "SSIS log provider for SQL Server"     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E09.  An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x8004...  The package execution fa...  The step failed.


Hoping for some help...

Ashwin





 
0
Comment
Question by:Ashwin_shastry
2 Comments
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 1500 total points
ID: 24164655
I suggest you enable database logging in this package: D:\Data\AMF_DW_Incr_SSIS_Solution\AMF_DW_Incr_SSIS\AMF_DW_Incr_SSIS\FactBowlingCenterSales_Incr_v2 (1).dtsx
and see why the sub paqckage is failing. It looks like that error message indicates the sub package has failed, but its not telling you why. Logging on the sub package should tell you why.
 The next error:
Description: The SSIS logging provider has failed to open the log. Error code: 0x80070005.  Access is denied
Means that the account it is running under cannot open the log file that you've set up.
 
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 24172718
Also, I encourage you to use UNC naming for all file locations. \\server\path\file
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

873 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