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

SriharshDid you ever get this working. I have exactly the same problem as your self, but have been unable to find good examples on the web. Would appreciate it if you could provide a working example if you have one. Sorry to be a hassleRegardsMike

Hello Mike, although your question is already from quite a while ago - I have just been looking for a solution to your question and haven't found an answer to it. But finally I have succeeded by trial and error :) Set up your OleDB command as described above. After assigning the SQL statement you have to update the component's meta data: wrapper.AcquireConnections(null); wrapper.ReinitializeMetaData(); wrapper.ReleaseConnections(); Then create the mapping for the parameters (my code assumes that the order in the list is also the order of the parameters): IDTSInput100 input = component.InputCollection[0]; IDTSVirtualInput100 vInput = input.GetVirtualInput(); List<string> paramValueColumns = new List<string>(); paramValueColumns.Add("DataFlowColumn1"); paramValueColumns.Add("DataFlowColumn2"); paramValueColumns.Add("DataFlowColumn3"); foreach (IDTSVirtualInputColumn100 vColumn in vInput.VirtualInputColumnCollection) { if (paramValueColumns.Contains(vColumn.Name)) { string paramName = string.Format("Param_{0}", paramValueColumns.IndexOf(vColumn.Name)); IDTSInputColumn100 column = wrapper.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY); wrapper.MapInputColumn(input.ID, column.ID, input.ExternalMetadataColumnCollection[paramName].ID); } else { wrapper.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_IGNORED); } } best regards, Gerald