July 2, 2017 at 4:06 pm
Hi,
I have a system that previously ran on SQL Server 2008. However, with our latest version upgrade we are now running on SQL Server 2014 (SP2).
With the previous install on SQL 2008, I had created an SSIS package that would bring together data from two different sources into staging tables for insert into a master “users” table.
With the upgrade to SQL 2014, I recreated the SSIS package in Visual Studio 2015. If I run the package in VS2015, then it executes perfectly. Trying to schedule it through SQL Server Agent results in the following error:
Executed as user: DOMAIN\sqlservice. Microsoft (R) SQL Server Execute Package Utility Version 12.0.5000.0 for 32-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 11:44:15 PM Error: 2017-07-02 23:44:18.81 Code: 0xC0010018 Source: Package Description: Error loading value "<DTS:ConnectionManagers xmlns: DTS="www.microsoft.com/SqlServer/Dts"><DTS:ConnectionManager DTS:refId="Package.ConnectionManagers[StaffExport]" DTS:CreationName="FLATFILE" DTS: DelayValidation="True" DTS: DTSID="{243A9A28-18C7-4529-9CDC-E44EC74EE9F6}" DTS:Obj" from node "DTS:ConnectionManagers". End Error Could not load package "\ImportStaff" because of error 0xC0010014. Description: 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. Source: Started: 11:44:15 PM Finished: 11:44:18 PM Elapsed: 2.953 seconds. The package could not be loaded. The step failed.
I have Googled and tried various things to no avail.
In VS2015, under Project -> Project Properties, I’ve set the TargetServerVersion to SQL Server 2014.
I have built the package with Run64BitRuntime alternately on and off.
None of those changes made any difference.
And and all assistance in this regard will be greatly appreciated.
July 2, 2017 at 4:55 pm
look specifically at the path in that Package.ConnectionManagers[StaffExport],
is it using a mapped drive, and not a unc path? ie the path should be \\servername\sharename\filename.txt, and not the T:\sharename\filename.txt
mapped drives get created /connected a t login,s o there's a good chance there is nomapped drive for the execution account.
under what security context is this running under? if this is a job, did you create a proxy account that has priviledges to that file share location?
Lowell
July 2, 2017 at 5:29 pm
I see that you have built the package with 64 bit runtime on and off. Did you set it to off and also set the job to run 32 bit when it was off - Execution options tab when editing the step?
Have you tried restarting SQL Server Integration Services?
Could you please list what else you have tried? Otherwise everyone can keep posting solutions, you say "I tried that", someone posts another "I tried that", etc.
Sue
July 2, 2017 at 8:45 pm
Lowell - Sunday, July 2, 2017 4:55 PMlook specifically at the path in that Package.ConnectionManagers[StaffExport],
is it using a mapped drive, and not a unc path? ie the path should be \\servername\sharename\filename.txt, and not the T:\sharename\filename.txt
mapped drives get created /connected a t login,s o there's a good chance there is nomapped drive for the execution account.under what security context is this running under? if this is a job, did you create a proxy account that has priviledges to that file share location?
It is using the full UNC path. It is running with a domain service account and I have given this account read/write permissions on the folder in question.
July 2, 2017 at 8:59 pm
Sue_H - Sunday, July 2, 2017 5:29 PMI see that you have built the package with 64 bit runtime on and off. Did you set it to off and also set the job to run 32 bit when it was off - Execution options tab when editing the step?
Have you tried restarting SQL Server Integration Services?
Could you please list what else you have tried? Otherwise everyone can keep posting solutions, you say "I tried that", someone posts another "I tried that", etc.Sue
I did set the option to use 32-bit when building with the 64 bit runtime off. Just rebuilt the package with that option off, and double checked that the execution option is set to use the 32-bit run time.
SSIS service restarted.
SSIS service set to log on as NT AUTHORITY\NetworkService
In the connection managers for the flat files, DelayValidation was initially set to FALSE. Set DelayValidation to TRUE, and package executes in SSMS with same results, failing on the flat file connection for StaffExport.
Initially used a mapped drive for the flat files. Had an "Execute Process Task" to map/unmap the drive at beginning and end of the sequence. With all the failures, I changed the flat file connections to use the UNC path: \\server\share.
July 3, 2017 at 8:37 am
tiaanb - Sunday, July 2, 2017 8:59 PMSue_H - Sunday, July 2, 2017 5:29 PMI see that you have built the package with 64 bit runtime on and off. Did you set it to off and also set the job to run 32 bit when it was off - Execution options tab when editing the step?
Have you tried restarting SQL Server Integration Services?
Could you please list what else you have tried? Otherwise everyone can keep posting solutions, you say "I tried that", someone posts another "I tried that", etc.Sue
I did set the option to use 32-bit when building with the 64 bit runtime off. Just rebuilt the package with that option off, and double checked that the execution option is set to use the 32-bit run time.
SSIS service restarted.
SSIS service set to log on as NT AUTHORITY\NetworkService
In the connection managers for the flat files, DelayValidation was initially set to FALSE. Set DelayValidation to TRUE, and package executes in SSMS with same results, failing on the flat file connection for StaffExport.Initially used a mapped drive for the flat files. Had an "Execute Process Task" to map/unmap the drive at beginning and end of the sequence. With all the failures, I changed the flat file connections to use the UNC path: \\server\share.
pretty sure your issue is:
NT AUTHORITY\NetworkService
that is a local account that would likely not have permissions on a remote system. You should have your service accounts running as AD accounts (if inside an AD domain) or as local accounts that have the same username and password across systems.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 3, 2017 at 9:01 am
bmg002 - Monday, July 3, 2017 8:37 AMpretty sure your issue is:
NT AUTHORITY\NetworkServicethat is a local account that would likely not have permissions on a remote system. You should have your service accounts running as AD accounts (if inside an AD domain) or as local accounts that have the same username and password across systems.
I can't tell if it's currently a domain account or network service. Network service can be granted access using the computer account - Domain\ServerName$
Not necessarily a good thing since other things on the server can run as network service but it's still doable.
Sue
July 3, 2017 at 10:13 am
Sue_H - Sunday, July 2, 2017 5:29 PMI see that you have built the package with 64 bit runtime on and off. Did you set it to off and also set the job to run 32 bit when it was off - Execution options tab when editing the step?
Have you tried restarting SQL Server Integration Services?
Could you please list what else you have tried? Otherwise everyone can keep posting solutions, you say "I tried that", someone posts another "I tried that", etc.Sue
Note that '64-bit runtime' does not have anything to do with the build process, as far as I know.
Instead, its value may be set during development to control which version of dtexec.exe is spawned when debugging/executing packages in Visual Studio.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 3, 2017 at 11:39 am
Phil Parkin - Monday, July 3, 2017 10:13 AMSue_H - Sunday, July 2, 2017 5:29 PMI see that you have built the package with 64 bit runtime on and off. Did you set it to off and also set the job to run 32 bit when it was off - Execution options tab when editing the step?
Have you tried restarting SQL Server Integration Services?
Could you please list what else you have tried? Otherwise everyone can keep posting solutions, you say "I tried that", someone posts another "I tried that", etc.Sue
Note that '64-bit runtime' does not have anything to do with the build process, as far as I know.
Instead, its value may be set during development to control which version of dtexec.exe is spawned when debugging/executing packages in Visual Studio.
Thanks Phil.
Regardless of the setting, the package executes in Visual Studio. It's only when run from Management Studio that it throws the error.
July 3, 2017 at 12:07 pm
You should also look at the logs (both windows and SQL) to see if there is any more information about the error. If it is a permission denied on the file error, that could be helpful.
If this is an SSIS Catalog, you should look at the execution report at that level and it should have a nicer error message for you.
Doing a quick google of that error (0xC0010014), it sounds like it could mean a variety of different things. Do any of your other logs give a better indication of what went wrong (SQL logs, SQL Agent logs, windows logs)?
Since it runs from SSMS (where it is executed as you) but not from the SSIS server, my thoughts are either permission error or missing reference (like missing ACE or JET drivers). But there should be a log somewhere that gives you a better understanding of what went wrong.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 3, 2017 at 12:25 pm
bmg002 - Monday, July 3, 2017 12:07 PMYou should also look at the logs (both windows and SQL) to see if there is any more information about the error. If it is a permission denied on the file error, that could be helpful.
If this is an SSIS Catalog, you should look at the execution report at that level and it should have a nicer error message for you.Doing a quick google of that error (0xC0010014), it sounds like it could mean a variety of different things. Do any of your other logs give a better indication of what went wrong (SQL logs, SQL Agent logs, windows logs)?
Since it runs from SSMS (where it is executed as you) but not from the SSIS server, my thoughts are either permission error or missing reference (like missing ACE or JET drivers). But there should be a log somewhere that gives you a better understanding of what went wrong.
I agree with checking the All Executions report in SSISDB, if possible, to (hopefully) get more detailed info.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 3, 2017 at 3:21 pm
Thanks bmg002. I will check out all the various logs in the morning.
I've only looked at the job history in SSMS.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply