running SSIS package as SQL agent job failed

Question

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.

Did you install the package to the file system or the database? Is the package really located in \\phantsmgmt\c$\SDATA\ITDokumentace\SQL_DT_projects\Export_ser_zak_CEBIA\Export_ser_zak_CEBIA\Export_ser_zak_CEBIA\Package.dtsx? — HardCode
Yes, file system and it really exists in that location. The problem was in Target Server Version configured in VS. See below — WhiteB1

Hadi Hadi · Accepted Answer · 2017-12-27T21:35:24

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.

running SSIS package as SQL agent job failed

2 Answers