SSIS Child Package Configuration

• rollie-728320

  Valued Member

  Points: 52

  More actions

  July 23, 2008 at 10:54 am

  #210418

  Hello i'm trying to build a ETL system, that read from the database the mapping (where i've got the source,destination and transformations info) the code down written is the parent code, that loads the child, and defines its configurations.

  but i get a wierd problem... the destination configuration doesn't stick. the source absorbs the configuration but not the destination.

  when I save, the dataflow gets deleted on the saved .dtsx

  what am I doing wrong? i've started this code with an example of building a package from scratch.

  any help is welcome, Thanks!

  Imports System

  Imports System.Data

  Imports System.Math

  Imports Microsoft.SqlServer.Dts.Runtime

  Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

  Public Class ScriptMain

  Dim pkg As New Package()

  Dim app As New Application()

  Dim pkgName As String = "C:\CLF_IS\CLF_IS\CLF_IS_Filho.dtsx"

  Dim pkgName2 As String = "C:\CLF_IS\CLF_IS\CLF_IS_Filho_SAVED.dtsx"

  Dim source As IDTSComponentMetaData90

  Dim destination As IDTSComponentMetaData90

  Dim e As Executable

  Dim thMainPipe As TaskHost

  Dim dataFlowTask As MainPipe

  Dim src As CManagedComponentWrapper

  Dim dst As CManagedComponentWrapper

  Public Sub Pacote()

  pkg = app.LoadPackage(pkgName, Nothing)

  e = pkg.Executables.Item(0)

  thMainPipe = CType(e, TaskHost)

  dataFlowTask = CType(thMainPipe.InnerObject, MainPipe)

  'pkg.Connections("Fonte").ConnectionString = "Data Source=MOZER;Initial Catalog=Accipiens;Provider=SQLNCLI.1" & _

  '";Integrated Security=SSPI;Auto Translate=False;"

  'pkg.Connections("Destino").ConnectionString = "Data Source=MOZER;Initial Catalog=CLF_DW;Provider=SQLNCLI.1" & _

  '";Integrated Security=SSPI;Auto Translate=False;"

  End Sub

  Public Sub Fonte()

  source = dataFlowTask.ComponentMetaDataCollection.Item(0)

  src = source.Instantiate

  src.ProvideComponentProperties()

  source.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(pkg.Connections("Fonte"))

  source.RuntimeConnectionCollection(0).ConnectionManagerID = pkg.Connections("Fonte").ID

  src.SetComponentProperty("AccessMode", 0)

  src.SetComponentProperty("OpenRowset", "[dbo].[Agenda]")

  src.AcquireConnections(Nothing)

  src.ReinitializeMetaData()

  src.ReleaseConnections()

  End Sub

  Public Sub Destino()

  destination = dataFlowTask.ComponentMetaDataCollection.Item(1)

  dst = destination.Instantiate

  dst.ProvideComponentProperties()

  destination.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(pkg.Connections("Destino"))

  destination.RuntimeConnectionCollection(0).ConnectionManagerID = pkg.Connections("Destino").ID

  dst.SetComponentProperty("AccessMode", 0)

  dst.SetComponentProperty("OpenRowset", "[dbo].[Agenda]")

  dst.AcquireConnections(Nothing)

  dst.ReinitializeMetaData()

  dst.ReleaseConnections()

  End Sub

  Public Sub Map()

  Dim Input As IDTSInput90 = destination.InputCollection(0)

  Dim vInput As IDTSVirtualInput90 = Input.GetVirtualInput()

  For Each vColumn As IDTSVirtualInputColumn90 In vInput.VirtualInputColumnCollection

  Dim vCol As IDTSInputColumn90 = dst.SetUsageType(Input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READWRITE)

  dst.MapInputColumn(Input.ID, vCol.ID, Input.ExternalMetadataColumnCollection(vColumn.Name).ID)

  Next

  End Sub

  Sub Save()

  app.SaveToXml(pkgName2, pkg, Nothing)

  End Sub

  Public Sub Main()

  Pacote()

  Fonte()

  Destino()

  Map()

  'Save()

  'For Each str As Microsoft.SqlServer.Dts.Runtime.ConnectionManager In pkg.Connections

  ' MsgBox(str.Name & " : " & str.ConnectionString)

  'Next

  'For Each prop As IDTSCustomProperty90 In source.CustomPropertyCollection

  ' MsgBox(prop.Name & " : " & prop.Value.ToString(), MsgBoxStyle.Information, "Fonte")

  'Next

  'For Each prop As IDTSCustomProperty90 In destination.CustomPropertyCollection

  ' MsgBox(prop.Name & " : " & prop.Value.ToString(), MsgBoxStyle.Exclamation, "Destino")

  'Next

  pkg.Execute()

  Dts.TaskResult = Dts.Results.Success

  End Sub

  End Class