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.
No comments:
Post a Comment