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