1

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.

Hadi
  • 36,233
  • 13
  • 65
  • 124
WhiteB1
  • 51
  • 2
  • 5
  • 1
    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 Dec 27 '17 at 15:40
  • Yes, file system and it really exists in that location. The problem was in Target Server Version configured in VS. See below – WhiteB1 Dec 29 '17 at 13:52

3 Answers3

1

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

WhiteB1
  • 51
  • 2
  • 5
0

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.

Hadi
  • 36,233
  • 13
  • 65
  • 124
0

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.

renaissanceMan
  • 385
  • 2
  • 7