public DerivedColumn AddDerivedColumn(DerivedColumnItem derivedColumnItem) { if (!derivedColumnItem.IsReplacingColumn) { Pipe.IDTSOutputColumn100 col = SSISDefaultOutput.OutputColumnCollection.New(); col.Name = derivedColumnItem.Name; col.SetDataTypeProperties((Wrapper.DataType)derivedColumnItem.DataType, derivedColumnItem.Length, derivedColumnItem.Precision, derivedColumnItem.Scale, derivedColumnItem.CodePage); col.ExternalMetadataColumnID = 0; col.ErrorRowDisposition = Pipe.DTSRowDisposition.RD_FailComponent; col.TruncationRowDisposition = Pipe.DTSRowDisposition.RD_FailComponent; Pipe.IDTSCustomProperty100 propEx = col.CustomPropertyCollection.New(); propEx.Name = "Expression"; propEx.Value = derivedColumnItem.Expression; Pipe.IDTSCustomProperty100 propFex = col.CustomPropertyCollection.New(); propFex.Name = "FriendlyExpression"; propFex.Value = derivedColumnItem.Expression; } else { Pipe.IDTSInputColumn100 col = base.FindSSISInputColumn(derivedColumnItem.ReplaceColumnName); col.ExternalMetadataColumnID = 0; col.ErrorRowDisposition = Pipe.DTSRowDisposition.RD_FailComponent; col.TruncationRowDisposition = Pipe.DTSRowDisposition.RD_FailComponent; Pipe.IDTSCustomProperty100 propEx = col.CustomPropertyCollection["Expression"]; propEx.Value = derivedColumnItem.Expression; Pipe.IDTSCustomProperty100 propFex = col.CustomPropertyCollection["FriendlyExpression"]; propFex.Value = derivedColumnItem.Expression; } return(this); }
/// <summary> /// Sets RowNumber default properties /// </summary> /// <param name="rowNumberColumn">RowNumber column which properties should be set</param> public static void SetRowNumberColumnProperties(IDTSOutputColumn rowNumberColumn) { rowNumberColumn.CustomPropertyCollection.RemoveAll(); rowNumberColumn.SetDataTypeProperties(DataType.DT_I4, 0, 0, 0, 0); //Initial Value IDTSCustomProperty initialValue = rowNumberColumn.CustomPropertyCollection.New(); initialValue.Name = Resources.RowNumberInitialValuePropertyName; initialValue.Description = Resources.RowNumberInitialValuePropertyDescription; initialValue.ContainsID = false; initialValue.EncryptionRequired = false; initialValue.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY; initialValue.Value = 1; //Increment By IDTSCustomProperty incrementBy = rowNumberColumn.CustomPropertyCollection.New(); incrementBy.Name = Resources.RowNumberIncrementByPropertyName; incrementBy.Description = Resources.RowNumberIncrementByPropertyDescription; incrementBy.ContainsID = false; incrementBy.EncryptionRequired = false; incrementBy.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY; incrementBy.Value = 1; }
public override void SetOutputColumnDataTypeProperties(int iOutputID, int iOutputColumnID, DataType eDataType, int iLength, int iPrecision, int iScale, int iCodePage) { //Check valid data types switch (eDataType) { case DataType.DT_DATE: case DataType.DT_DBDATE: case DataType.DT_I1: case DataType.DT_I2: case DataType.DT_I4: case DataType.DT_I8: case DataType.DT_NUMERIC: case DataType.DT_R4: case DataType.DT_R8: case DataType.DT_UI1: case DataType.DT_UI2: case DataType.DT_UI4: case DataType.DT_UI8: break; default: throw new Exception("Unsupported Data Type"); } IDTSOutputColumn col = ComponentMetaData.OutputCollection.GetObjectByID(iOutputID).OutputColumnCollection.GetObjectByID(iOutputColumnID); foreach (IDTSCustomProperty prop in col.CustomPropertyCollection) { switch (prop.Name) { case "IncrementBy": prop.Value = GetDefaultDataTypeIncrement(eDataType); col.SetDataTypeProperties(eDataType, iLength, iPrecision, iScale, iCodePage); break; case "InitialValue": prop.Value = GetDataTypeInitialValue(eDataType); col.SetDataTypeProperties(eDataType, iLength, iPrecision, iScale, iCodePage); break; } } }
public Union InsertOutputColumn(OutputColumn outputColumn, List <UnionInputColumn> unionInputColumns) { Pipe.IDTSOutputColumn100 newSSISOutputColumn = Component.OutputCollection[0].OutputColumnCollection.New(); newSSISOutputColumn.Name = outputColumn.Name; newSSISOutputColumn.SetDataTypeProperties((SSIS.Wrapper.DataType)outputColumn.DataType, outputColumn.Length, outputColumn.Precision, outputColumn.Scale, outputColumn.CodePage); foreach (UnionInputColumn unionInputColumn in unionInputColumns) { base.AddReadOnlyInputColumnForInput(unionInputColumn.ColumnName, unionInputColumn.InputName); Pipe.IDTSInputColumn100 inputColumn = base.FindSSISInputColumn(unionInputColumn.ColumnName, unionInputColumn.InputName); inputColumn.CustomPropertyCollection[0].Value = newSSISOutputColumn.LineageID; } return(this); }
/// <summary> /// Sets DataType of the Output Clumn according the HashType /// </summary> /// <param name="hashType">Hash Type of the output Column</param> /// <param name="outputColumn">Ourtput Column to set data type</param> public static void SetHashColumnDataType(HashColumnsTransformation.HashType hashType, IDTSOutputColumn outputColumn, int locale) { int dataLength; DataType dt = outputColumn.DataType; if (dt != DataType.DT_BYTES && dt != DataType.DT_WSTR && dt != DataType.DT_WSTR) { dt = DataType.DT_BYTES; } GetHashTypeDataType(hashType, dt, out dataLength); outputColumn.SetDataTypeProperties(dt, dataLength, 0, 0, outputColumn.CodePage); }
/// <summary> /// Allow the user to change a String to a NText, although this may affect performance. /// </summary> /// <param name="outputID"></param> /// <param name="outputColumnID"></param> /// <param name="dataType"></param> /// <param name="length"></param> /// <param name="precision"></param> /// <param name="scale"></param> /// <param name="codePage"></param> public override void SetOutputColumnDataTypeProperties(int outputID, int outputColumnID, Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType dataType, int length, int precision, int scale, int codePage) { var outputColl = this.ComponentMetaData.OutputCollection; IDTSOutput output = outputColl.GetObjectByID(outputID); var columnColl = output.OutputColumnCollection; IDTSOutputColumn column = columnColl.GetObjectByID(outputColumnID); var sourceColl = output.ExternalMetadataColumnCollection; IDTSExternalMetadataColumn columnSource = sourceColl.GetObjectByID(column.ExternalMetadataColumnID); if (((column.DataType == DataType.DT_WSTR) || (column.DataType == DataType.DT_NTEXT)) && ((dataType == DataType.DT_NTEXT) || (dataType == DataType.DT_WSTR))) { column.SetDataTypeProperties(dataType, length, precision, scale, codePage); } else { base.SetOutputColumnDataTypeProperties(outputID, outputColumnID, dataType, length, precision, scale, codePage); } }
/// <summary> /// Sets XmlColumn properties for Output Column /// </summary> /// <param name="xmlColumn">Output Column to set XmlColumn Properties</param> internal static void SetXmlColumnProperties(IDTSOutputColumn xmlColumn, SetPropertyType propType, bool setDataType) { List <int> IdsToRemove = new List <int>(); foreach (IDTSCustomProperty prop in xmlColumn.CustomPropertyCollection) { if ( (prop.Name == Resources.XmlSaveOptionPropertyName && (propType & SetPropertyType.XmlSaveOptions) == SetPropertyType.XmlSaveOptions) || (prop.Name == Resources.XmlSerializeLineageName && (propType & SetPropertyType.XmlSerializeLineage) == SetPropertyType.XmlSerializeLineage) || (prop.Name == Resources.XmlSerializeDataTypeName && (propType & SetPropertyType.XmlSerializeDataType) == SetPropertyType.XmlSerializeDataType) || (prop.Name == Resources.XmlInputColumnsPropertyname && (propType & SetPropertyType.XmlInputColumns) == SetPropertyType.XmlInputColumns) || (prop.Name == Resources.XmlSourceIdPropertyName && (propType & SetPropertyType.XMlSourceID) == SetPropertyType.XMlSourceID) || (prop.Name == Resources.XmlSourceNamePropertyName && (propType & SetPropertyType.XmlSourceName) == SetPropertyType.XmlSourceName) ) { IdsToRemove.Add(prop.ID); } } foreach (int id in IdsToRemove) { xmlColumn.CustomPropertyCollection.RemoveObjectByID(id); } //Add Save Options if ((propType & SetPropertyType.XmlSaveOptions) == SetPropertyType.XmlSaveOptions) { IDTSCustomProperty xmlSaveOption = xmlColumn.CustomPropertyCollection.New(); xmlSaveOption.Description = Resources.XmlSaveOptionsDescriptions; xmlSaveOption.Name = Resources.XmlSaveOptionPropertyName; xmlSaveOption.ContainsID = false; xmlSaveOption.EncryptionRequired = false; xmlSaveOption.ExpressionType = DTSCustomPropertyExpressionType.CPET_NONE; xmlSaveOption.TypeConverter = typeof(SaveOptions).AssemblyQualifiedName; xmlSaveOption.Value = SaveOptions.None; } //Lineage & Colun IDs information if ((propType & SetPropertyType.XmlSerializeLineage) == SetPropertyType.XmlSerializeLineage) { IDTSCustomProperty lineage = xmlColumn.CustomPropertyCollection.New(); lineage.Description = Resources.XmlSerializeLineageDescription; lineage.Name = Resources.XmlSerializeLineageName; lineage.ContainsID = false; lineage.EncryptionRequired = false; lineage.ExpressionType = DTSCustomPropertyExpressionType.CPET_NONE; lineage.TypeConverter = typeof(bool).AssemblyQualifiedName; lineage.Value = false; } //Data Type Information if ((propType & SetPropertyType.XmlSerializeDataType) == SetPropertyType.XmlSerializeDataType) { IDTSCustomProperty storeDataType = xmlColumn.CustomPropertyCollection.New(); storeDataType.Description = Resources.XmlStoreDataTypeDescription; storeDataType.Name = Resources.XmlSerializeDataTypeName; storeDataType.ContainsID = false; storeDataType.EncryptionRequired = false; storeDataType.ExpressionType = DTSCustomPropertyExpressionType.CPET_NONE; storeDataType.TypeConverter = typeof(bool).AssemblyQualifiedName; storeDataType.Value = false; } if ((propType & SetPropertyType.XmlInputColumns) == SetPropertyType.XmlInputColumns) { IDTSCustomProperty storeDataType = xmlColumn.CustomPropertyCollection.New(); storeDataType.Name = Resources.XmlInputColumnsPropertyname; storeDataType.Description = "Specifies the columns to be used to build Xml"; storeDataType.ContainsID = true; storeDataType.EncryptionRequired = false; storeDataType.ExpressionType = DTSCustomPropertyExpressionType.CPET_NONE; storeDataType.Value = string.Empty; } if ((propType & SetPropertyType.XMlSourceID) == SetPropertyType.XMlSourceID) { IDTSCustomProperty storeDataType = xmlColumn.CustomPropertyCollection.New(); storeDataType.Name = Resources.XmlSourceIdPropertyName; storeDataType.Description = "Specifies optional descriptive ID of the culumns source"; storeDataType.ContainsID = false; storeDataType.EncryptionRequired = false; storeDataType.ExpressionType = DTSCustomPropertyExpressionType.CPET_NONE; storeDataType.Value = string.Empty; } if ((propType & SetPropertyType.XmlSourceName) == SetPropertyType.XmlSourceName) { IDTSCustomProperty storeDataType = xmlColumn.CustomPropertyCollection.New(); storeDataType.Name = Resources.XmlSourceNamePropertyName; storeDataType.Description = "Specifies optional descriptive Name of the columns source"; storeDataType.ContainsID = false; storeDataType.EncryptionRequired = false; storeDataType.ExpressionType = DTSCustomPropertyExpressionType.CPET_NONE; storeDataType.Value = string.Empty; } //Column Data Type if (setDataType) { xmlColumn.SetDataTypeProperties(DataType.DT_NTEXT, 0, 0, 0, 0); } }
/// <summary> /// Connects to SharePoint and gets any columns on the target /// </summary> /// <param name="output"></param> /// <param name="p"></param> private void CreateExternalMetaDataColumns( IDTSOutput output, string sharepointUrl, string listName, string viewName, Dictionary <string, string> existingColumnData) { // No need to load if the Url is bad. if ((sharepointUrl == null) || (sharepointUrl.Length == 0)) { return; } // Need a list to continue if ((listName == null) || (listName.Length == 0)) { return; } // If the list has changed, then we do not want any of the exisiting column data to // influence it (provides a way to actually reset the names if needed) if (output.Description != listName) { existingColumnData.Clear(); output.Description = listName; } try { List <SharePointUtility.DataObject.ColumnData> accessibleColumns = GetAccessibleSharePointColumns(sharepointUrl, listName, viewName); foreach (var column in accessibleColumns) { // Setup the primary column details from the List IDTSExternalMetadataColumn dtsColumnMeta = output.ExternalMetadataColumnCollection.New(); if (existingColumnData.ContainsKey(column.Name)) { dtsColumnMeta.Name = existingColumnData[column.Name]; } else { dtsColumnMeta.Name = column.FriendlyName; } dtsColumnMeta.Description = column.DisplayName; dtsColumnMeta.Length = 0; dtsColumnMeta.Precision = 0; dtsColumnMeta.Scale = 0; if ("Boolean|AllDayEvent|Attachments|CrossProjectLink|Recurrence".Contains(column.SharePointType)) { dtsColumnMeta.DataType = DataType.DT_BOOL; } else if (column.SharePointType == "DateTime") { dtsColumnMeta.DataType = DataType.DT_DBTIMESTAMP; } else if ("Number|Currency".Contains(column.SharePointType)) { // Max = 100,000,000,000.00000 dtsColumnMeta.DataType = DataType.DT_R8; } else if ("Counter|Integer".Contains(column.SharePointType)) { dtsColumnMeta.DataType = DataType.DT_I4; } else if ("Guid".Contains(column.SharePointType)) { dtsColumnMeta.DataType = DataType.DT_GUID; } else { if (column.MaxLength == -1) { dtsColumnMeta.DataType = DataType.DT_NTEXT; dtsColumnMeta.Length = 0; } else { dtsColumnMeta.DataType = DataType.DT_WSTR; dtsColumnMeta.Length = column.MaxLength; } } IDTSCustomProperty fieldNameMeta = dtsColumnMeta.CustomPropertyCollection.New(); fieldNameMeta.Name = "Id"; fieldNameMeta.Description = "SharePoint ID"; fieldNameMeta.Value = column.Name; // Create default output columns for all of the fields returned and link to the original columns IDTSOutputColumn dtsColumn = output.OutputColumnCollection.New(); dtsColumn.Name = dtsColumnMeta.Name; dtsColumn.Description = dtsColumnMeta.Description; dtsColumn.ExternalMetadataColumnID = dtsColumnMeta.ID; IDTSCustomProperty fieldName = dtsColumn.CustomPropertyCollection.New(); fieldName.Name = fieldNameMeta.Name; fieldName.Description = fieldNameMeta.Description; fieldName.Value = fieldNameMeta.Value; dtsColumn.SetDataTypeProperties( dtsColumnMeta.DataType, dtsColumnMeta.Length, dtsColumnMeta.Precision, dtsColumnMeta.Scale, 0); } } catch (ApplicationException) { // Exception happened, so clear the columns, which will invalidate this object. output.ExternalMetadataColumnCollection.RemoveAll(); } }