Daniel Esser

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 on Macros 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:

Download: Samples.zip

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

Public Module Samples

Public Sub Example01()
Dim a 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 = a.LoadPackage(filename, Nothing)

“ Do some changes
p.CreatorName = „test“

“ Save the package
a.SaveToXml(filename, p, Nothing)

End Sub
End Module