Column Mapping in a Custom Component

Does anyone know how to get destination coulmns to show up in the advanced editor for a custom component? I have a custom flat file destination component that builds the output based on a specific layout. It works as long as the upstream column names match my output names. What I want is to allow non-matching columns to be mapped by the user as they can in a stock flat file destination. The closest that I have been able to come is to get the "column mappings" tab to show up and populate the "Available Input Columns" by setting ExternalmetadataColumnCollection.IsUsed to true on the input. The problem is that the "Available destination columns" box is always empty. I have tried the IsUsed property on the output and pretty much every other property that I could find. On the Input and Output properties all of my columns show up under the output as both External and Output columns. Is there a separate collection for "destination" columns that I can't find? It's getting a little frustrating, is this something that can be done or do I have to write a custom UI to make it happen?

Thanks!
Harry

Have you added columns to the external metadata column collection for you destination? The available destination columns come from this collection so if you haven't put any columns there then there are none to be displayed.

Matt

|||

Matt,

Thanks for your response. Yes, I think that I have added the columns to the external metadata for the output. The output is built from a sql server table containing the layout. The layout (copybook) is supplied via a custom property. All of the columns show up correctly on the input and output page and there is an external and output list there. Here is my code that builds the output. I have also tried setting the SyncronousInputID and it doesn't make any difference.
(Please keep in mind that I am just learning C# )

Thanks!
Harry

IDTSOutput90 output = this.ComponentMetaData.OutputCollection.New();
output.Name = _copybook;
output.ExternalMetadataColumnCollection.IsUsed = true;
output.SynchronousInputID = 0;
IDTSOutputColumnCollection90 outCols = output.OutputColumnCollection;
IDTSExternalMetadataColumnCollection90 extCols = output.ExternalMetadataColumnCollection;
SqlConnection sqlConn = new SqlConnection();
UpdateConnectionString();
sqlConn.ConnectionString = _connectionString;
sqlConn.Open();
SqlCommand sqlCmd = sqlConn.CreateCommand();
sqlCmd.CommandText = "Select ColumnName, ColumnType, Length, Precision, Scale" +
" From " + _copybook +
" Order By ColumnSeq";
SqlDataReader sqlRdr = sqlCmd.ExecuteReader();
DataType dtColType;
while (sqlRdr.Read()) {
string ColumnName = sqlRdr.GetString(0).Trim();
string ColumnType = sqlRdr.GetString(1).Trim();
Int16 Len = sqlRdr.GetInt16(2);
Int16 Prec = sqlRdr.GetInt16(3);
Int16 Scale = sqlRdr.GetInt16(4);
IDTSOutputColumn90 outColumn = outCols.New();
outColumn.Name = ColumnName;
switch (ColumnType) {
case "Z":
dtColType = DataType.DT_NUMERIC;
Len = 0;
break;
case "P":
dtColType = DataType.DT_NUMERIC;
Len = 0;
break;
case "B":
if (Len > 4)
dtColType = DataType.DT_I8;
else if (Len > 2)
dtColType = DataType.DT_I4;
else
dtColType = DataType.DT_I2;
Len = 0;
break;
default:
dtColType = DataType.DT_WSTR;
break;
}
outColumn.SetDataTypeProperties(dtColType, Len, Prec, Scale, 0);
IDTSExternalMetadataColumn90 extColumn = extCols.New();
extColumn.Name = outColumn.Name;
extColumn.DataType = outColumn.DataType;
extColumn.Length = outColumn.Length;
extColumn.Precision = outColumn.Precision;
extColumn.Scale = outColumn.Scale;
extColumn.CodePage = outColumn.CodePage;
outColumn.ExternalMetadataColumnID = extColumn.ID;
}

sqlRdr.Close();
sqlCmd.Dispose();
sqlConn.Close();
sqlConn.Dispose();

|||

Well, I don't know if this is the only problem but destinations don't have outputs only inputs (genrally speaking anyhow). A destination usually just has an input (and many have an error output, but that is just for rows that fail to get written to the destination. You would then add external columns to the input's external columns collection and map the column to the associated input column.

Matt

|||

Matt,

Thanks, that was it. I didn't know that the mapping relation was between the input and the external metadata for the input. It makes sense, I was just stuck on mapping input to output. This is really supposed to be a tranformation so that's where the output came from. I just made it a destination to see if that was my issue. It probably did look a bit odd to have an output on a destination

Again, thank you for your help.

Harry