SSRS Report Deployment Connection Error

A co-worker received the following error message when attempting to deploy a report from Visual Studio to a Sharepoint document library setup for Integrated Mode with SSRS.

“A connection could not be made to the report server…”

The message was followed by some additional information that indicated “Client found response content type of ‘text/html; charset=utf-8′, but expected ‘text/xml’.” as well as some additional HTML tags.  The error dialog was rather ugly.  Other developers working on the same solution with other reports were not having this problem.

The gist of the problem was that the developer’s PC did not have SQL Server 2005 Service Pack 2 installed.  Once the Service Pack was installed the error went away and the report could be deployed.

Format XML in Visual Studio

Here’s an easy tip for formatting XML in Visual Studio which apparently isn’t very well known.  There are times I find myself using Visual Studio to view/edit an XML file, and the source XML is tightened with no line breaks (for whatever reason) after the nodes.  Below is one example.

<?xml version="1.0"?>
<BrowserInfo><Browser><Name>Microsoft Internet Explorer</Name><Path>"C:\Program Files\Internet Explorer\iexplore.exe"</Path><Resolution>0</Resolution><IsDefault>True</IsDefault><DDE><Service>IExplore</Service><TopicOpenURL>WWW_OpenURL</TopicOpenURL><ItemOpenURL>"%s",,0xffffffff,3,,,,</ItemOpenURL><TopicActivate>WWW_Activate</TopicActivate><ItemActivate>0xffffffff,0</ItemActivate></DDE></Browser><InternalBrowser><Resolution>0</Resolution><IsDefault>False</IsDefault></InternalBrowser></BrowserInfo>
     

You can use the Format Document (Ctrl+E, D) command from the Edit->Advanced menu to nicely format your XML document:

This results in a much nicer formatting of the hierarchical information:

<?xml version="1.0"?>
<BrowserInfo>
  <Browser>
    <Name>Microsoft Internet Explorer</Name>
    <Path>"C:\Program Files\Internet Explorer\iexplore.exe"</Path>
    <Resolution>0</Resolution>
    <IsDefault>True</IsDefault>
    <DDE>
      <Service>IExplore</Service>
      <TopicOpenURL>WWW_OpenURL</TopicOpenURL>
      <ItemOpenURL>"%s",,0xffffffff,3,,,,</ItemOpenURL>
      <TopicActivate>WWW_Activate</TopicActivate>
      <ItemActivate>0xffffffff,0</ItemActivate>
    </DDE>
  </Browser>
  <InternalBrowser>
    <Resolution>0</Resolution>
    <IsDefault>False</IsDefault>
  </InternalBrowser>
</BrowserInfo>

Deploying Custom SSIS Connection Manager

When deploying a custom SSIS Connection Manager component you need to ensure the DLL assembly is deployed into the GAC (C:\Windows\Assembly) as well as the \DTS\Connections folder.  This is true even when deploying to a non-developer PC, such as a test server or production server.

The documentation found in the SQL Server Books Online seems to indicate you need to copy the DLL to the \DTS\Connections folder so that the BIDS designer can locate the custom components to use when authoring a package.  This is true, but a little misleading.  In my experience the assembly needs to be copied to the \DTS\Connections folder even on servers where the packages will just be executing.  The documentation does provide some help on how to find the specific path the assembly needs to be copied to, including the registry key which contains the full path to the \DTS folder.

Note, I did not find this to be the case for custom SSIS Data Flow components.  Placing the DLL in the \DTS\PipelineComponents folder was only necessary on the developer PC when designing the package.  Just copying it to the GAC was sufficient for servers which execute the packages using the custom data flow components.

Build SSIS Custom Components on TeamBuild

For our SSIS custom components (custom connection managers, custom data sources, custom data transformations, etc.) we have them deployed into the GAC and copied into the appropriate \DTS folder so that they become available in the design tools to be dropped on a design canvas.  This is accomplished via the Build Events in Visual Studio:

We also use TFS and TeamBuild for daily builds.  We run unit tests on our database stored procs and functions, and on our custom business assemblies.  No unit testing on SSIS components yet, but I am interested in what’s happening in this space.  The problem we ran into with TeamBuilds on the SSIS custom components is that we didn’t want/need the build events to occur.  In fact our TeamBuilds failed because the paths couldn’t be resolved.

To resolve this I opened up the project files and modified the PreBuildEvent and PostBuildEvent tags to include a condition checking if the build was occurring within Visual Studio or not.  The tags look like this now:

<PropertyGroup>
  <PreBuildEvent Condition="'$(BuildingInsideVisualStudio)'=='true'">"$(DevEnvDir)..\..\SDK\v2.0\Bin\gacutil.exe" /f /u "$(TargetPath)"
del "C:\Program Files\Microsoft SQL Server\90\DTS\PipelineComponents\$(TargetFileName)"</PreBuildEvent>
  <PostBuildEvent Condition="'$(BuildingInsideVisualStudio)'=='true'">"$(DevEnvDir)..\..\SDK\v2.0\Bin\gacutil.exe" /if "$(TargetPath)"
xcopy "$(TargetPath)" "C:\Program Files\Microsoft SQL Server\90\DTS\PipelineComponents"</PostBuildEvent>
</PropertyGroup>