Post reply

Applying script using a DLL applying DTS reference

  • May 16, 2014 at 3:17 pm

    #285658

    GIVEN:

    I am looking into updating multiple SSIS packages in SQL Server 2008 R2 that include script written in C# that then apply common methods that use DTS commands. These commands are applying the following two references:

    Microsoft.SqlServer.Dts.Pipeline.Wrapper

    using Microsoft.SqlServer.Dts.Runtime.Wrapper

    OBJECTIVE:

    I would like to clean up the redundancy in the script code by moving the commonly applied methods to a DLL file that can then be used by script tasks in all of the SSIS packages.

    PROBLEM:

    The problem is that when I open Visual Studio 2010 to create a new class, I am not seeing the same references available under either .NET or COM. So, calls like ComponentMetaData and oOdbcConnection in the code cannot be defined. Is it possible to create methods in a DLL class that apply DTS calls?

    As an example, here is one method I've created in the DLL file in a class called Logging.

    using System;

    using System.Linq;

    using System.Text;

    using System.Data;

    using Microsoft.SqlServer.Dts.Pipeline.Wrapper;

    using Microsoft.SqlServer.Dts.Runtime.Wrapper;

    using System.Data.Odbc;

    using System.Collections;

    using System.Collections.Generic;

    namespace xxx.xxx.common

    {

    public class Logging

    {

    private void LMessage(string tableName, string fieldName, string fieldValue, bool bDebug)

    {

    if (bDebug)

    {

    bool bTrash = false;

    int iTrash = 1;

    this.ComponentMetaData.FireInformation(iTrash, tableName, fieldName, fieldValue, iTrash, ref bTrash);

    }

    }

    }

    }

    The ComponentMetaData in the last command line in the above code requires a definition in this example. If the two Microsoft.SqlServer.Dts references cannot be applied here, is there an alternative approach that will work?

    Thanks,

    Grant

  • May 20, 2014 at 2:19 pm

    #1715141

    I was able to resolve this issue by generating the DLL file using Visual Studio 2008, instead of Visual Studio 2010. I had to first install Visual Studio 2008, which did not overwrite the Business Intelligence Development Studio. Once this was done, the BIDS-related (DTS-related) assemblies were available for selection among the .NET references when creating the classes for the solution to become the DLL file. Since the script task in the SSIS package built with SQL Server 2008 R2 is applying a .NET Framework version of 2.0, I made certain to do the same with the DLL file. At the same time this was done, I also added it to the GAC with a strong name key file.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply