SSIS - Adding Mappings Programatically

Hi Gurus,

We have just started using SSIS object model to create SSIS Packages
programmatically in our Data warehousing Project.

I am stuck with Adding Mappings,

My question is, how can I Map columns using column names?

e.g.

I want to Map

Table_A.CID - > Table_B.RecID
Table_A.FName - > Table_B.First_Name
Table_A.BirthDate - > Table_B.Birth_Date

All samples I found so far, only show how to loop through InputColumns and
call SetUsageType but they dont show how to do one to one mapping by column
name from Source and Destination.

Any help or link will be greatly appreciated.


here is part of my sample code

--//Tables used in SSIS package

CREATE TABLE Table_A (
CID int NULL ,
FName [varchar] (50) NULL ,
Birthdate datetime NULL
)

CREATE TABLE Table_B (
RecID int NULL ,
First_Name [varchar] (50) NULL ,
Birth_date datetime NULL
)
GO

'//Sample code to Create a SSIS Package to Move data from Table_A to Table_B

'// **********************************************
'// Here is subset of my sample code
'// **********************************************

Sub SSISPackageDemo()
app = CreateApplication()
pkg = CreatePackage(PKG_NAME, "This is test SSIS package")

AddConnections() '//First add Source/Destination Connections
AddDataflow() '//Add dataflow to move data from Table_A to Table_B,
also define mappinngs
Savepackage(pkg, True) '//Save Package to SQL Server
ExecutePackage() '//Run the package
End Sub

Sub AddDataflow()
Dim th As TaskHost = TryCast(pkg.Executables.Add("DTS.Pipeline"),
TaskHost)
th.Name = "DataFlow"
th.Description = "The DataFlow task in the DTSAuto sample."

dataFlow = TryCast(th.InnerObject, MainPipe)
'dataFlow.Events = TryCast(pipelineEvents, wrap.IDTSComponentEvents90)

AddOLEDBSource() '//Works fine
AddOLEDBDest() '//Works fine
CreatePath(oledbSource, oledbDest) '//Works fine
AddMappings(oledbDest) '****** Problem [How to map columns] *******
End Sub

Sub AddMappings(ByVal inComponent As IDTSComponentMetaData90)
' Get the design time instance of the component.
Dim designTimeComponent As CManagedComponentWrapper =
inComponent.Instantiate()

‘Iterate through the inputs of the component.
For Each input As IDTSInput90 In inComponent.InputCollection

' Get the virtual input column collection for the input.
Dim vInput As IDTSVirtualInput90 = input.GetVirtualInput()

‘Iterate through the virtual column collection.
For Each vColumn As IDTSVirtualInputColumn90 In
vInput.VirtualInputColumnCollection
'If vColumn.Name = "CID" Then '//Map to RecID in Table_B
'ElseIf vColumn.Name = "FName" Then '//Map to First_Name in
Table_B
'ElseIf vColumn.Name = "BirthDate" Then '//Map to Birth_Date
in Table_B
'End If
' Call the SetUsageType method of the design time instance
of the component.
designTimeComponent.SetUsageType(input.ID, vInput,
vColumn.LineageID, DTSUsageType.UT_READONLY)
Next
Next
End Sub


--
Nayan Patel (MCSE, MCDBA, MCSD.net)
www.binaryworld.net
A Powerful Knowledge Sharing Platform

.

Relevant Pages

  • Re: best practice ... requires
    ... affect the way Perl parses your program, like strict and warnings). ... Do you know what calling a sub with & does? ... so you can see that my package is nested and I am now wondering how ...
    (comp.lang.perl.misc)
  • Re: perl menubased user interface
    ... actually use a perl package OR a hash for this, In a "perl package" ... sub map_method { ... # and they're all shell commands. ... Do whatever you want to wrap a shell command. ...
    (comp.lang.perl.misc)
  • Re: Using a DBI connection in many places (in the code)
    ... # non-exported package globals go here ... sub set_name { ... nothing to do with DBI. ...
    (comp.lang.perl.modules)
  • Perl-5.8.2 NDBM_File appears to insert POD into DB file?
    ... sub STORE { ... these methods should be defined by the package inheriting from ... sub NEXTKEY } ... =head2 Exporting without using Exporter's import method ...
    (comp.lang.perl.misc)
  • Re: multiple packages/classes in one file
    ... > so you can use globals under strict and not just lexicals. ... > this means global to the package that our is used in. ... > sub set_member ... > return $member; ...
    (comp.lang.perl.misc)