The Fix for a Mysterious SSIS Error

When running an SSIS project, I was getting a very strange error: All data flow components of a child package succeeded with green check marks, but the entire package is marked with a red cross, indicating that it failed. In the "Progress" log, the error says: [... [1875]] Error: Category does not exist.

It turns out that the error was caused by a debug break point in a script component. Debug break point in a script component usually works, but when a data viewer is turned on, or an earlier debug break point was hit in a different script component, a subsequent debug break point in a script component would usually fail, probably due to lack of available RAM or something.

This type of failure would be harmless if the package is the last one in the control flow. But if it is in the middle of the control flow, the entire task would fail.

The fix is easy, of course. All you would need to do is to open the script component source code, and remove the break point.

Building LightSwitch HTML Clients with TFS Builder Agent

LightSwitch HTML Client is now included in Visual Studio 2012 Update 3. It is really a very nice thing when you want to build a tool quickly for internal use. The problem is that it does not play nice with TFS build agents.

First of all, if you are running a 64 bit server, MSBuild usually defaults to use its 64 bit version. The problem is that "Microsoft.VisualStudio.Settings.11.0.dll" that comes with Visual Studio 2012 SDK has only a 32 bit version. So when the TFS build agent runs, you will get errors saying that "The system cannot find the file specified. File name: 'Microsoft.VisualStudio.Settings.11.0, Version=11.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'...". The file is actually right there in the Visusal Studio SDK common assembly folder. you can even add it to the GAC. It will not help because it is a 32 bit assembly. You will have to switch to the 32 bit MSBuild in your build definition:

Build Definition Setting for Building LightSwitch Applications

Once you set the MSBuild platform to "X86", the problem goes away. By the way, this does not change the build platform setting in your solution. It only applies to the MSBuild process. So everything works out as far as fixing the missing DLL problem.

The problems for building LightSwitch applications do not end there, unfortunately. Once you get pass the DLL problem, you will most likely see that the build agent starts to complain that the server output is missing: "C:\Program Files (x86)\MSBuild\Microsoft\VisualStudio\LightSwitch\v3.0\Microsoft.LightSwitch.targets (108): Could not copy the file "...\obj\Release\Application.Server.dll" because it was not found.

Usually TFS build agent would build the release configuration of a solution. With LightSwitch projects (*.ls3proj) files, there are actually two sub-projects; one for the server and one for the HTML client. When building the LightSwitch project using MSBuild, the selected build configuration information gets lost. It never gets passed to the two sub-projects. When no build configuration is specified, these projects default to debug builds. When the time comes for the "ls3proj" to copy the output files, they are created in the wrong configuration, any you will get the errors described above.

A quick fix for this is to change the default configuration of these sub-projects. In the .Server folder, open the "*.csproj" file with NotePad, and change the default configuration to "Release":

Server Project File

Similarly, you can change the HTML client default to release as well. In the .HTMLClient folder, open the "*.jsproj" file with NotePad, and add a default configuration that is set to "Release":

HTML Client Project File

Of course, a better solution would be to fix "Microsoft.LightSwitch.Build.Tasks.targets" file to pass selected configuration to the sub-projects. That is a little more complicated. Since I do not need to create LightSwitch projects very often, fixing the project files is a much simpler solution.

Developing SSIS Packages with SSDT in Visual Studio 2012

Unlike older versions of Visual Studio, Visual Studio 2012 relies on SQL Server Data Tools (SSDT) for SQL project support. However, after installing SSDT (http://msdn.microsoft.com/en-us/jj650015), you will notice that the old Business Intelligence project types are missing. As a result, Integration Services (SSIS) project type, Reporting Services (SSRs) project type, and Analysis Services (SSAS) project type are not available when you try to create a  new project.

It turns out that "SQL Server Data Tools - Business Intelligence for Visual Studio 2012 (SSDT-BI)" is a package that is separate from SSDT. It can be downloaded from here: http://www.microsoft.com/en-us/download/details.aspx?id=36843.

Installing SSDT-BI is fairly straightforward, except the following:

1. It will launch the SQL Server installer. You will need to check the   "SQL Server Data Tools - Business Intelligence" as an option.
2. If you are running a 64 bit OS, you will need to select "create a new SQL instance". Otherwise you will get an "architecture mismatch" error, and the installation will fail. The installer will not actually create a new instance of SQL.

Once this is completed, you will be able to create SSIS packages with SSIS packages like before.