Using Macros in SSIS

Do you ever used macros in Visual Studio? You can do also in BIDS and SSIS…

There are many recurring tasks via ETL development. I will now explain how to write a macro to automate these recurring tasks. For example we want to add a frequently used connection manager. Unfortunately the Visual Studio Macros environment isn’t set up correctly for this.

Go to the Microsoft SQL Server SDK folder: C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies

Copy the following assemblies: Microsoft.SqlServer.DTSPipelineWrap.dll Microsoft.SQLServer.DTSRuntimeWrap.dll

To the Visual Studio Public Assemblies folder: C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PublicAssemblies

Now start Visual Studio and open an SSIS project. Select Other Windows and then Macro Explorer (Alt+F8). Right click onMacros in the Macro Explorer and select New Macro Project called MySSISMacros. Create a new module called Samples. Double click Samples to open the Macro Editor.

In the Macro Editor right click at the MySSISMacros project and select Add reference. Add references to the assemblies above and click OK.

Now you are ready to write macros for SSIS development. Here is a very simple example:

Imports System

Imports EnvDTE

Imports EnvDTE80

Imports EnvDTE90

Imports EnvDTE90a

Imports System.Diagnostics

Imports Microsoft.SqlServer.Dts.Runtime

Public Module Samples

    Public Sub Example01()

        Dim app As New Microsoft.SqlServer.Dts.Runtime.Application()

        Dim filename As String

        filename = DTE.ActiveDocument.FullName()

        Dim p As Package

        '' Save the document before making changes

        DTE.ActiveDocument.Save()

        '' Open the package

        p = app.LoadPackage(filename, Nothing)

        '' Do some changes

        Dim cm As ConnectionManager

        Dim connString, dataSource, catalog, provider, appName As String

        dataSource = "."

        catalog = "AdventureWorksDW2008"

        provider = "SQLNCLI10.1"

        appName = p.Name

        connString = String.Format("Data Source={0};Initial Catalog={1};Provider={2};

             Integrated Security=SSPI;Application Name={3};Auto Translate=False;"

             , dataSource, catalog, provider, appName)

        cm = p.Connections.Add("OLEDB")

        cm.ConnectionString = connString

        cm.Description = "AdventureWorks DW"

        cm.Name = "AdventureWorksDW"

        '' Save the package

        app.SaveToXml(filename, p, Nothing)

    End Sub

End Module

Now open a package and execute the macro. It will add a connection to the active package.