Issue While Programmatically Slowly Chaging Dimension.

Hi,

I am working on Creating an SSIS package to add Slowly Chaging Dimension to the package programmatically.

I have done the following steps:
1. Choosing the connection manager to access the data source that contains the dimension table that you want to update.
You can select from a list of connection managers that the package includes.
2. Choosing the dimension table or view you want to update.
After you select the connection manager, you can select the table or view from the data source.
3. Setting key attributes on columns and map input columns to columns in the dimension table.
You must choose at least one business key column in the dimension table and map it to an input column. Other input columns can be mapped to columns in the dimension table as non-key mappings.
4. Choose the change type for each column.
o Changing attribute overwrites existing values in records.
o Historical attribute creates new records instead of updating existing records.
o Fixed attribute indicates that the column value must not change.
Code://Set the Key Element as part of Creating the SCD Transformation:

instance.SetInputColumnProperty(ID, this.SlowlyChaningDim.InputCollection[0].InputColumnCollection[1].ID, "ColumnType", 1);

//Set one of the ColumnType Properties as Changing Attribute Updates Output

instance.SetInputColumnProperty(ID, this.SlowlyChaningDim.InputCollection[0].InputColumnCollection[15].ID, "ColumnType", 2);

//Set one of the ColumnType Properties as Historical Attributes Updates output.

instance.SetInputColumnProperty(ID, this.SlowlyChaningDim.InputCollection[0].InputColumnCollection[85].ID, "ColumnType", 3);



Now i create an OleDbCommand Transformation to capture "Changing Attribute Updates" (Type-1) output.



this.OledbCommand1 = this.dataflow.ComponentMetaDataCollection.New();

this.OledbCommand1.ComponentClassID = "DTSTransform.OleDBCommand.1";

this.OledbCommand1.Name = "OledbCommand1";

this.OledbCommand1.Description = "ChangingColumnAttribute";



CManagedComponentWrapper oledbinstance = this.OledbCommand1.Instantiate();

oledbinstance.ProvideComponentProperties();



this.OledbCommand1.RuntimeConnectionCollection[0].ConnectionManagerID = package.Connections["OLEDBDestination"].ID;

this.OledbCommand1.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections["OLEDBDestination"]);

oledbinstance.SetComponentProperty("CommandTimeout", 0);

oledbinstance.SetComponentProperty("DefaultCodePage",1252);



StringBuilder sbquery = new StringBuilder();

sbquery.Append("UPDATE SIRWorkdm..[Engagement] SET [BillingType] = ? WHERE [EngagementId] = ?"); //Here BillingType is the "ChangingColumnAttribute" and EngagementId is the key

oledbinstance.SetComponentProperty("SqlCommand", sbquery.ToString());

---- Need inputs as to how to map the Source and ExternalMetadataColumns.

--------- -------- ------------

this.dataflow.PathCollection.New().AttachPathAndPropagateNotifications(this.SlowlyChaningDim.OutputCollection[3], this.OledbCommand1.InputCollection[0]);





Please advice as to how to proceed further

Thanks,

Sriharsh