Thursday, November 06, 2014

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

        '' 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.

No comments:

Post a Comment