February 27, 2017 at 1:45 pm
I have been trying to solve a SSIS project related problem for a week now.
The SSIS solution/project has been working fine for two years but is not working at all for a second user after adding him two weeks ago.
The user can open the solution and execute packages after connecting to the source code via VSO/TFS or by using a local copy.
But after making changes to the project the other user got the error message (1) below when trying to build the project or executing a package.
Trying to import the project from the server results in another error message (2).
Most posts I have find refer to versions of SQL/visual studio (project created on a version that differs to the one currently in use) it is not appicable to my case
Any help or feedback highly appriciated.
My conditions are:
SQL Server 2014 (including SSIS) on win server 2012 r2
Dev machine with win 10 and SQL Server Data Tools 2013 (latest version)
Visual Studio Online for source code/versioning (GIT)
SSIS project deployment model
ProtectionLevel = EncryptSensitiveWithPassword
What I have tried so far:
Tried all cominations of user and dev machine
Tried different versions of Data tools
Tried to find differences in project/solution files as well as in packages to identify user related code etc.
Tried the SSI project with and without source control
Tried to change the Run64BitRunTime property
(1)
Error 1 Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: 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. ---> System.Runtime.InteropServices.COMException: 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. at Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.LoadPackage(String FileName, Boolean loadNeutral, IDTSEvents100 pEvents) at Microsoft.SqlServer.Dts.Runtime.Application.LoadPackage(String fileName, IDTSEvents events, Boolean loadNeutral) --- End of inner exception stack trace --- at Microsoft.SqlServer.Dts.Runtime.Application.LoadPackage(String fileName, IDTSEvents events, Boolean loadNeutral) at Microsoft.SqlServer.Dts.Runtime.Application.LoadPackage(String fileName, IDTSEvents events) at Microsoft.DataTransformationServices.Project.ProjectBuildItemInfo.Update(DateTime lastWriteTime, PackageItem packageItem, Project project, String projectDirectory) at Microsoft.DataTransformationServices.Project.ProjectBuildItemInfo..ctor(String name, DateTime lastWriteTime, PackageItem packageItem, Project project, String projectDirectory) at Microsoft.DataTransformationServices.Project.ProjectBuildValidator.RefreshCache(PackageItem item) at Microsoft.DataTransformationServices.Project.ProjectBuildValidator.CheckBuildItem(PackageItem item) at Microsoft.DataTransformationServices.Project.ProjectBuildValidator.CheckConsistency(String& errors, String buildLogFullName) at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.IncrementalBuildThroughObj(IOutputWindow outputWindow) at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.BuildIncremental(IOutputWindow outputWindow)
(2)
The package failed to load due to error 0xC0011008 "Error loading from XML. No further detailed error information can be specified for this problem because no Events object was passed where detailed error information can be stored.". This occurs when CPackage::LoadFromXML fails.
February 27, 2017 at 2:19 pm
Seems like a problem with the actual XML in the package either getting corrupted (often this occurs due to someone trying to fix a problem by editing the XML directly, but ends up breaking it instead), or has made some kind of change that rendered the package XML invalid. The fastest way to find the source of the problem is to use a text compare tool (like Beyond Compare) and look at the differences between this user's copy and the original. Alternatively, copy the bad package somewhere to at least have a copy of it, and then have the user start fresh with the original, and ask them to step through the steps they took to make changes. Either you find the problem that way, or you at least get back to a known working package. If you have a spare test machine that can be loaded with all the code, you could consider using that as a test bed, ... e.g. writing down all the steps that produce the problem, and then trying to reproduce that error on the test machine. If it reproduces the error, it may be the steps themselves that are the problem, and you may need an alternative sequence of events to get the package properly edited. Try to avoid direct edits of an SSIS package's XML unless you know exactly what you're doing.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 27, 2017 at 11:53 pm
sgmunson - Monday, February 27, 2017 2:19 PMSeems like a problem with the actual XML in the package either getting corrupted (often this occurs due to someone trying to fix a problem by editing the XML directly, but ends up breaking it instead), or has made some kind of change that rendered the package XML invalid. The fastest way to find the source of the problem is to use a text compare tool (like Beyond Compare) and look at the differences between this user's copy and the original. Alternatively, copy the bad package somewhere to at least have a copy of it, and then have the user start fresh with the original, and ask them to step through the steps they took to make changes. Either you find the problem that way, or you at least get back to a known working package. If you have a spare test machine that can be loaded with all the code, you could consider using that as a test bed, ... e.g. writing down all the steps that produce the problem, and then trying to reproduce that error on the test machine. If it reproduces the error, it may be the steps themselves that are the problem, and you may need an alternative sequence of events to get the package properly edited. Try to avoid direct edits of an SSIS package's XML unless you know exactly what you're doing.
Thanks for your answer!
I have not"manually" edited the XML files for the packages.
The only thing I have edited is sort order of the packages in the project file (*.dtproj)
As mentioned, the project works when user downloads it but a meaningless change (like moving a task) makes the project "unbuilable".
I have tried text merge/difftools both for packages and project/solution files to identify if something "strange" or user related has been added, but I have not found anything yet.
February 28, 2017 at 1:44 pm
I didn't see any indication that you followed any of the steps I outlined, but consider that what you are referring to as a "meaningless change" probably isn't, from the point of view of the project. As I never build an entire project, and only build individual packages, I have no experience with any other methodology, but then, I have yet to see any benefit of going beyond an individual package for build purposes. Maybe someone can enlighten me?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 28, 2017 at 1:57 pm
sgmunson - Tuesday, February 28, 2017 1:44 PMI didn't see any indication that you followed any of the steps I outlined, but consider that what you are referring to as a "meaningless change" probably isn't, from the point of view of the project. As I never build an entire project, and only build individual packages, I have no experience with any other methodology, but then, I have yet to see any benefit of going beyond an individual package for build purposes. Maybe someone can enlighten me?
Working in projects allows you to share parameters and connection managers between all packages in the project. That reason alone worked for me. Throw in native logging in SSISDB and configuration via Environments and there's a few reasons right there.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 28, 2017 at 3:02 pm
Phil Parkin - Tuesday, February 28, 2017 1:57 PMsgmunson - Tuesday, February 28, 2017 1:44 PMI didn't see any indication that you followed any of the steps I outlined, but consider that what you are referring to as a "meaningless change" probably isn't, from the point of view of the project. As I never build an entire project, and only build individual packages, I have no experience with any other methodology, but then, I have yet to see any benefit of going beyond an individual package for build purposes. Maybe someone can enlighten me?Working in projects allows you to share parameters and connection managers between all packages in the project. That reason alone worked for me. Throw in native logging in SSISDB and configuration via Environments and there's a few reasons right there.
And it ties your package to your project, which while more secure, can actually turn out to be a seriously painful problem due to the dependency. An SSIS_CONFIG configuration database is more than adequate for a shared set of configurations across environments.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 28, 2017 at 3:07 pm
sgmunson - Tuesday, February 28, 2017 3:02 PMPhil Parkin - Tuesday, February 28, 2017 1:57 PMsgmunson - Tuesday, February 28, 2017 1:44 PMI didn't see any indication that you followed any of the steps I outlined, but consider that what you are referring to as a "meaningless change" probably isn't, from the point of view of the project. As I never build an entire project, and only build individual packages, I have no experience with any other methodology, but then, I have yet to see any benefit of going beyond an individual package for build purposes. Maybe someone can enlighten me?Working in projects allows you to share parameters and connection managers between all packages in the project. That reason alone worked for me. Throw in native logging in SSISDB and configuration via Environments and there's a few reasons right there.
And it ties your package to your project, which while more secure, can actually turn out to be a seriously painful problem due to the dependency. An SSIS_CONFIG configuration database is more than adequate for a shared set of configurations across environments.
Please explain what you mean by 'dependency' in this context. It's never been seriously painful for me.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 1, 2017 at 1:33 am
This has finally been solved.
I had an Microsoft odata driver installed and a package connection manager that used that driver in one of the packages.
Removing that connectoion manager solved the problem.
I find it really strange though that you are able to build the project "untouched" and that this connection is somehow validated only after changes are made to the project or if you import the project from the server (integration services catalog).
Also unfortunate that SSIS was unable to produce a "better" error message.
Thank you all for your input.
March 1, 2017 at 7:26 am
joakim.fenno - Wednesday, March 1, 2017 1:33 AMThis has finally been solved.
I had an Microsoft odata driver installed and a package connection manager that used that driver in one of the packages.
Removing that connectoion manager solved the problem.I find it really strange though that you are able to build the project "untouched" and that this connection is somehow validated only after changes are made to the project or if you import the project from the server (integration services catalog).
Also unfortunate that SSIS was unable to produce a "better" error message.Thank you all for your input.
Phil,
There's a prime example...
Joakim,
SSIS is pretty good at delivering absolutely useless error messages at times, and many of us think it's far too often. However, if you try to think logically about every assumption you're making, and be willing to question those assumptions... you can usually overcome the problem. Of course, a good Google search never hurts. Someone else has usually encountered at least a similar problem before.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 1, 2017 at 8:33 am
I have a theory on this:
In GIT, do you have your BIN folder included? if Visual Studio detects no changes, then when you click on "BUILD" it sees no reason to build it as the currently built version and the source have no differences. Then you make any change and click on BUILD, and it sees changes and then fails to build (in your case).
I highly recommend that you either:
A - not include your BIN folder in GIT (it can make merges messy too as binary files rarely merge nicely)
B - click on REBUILD instead of BUILD
You will notice if you just click on "BUILD", you end up with something like this in your output window:
------ Build started: Project: Integration Services Project2, Configuration: Development ------
Build started: SQL Server Integration Services project: Incremental ...
Build complete -- 0 errors, 0 warnings
========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========
You click on REBUILD and you will get a much longer output that states FULL instead of Incremental.
Same thing applies to any visual studio project. In C# I recommend you do a CLEAN before you build to make sure you are getting fresh files.
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.
March 1, 2017 at 9:01 am
bmg002 - Wednesday, March 1, 2017 8:33 AMI have a theory on this:
In GIT, do you have your BIN folder included? if Visual Studio detects no changes, then when you click on "BUILD" it sees no reason to build it as the currently built version and the source have no differences. Then you make any change and click on BUILD, and it sees changes and then fails to build (in your case).
I highly recommend that you either:
A - not include your BIN folder in GIT (it can make merges messy too as binary files rarely merge nicely)
B - click on REBUILD instead of BUILDYou will notice if you just click on "BUILD", you end up with something like this in your output window:
------ Build started: Project: Integration Services Project2, Configuration: Development ------
Build started: SQL Server Integration Services project: Incremental ...
Build complete -- 0 errors, 0 warnings
========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========You click on REBUILD and you will get a much longer output that states FULL instead of Incremental.
Same thing applies to any visual studio project. In C# I recommend you do a CLEAN before you build to make sure you are getting fresh files.
Yes, GIT does contain the BIN folder (which contaisn the build .ipac file).
Thansk for your input, I will give it a try
March 1, 2017 at 9:47 am
bmg002 - Wednesday, March 1, 2017 8:33 AMI have a theory on this:
In GIT, do you have your BIN folder included? if Visual Studio detects no changes, then when you click on "BUILD" it sees no reason to build it as the currently built version and the source have no differences. Then you make any change and click on BUILD, and it sees changes and then fails to build (in your case).
I highly recommend that you either:
A - not include your BIN folder in GIT (it can make merges messy too as binary files rarely merge nicely)
B - click on REBUILD instead of BUILDYou will notice if you just click on "BUILD", you end up with something like this in your output window:
------ Build started: Project: Integration Services Project2, Configuration: Development ------
Build started: SQL Server Integration Services project: Incremental ...
Build complete -- 0 errors, 0 warnings
========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========You click on REBUILD and you will get a much longer output that states FULL instead of Incremental.
Same thing applies to any visual studio project. In C# I recommend you do a CLEAN before you build to make sure you are getting fresh files.
Option A is pretty much essential, regardless of option B, IMO.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply