15.02.2011

BIDS Goodie #1: How to use macros with SSIS

Technical Value

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:

  1. Imports System
  2. Imports EnvDTE
  3. Imports EnvDTE80
  4. Imports EnvDTE90
  5. Imports EnvDTE90a
  6. Imports System.Diagnostics
  7. Imports Microsoft.SqlServer.Dts.Runtime
  8.  
  9. Public Module Samples
  10.  
  11.     Public Sub Example01()
  12.         Dim app As New Microsoft.SqlServer.Dts.Runtime.Application()
  13.         Dim filename As String
  14.  
  15.         filename = DTE.ActiveDocument.FullName()
  16.         Dim p As Package
  17.  
  18.         '' Save the document before making changes
  19.         DTE.ActiveDocument.Save()
  20.  
  21.         '' Open the package
  22.         p = app.LoadPackage(filename, Nothing)
  23.  
  24.         '' Do some changes
  25.         Dim cm As ConnectionManager
  26.         Dim connString, dataSource, catalog, provider, appName As String
  27.  
  28.         dataSource = "."
  29.         catalog = "AdventureWorksDW2008"
  30.         provider = "SQLNCLI10.1"
  31.         appName = p.Name
  32.         connString = String.Format("Data Source={0};Initial Catalog={1};Provider={2};
  33.             Integrated Security=SSPI;Application Name={3};Auto Translate=False;"
  34.              , dataSource, catalog, provider, appName)
  35.  
  36.         cm = p.Connections.Add("OLEDB")
  37.         cm.ConnectionString = connString
  38.         cm.Description = "AdventureWorks DW"
  39.         cm.Name = "AdventureWorksDW"
  40.  
  41.         '' Save the package
  42.         app.SaveToXml(filename, p, Nothing)
  43.     End Sub
  44. End Module

Download: Samples.zip

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

Teilen auf

Newsletter Anmeldung

Abonnieren Sie unseren Newsletter!
Lassen Sie sich regelmäßig über alle Neuigkeiten rundum ORAYLIS und die BI- & Big-Data-Branche informieren.

Jetzt anmelden