CODEWITHSUNDEEP
vb.netssis
Brett
Brett

Reputation: 33

How to edit SSIS packages programatically in VB?

We have created a workflow process as an SSIS package and would like to find a way of gaining access to this code so that through vb.net we can dynamically access and run that code. For example, we would like to change the data sources being used, or change the column mappings of existing packages and then execute them from a vb.net application. Please advise the best way to do this.

Upvotes: 1

Views: 2784

Answers (1)

billinkc
billinkc

Reputation: 61201

You will find some of your tasks easy, others not so much.

Generally speaking, you'll be interested in reading the Developers Guide to Integration Services. Of particular interest will be Building Packages Programmatically and Running and Managing Packages Programmatically.

For example, to run a package from VB.NET

Imports Microsoft.SqlServer.Dts.Runtime

Module Module1

  Sub Main()

    Dim pkgLocation As String
    Dim pkg As New Package
    Dim app As New Application
    Dim pkgResults As DTSExecResult

    pkgLocation = _
      "C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services" & _
      "\Package Samples\CalculatedColumns Sample\CalculatedColumns\CalculatedColumns.dtsx"
    pkg = app.LoadPackage(pkgLocation, Nothing)
    pkgResults = pkg.Execute()

    Console.WriteLine(pkgResults.ToString())
    Console.ReadKey()

  End Sub
End Module

To change the connection manager programmatically, it'd be the VB.NET equivalent of

ConnectionManager item = ep.Connections["MyConnectionManagerName"]
item.ConnectionString = @"Provider=SQLNCLI10.1;Data Source=Server1;Initial Catalog=ABC;Integrated Security=SSPI;";

Changing column mappings, that's where it's going to get interesting, for all definitions of the word interesting. I'm have a distilled example but it takes some work and you'll want to really understand the whole object model (and I hope you like COM). EzAPI might be of some assistance in that area.

Upvotes: 2

Related Questions