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();
}
}
