At my current client we use MSBuild and Octopus to set up automated builds and continuous integration for our solutions. Every commit, a build server grabs the latest code, MSBuild compiles it, a NuGet package gets dropped into a drop folder, and Octopus then pulls the package from a package feed. Then we manually deploy as needed. This usually works pretty well, except… SSIS.
Builds for one of our SSIS packages started failing in July. A couple developers took some stabs at getting it working again, but were unable to do so. Then the backlog started filling. Seven months later, and here I am trying to resolve the build. I’m basically starting from scratch, so I don’t have a lot of knowledge of what may have caused the initial failures. But no problem!
I was able to resolve the build problems by logging directly into the build server and running build commands locally to get more specific errors. Eventually, I identified a third party module that was not present on our build server as the culprit of the problems, despite a couple red herrings. I discuss my process below.
In Visual Studio, each commit would see the following when we viewed the corresponding Build Status
“C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\devnenv.com” MySsisSolution.sln /rebuild Trunk exited with code 1.
Not a very helpful or verbose error.
Running this command locally produced no error. The build would succeed.
However, if I ran the build command on the build server, I got the following error:
C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\devnenv.com MySsisSolution.sln /rebuild Trunk
File ‘D:\TFSBuilds\23\MySsisSolution.Trunk\Sources\obj\Trunk\MyPackage.dtsx’ get updated.
Applied active configuration to ‘Project.params’.
System.ArgumentException: Value does not fall within the expected range. at Microsoft.SqlServer.Dts.Runtime.Interop.ProjectInterop.ReferencePackage(Package package, String packageLocation) at Microsoft.SqlServer.Dts.Runtime.PackageItem.Load(IDTSEvents events) at Microsoft.SqlServer.Dts.Runtime.PackageItem.get_Package() at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.IncrementalBuildThroughObj(IOutputWindow outputWindow) at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.BuildIncremental(IOutputWindow outputWindow) “Value does not fall within expected range” is an incredibly generic SSIS error. Googling revealed a thousand causes, and none of them were are compile-time.
At this point, I did two things:
- Isolate the build problem to the package itself. I verified that there was nothing wrong with the build server by duplicating the build definition and building a blank slate SSIS solution. No problem. Ignored the red herring project.params. It might look like the error throws at Project.params. In fact, the error was throwing when it was building my first package. I know this because I was able to compare my local output to see which step comes after “Applied active configuration to ‘Project.params’.” Notice the past tense. This step was successful.
- Using my local output, I noted that the problem was at the step right after “Applied active congiruation to Project.params.” it was at the step, “Build package MyPackage.dtsx.”
So, I deleted MyPackage.dtsx and tried again. Still the same error.
Then, I deleted another package. And another. Eventually I deleted all the packages. Boom, a new error.
This time, the top of the call stack showed the following error:
Unable to create the type with the name ‘AzureStorage’
I have an old connection manager that uses this third party module. We don’t use it anymore. Delete!
After deleting, the solution was able to build.
I rolled back my package deletions, and the solution was still able to build.
So, what have we learned?
- The TFS Build Status window gives less detail than running the build command by remoting in to the remote server. All I got was a “Command exited with code 1.” Great. Always remote in to your build server to diagnose problems. We can get to more detailed errors by first solving for generic errors. In our case, we had errors with packages, so we removed packages. Then we learned that the error is not directly caused by a package at all, but rather an unknown type.
- Pay special attention the MSBuild output. My error kept getting thrown after “Applied active configuration to Project.params.” This caused me to look at Project.params for the problem. In fact, the error was throwing at the step immediately after this step. See a list of the successful build output locally, and comparing that to the failed remote build output, allowed me to immediately identify the true step that was failing. It was a package build step.
- The fact that /rebuild worked locally but not remotely should have immediately made us check our third party modules that were integrated into SSIS. Putting a reference to AzureStorage on the build server would have prevented this from the beginning.