CODEWITHSUNDEEP
sql-serverssissql-agent-jobsql-server-2017ssis-2017
WhiteB1
WhiteB1

Reputation: 51

running SSIS package as SQL agent job failed

I created SSIS an project, which exports some data to Excel. In Visual Studio 2017 it works fine, but when I run it using a SQL Server Agent job (MS SQL Server 2017), it throws this error:

"Executed as user: Administrator. Microsoft (R) SQL Server Execute Package 
Utility  Version 13.0.1601.5 for 32-bit  Copyright (C) 2016 Microsoft. All 
rights reserved.    Started:  3:31:46 PM  Error: 2017-12-27 15:31:47.10     
Code: 0xC0010018     Source: Package      Description: Error loading value "
<DTS:ConnectionManagers xmlns:DTS="www.microsoft.com/SqlServer/Dts">
<DTS:ConnectionManager DTS:refId="Package.ConnectionManagers[Excel]" 
DTS:CreationName="EXCEL" DTS:DTSID="{0E7AADD0-AA79-4C99-8FFC-92E1CB5A871A}" 
DTS:ObjectName="Excel"><DTS:ObjectData><DTS" from node 
"DTS:ConnectionManagers".  End Error  Could not load package "\\phantsmgmt\c$\SDATA\ITDokumentace\SQL_DT_projects\Export_ser_zak_CEBIA\Export_ser_zak_CEBIA\Export_ser_zak_CEBIA\Package.dtsx"
because of error 0xC0010014.  Description: The package failed to load due to 
error 0xC0010014 "One or more error occurred. There should be more specific 
errors preceding this one that explains the details of the errors. This 
message is used as a return value from functions that encounter errors.". 
This occurs when CPackage::LoadFromXML fails.  Source: Package  Started:  
3:31:46 PM  Finished: 3:31:47 PM  Elapsed:  0.422 seconds.  The package 
could not be loaded.  The step failed."

That is stange, because I have another SSIS project, which is using the Excel connection manager too, and it works fine using the scheduled SQL Server Agent job.

I tried to turn 32 bit mode on and off again, but nothing helps.

Upvotes: 1

Views: 3840

Answers (3)

renaissanceMan
renaissanceMan

Reputation: 433

I'm getting this message more recently with a VS 2019 PRO build of an ssis package and oledb access to ms sql. A straight forward vs 2019 build with oledb access to ms sql would throw this message on our batch machines even though the same job would run on a laptop. I couldn't get anything to work until I set TargetServerVersion to SQLServer 2016. Probably just an anomoly of our somewhat strange environment but I wanted to mention it in case it helped someone else. Some people are saying that this message means dtexec can't read your .dtsx package.

Upvotes: 0

WhiteB1
WhiteB1

Reputation: 51

The solution, that helps me is Target Server Version setting in Visual Studio. More info here: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/84e470e9-aeef-4850-b29e-9eb3d545819f/running-ssis-package-as-sql-agent-job-failed?forum=sqlintegrationservices

Upvotes: 1

Hadi
Hadi

Reputation: 37358

They main error is:

Error loading value "
<DTS:ConnectionManagers xmlns:DTS="www.microsoft.com/SqlServer/Dts">
<DTS:ConnectionManager DTS:refId="Package.ConnectionManagers[Excel]" 
DTS:CreationName="EXCEL" DTS:DTSID="{0E7AADD0-AA79-4C99-8FFC-92E1CB5A871A}" 
DTS:ObjectName="Excel"><DTS:ObjectData><DTS" from node 
"DTS:ConnectionManagers".  End Error

It looks like the error is from the Excel connection manager, there is a value that cannot be loaded. First you have to check that the excel file path exists. If the file is created after package execution you have to set the DelayValidation property to True for each component that uses this connection.

Upvotes: 0

Related Questions