Can't Compile SSIS Component; Missing Assembly

问题:

There is no standard control flow task in SSIS to download a file over HTTP. I have tried some workarounds (Execute Process wget, Script Task HttpClientConnection), but have come to the conclusion that thew most robust and reusable way to perform this task is to create a custom component.

I have tried to do this by following Ray Gorski's guide, but have run into problem in my development environment.

The problem is that Visual Studio 2008 does not recognise the Microsoft.SqlServer.Dts namespace. It complains with the error message "The type or namespace name 'Dts' does not exist in namespace 'Microsoft.SqlServer' (are you missing an assembly reference?)".

I found advice on the MSDN forum that says you can add a reference by adding the assembly ManagedDTS.dll found in C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\, but this directory does not exist on my system. I think this is because I am using SQL Server 2008 and not SQL Server 2005.

How do I fix this problem?

Here is my code, which is based on the sample in Ray Gorski's guide. There is no code here to actually accomplish my task, just an empty stub, which doesn't compile.

using System;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Runtime.Design;

namespace HTTPControlTask
{
    [DtsTask(
        Description = "HTTP",
        DisplayName = "HTTP",
        TaskContact = "Iain Elder",
        TaskType = "SSIS Help Task",
        RequiredProductLevel = DTSProductLevel.None)]
    public class HTTPControlTask : Task, IDTSComponentPersist
    {
    }
}

Here is a screenshot of the errors I see when I try to compile it:

回答1:

On 64-bit Operating System, you can find the file Microsoft.SQLServer.ManagedDTS.dll in the following path:

C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies

On 32-bit Operating System, you can find the file Microsoft.SQLServer.ManagedDTS.dll in the following path:

C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies

The folder 90 under Microsoft SQL Server represents SQL Server 2005.

The folder 100 under Microsoft SQL Server represents SQL Server 2008.

回答2:

Just a small update on the earlier answer for SQL2012

DLL files are in 110.

Now even after adding the reference you will get the same error if you build your project with .Net3.5 or older profiles.

for SQL2012 you must use .Net4.0 or later else Visual Studio will show you the same error.

回答3:

After install SS2012, script is ssis packages developed in ss2008r2 have the error described. After several tentatives trying to resolve, i have install the ss2008r2 sdk, open all the scripts and close, and everything is ok now.

Thanks Mark