/// <summary>
/// Gets List of OutputColumns of Particular OuputID encapsulated in the FormOutputColumn class
/// </summary>
/// <param name="outputID">ID of the Output to get OutputColumns</param>
/// <param name="creator">OutputColumnCreator to be used for creation of the FormInputColumn instances</param>
/// <param name="changedHandler">PropertyChangedEventHanled to be associated with the newly created FormInputColumn objects</param>
/// <returns>List of FomrInputColumn</returns>
public virtual List <FormOutputColumn> GetFormOutputColumns(int outputID, OutputColumnCreator creator, PropertyChangedEventHandler changedHandler)
{
List <FormOutputColumn> outputCols = new List <FormOutputColumn>();
IDTSOutput output = ComponentMetadata.OutputCollection.GetObjectByID(outputID);
if (output != null && creator != null)
{
for (int i = 0; i < output.OutputColumnCollection.Count; i++)
{
IDTSOutputColumn col = output.OutputColumnCollection[i];
FormOutputColumn oCol = creator(col);
oCol.UIHelper = this;
oCol.OutputID = outputID;
oCol.Index = i;
if (changedHandler != null)
{
oCol.PropertyChanged += changedHandler;
}
outputCols.Add(oCol);
}
}
return(outputCols);
}
public static int GetColumnSizeInBytes(IDTSOutputColumnXX col)
{
//this is based on a quick interpretation of http://msdn2.microsoft.com/en-us/library/ms141036.aspx and is not guaranteed to be exactly accurate
switch (col.DataType)
{
case DataType.DT_BOOL:
case DataType.DT_I1:
case DataType.DT_UI1:
return(1);
case DataType.DT_GUID:
case DataType.DT_I2:
case DataType.DT_UI2:
return(2);
case DataType.DT_I4:
case DataType.DT_UI4:
case DataType.DT_R4:
return(4);
case DataType.DT_CY:
case DataType.DT_DATE:
case DataType.DT_DBDATE:
case DataType.DT_DBTIME:
case DataType.DT_DBTIMESTAMP:
case DataType.DT_FILETIME:
case DataType.DT_I8:
case DataType.DT_UI8:
case DataType.DT_R8:
return(8);
case DataType.DT_DECIMAL:
return(12);
case DataType.DT_NUMERIC:
return(16);
case DataType.DT_BYTES:
case DataType.DT_STR:
return(col.Length + 1); //null terminated
case DataType.DT_WSTR:
return(col.Length * 2 + 1); //null terminated
case DataType.DT_IMAGE:
case DataType.DT_TEXT:
case DataType.DT_NTEXT:
return(24); //don't know length based on metadata so we guess... SSIS buffer tuning may use the same guess???
case DataType.DT_DBTIME2:
case DataType.DT_DBTIMESTAMP2:
return(8);
case DataType.DT_DBTIMESTAMPOFFSET:
return(10);
default:
return(4);
}
}
/// <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 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>
/// Inserts new Output columns as specified position in specified output
/// </summary>
/// <param name="outputID">ID of the output to insert new output column</param>
/// <param name="outputColumnIndex">Index of the inserted output column</param>
/// <param name="name">Name of the inserted output column</param>
/// <param name="description">Description of the inserted column</param>
/// <returns>Inserted output column</returns>
public override IDTSOutputColumn InsertOutputColumnAt(int outputID, int outputColumnIndex, string name, string description)
{
//Insert new column into specified output
IDTSOutputColumn rowNumColumn = base.InsertOutputColumnAt(outputID, outputColumnIndex, name, description);
SetRowNumberColumnProperties(rowNumColumn);
return(rowNumColumn);
}
/// <summary>
/// Creates a new instance of the IDTSOutputColumn enccapsualted in the MetadataSolumn
/// </summary>
/// <param name="output">Output where the new OutputColumn should be created</param>
/// <returns></returns>
public static MetadataColumn CreateNewInstance(IDTSOutput output)
{
IDTSOutputColumn col = output.OutputColumnCollection.New();
col.Name = Resources.RegExExpressionDefaultOutputColumnName + col.ID.ToString();
RegExExtractionTransformation.SetOutputColumnProperties(col, RegExExtractionTransformation.SetPropertyType.All, null, null);
MetadataColumn c = new MetadataColumn(col);
return(c);
}
public static FormOutputColumn CreateNewInstance(IDTSOutput output)
{
IDTSOutputColumn col = output.OutputColumnCollection.New();
col.Name = Resources.XmlColumnDefaultName + col.ID;
col.Description = Resources.XmlColumnDefaultDesccription;
ColumnsToXmlTransformation.SetXmlColumnProperties(col);
LookupErrorColumn c = new LookupErrorColumn(col);
return(c);
}
/// <summary>
/// Creates a new instance of the IDTSOutputColumn enccapsualted in the MetadataSolumn
/// </summary>
/// <param name="output">Output where the new OutputColumn should be created</param>
/// <returns></returns>
public static RowNumberColumn CreateNewInstance(IDTSOutput output)
{
IDTSOutputColumn col = output.OutputColumnCollection.New();
col.Name = Resources.RowNumberDefaultColumnName + col.ID.ToString();
RowNumberTransformation.SetRowNumberColumnProperties(col);
RowNumberColumn c = new RowNumberColumn(col);
return(c);
}
/// <summary>
/// Create a new instance of the HashOutputColumn
/// </summary>
/// <param name="output">output in which the instance should be created</param>
/// <returns></returns>
public static HashOutputColumn CreateNewInstance(IDTSOutput output)
{
IDTSOutputColumn col = output.OutputColumnCollection.New();
col.Name = Resources.HashColumnDefaultName + col.ID;
col.Description = Resources.HashColumnDefaultDesccription;
HashColumnsTransformationHelper.SetHashColumnProperties(HashColumnsTransformation.HashOuputColumnProperties.All, col);
HashOutputColumn c = new HashOutputColumn(col);
return(c);
}
/// <summary>
/// Inserts new Output columns as specified position in specified output
/// </summary>
/// <param name="outputID">ID of the output to insert new output column</param>
/// <param name="outputColumnIndex">Index of the inserted output column</param>
/// <param name="name">Name of the inserted output column</param>
/// <param name="description">Description of the inserted column</param>
/// <returns>Inserted output column</returns>
public override IDTSOutputColumn InsertOutputColumnAt(int outputID, int outputColumnIndex, string name, string description)
{
//Insert new column into specified output
IDTSOutput output = ComponentMetaData.OutputCollection.FindObjectByID(outputID);
IDTSOutputColumn xmlColumn = output.OutputColumnCollection.NewAt(outputColumnIndex);
xmlColumn.Name = name;
xmlColumn.Description = description;
SetXmlColumnProperties(xmlColumn);
return(xmlColumn);
}
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);
}
public Lookup AddLookupColumn(LookupColumn lookupItem)
{
Refresh();
if (!lookupItem.IsReplacingOutput)
{
Pipe.IDTSOutputColumn100 newColumn = ComponentWrapper.InsertOutputColumnAt(SSISDefaultOutput.ID, 0, lookupItem.OutputAlias, "");
ComponentWrapper.SetOutputColumnProperty(SSISDefaultOutput.ID, newColumn.ID, "CopyFromReferenceColumn", lookupItem.Name);
}
else
{
Pipe.IDTSVirtualInputColumn100 virtualColumn = base.FindVirtualInputColumn(lookupItem.ReplaceColumnName);
Pipe.IDTSInputColumn100 inputColumn = ComponentWrapper.SetUsageType(SSISDefaultInput.ID, base.DefaultVirtualInput, virtualColumn.LineageID, Pipe.DTSUsageType.UT_READWRITE);
ComponentWrapper.SetInputColumnProperty(SSISDefaultInput.ID, inputColumn.ID, "CopyFromReferenceColumn", lookupItem.Name);
}
return(this);
}
/// <summary>
/// Sets value of the custom column property
/// </summary>
/// <param name="outputID">Output ID</param>
/// <param name="outputColumnID">Output Column ID</param>
/// <param name="propertyName">Name of the custom Property</param>
/// <param name="propertyValue">Property value to be set</param>
/// <returns>Custom property</returns>
public override IDTSCustomProperty SetOutputColumnProperty(int outputID, int outputColumnID, string propertyName, object propertyValue)
{
//in case of HasshType property update, update output column data type
if (propertyName == Resources.XmlSaveOptionPropertyName)
{
IDTSOutput output = ComponentMetaData.OutputCollection[0];
IDTSOutputColumn outputColumn = output.OutputColumnCollection.FindObjectByID(outputColumnID);
//outputColumn.SetDataTypeProperties(DataType.DT_NTEXT, 0, 0, 0, 0);
return(base.SetOutputColumnProperty(outputID, outputColumnID, propertyName, propertyValue));
}
else if (propertyName == Resources.XmlSerializeLineageName || propertyName == Resources.XmlSerializeDataTypeName || propertyName == "DataType" || propertyName == "DataLength")
{
return(base.SetOutputColumnProperty(outputID, outputColumnID, propertyName, propertyValue));
}
throw new Exception(string.Format(Resources.ErrorOutputColumnPropertyCannotBeChanged, propertyName));
}
public static FormOutputColumn CreateInstance(IDTSOutputColumn col)
{
if (col.Name == "LookupDetailsXml")
{
return(new LookupErrorColumn(col));
}
else if (col.Name == Resources.LookupErrorAggLookupSourceIDName)
{
return(new LookupSourceIDColumn(col));
}
else if (col.Name == Resources.LookupErrorAggLookupSourceDescriptionName)
{
return(new LookupSourceDescriptionColumn(col));
}
else
{
return(new FormOutputColumn(col));
}
}
/// <summary>
/// Sets value of the custom column property
/// </summary>
/// <param name="outputID">Output ID</param>
/// <param name="outputColumnID">Output Column ID</param>
/// <param name="propertyName">Name of the custom Property</param>
/// <param name="propertyValue">Property value to be set</param>
/// <returns>Custom property</returns>
public override IDTSCustomProperty SetOutputColumnProperty(int outputID, int outputColumnID, string propertyName, object propertyValue)
{
IDTSOutputColumn col = ComponentMetaData.OutputCollection.GetObjectByID(outputID).OutputColumnCollection.GetObjectByID(outputColumnID);
if (propertyName == Resources.RowNumberIncrementByPropertyName)
{
if (col.DataType == DataType.DT_DBDATE || col.DataType == DataType.DT_DATE)
{
string pv = propertyValue.ToString().ToLower();
if (string.IsNullOrEmpty(pv))
{
propertyValue = "1d";
}
else
{
int ival;
char ch = pv[pv.Length - 1];
string val = pv.Substring(0, pv.Length - 1);
if (int.TryParse(pv, out ival))
{
propertyValue = ival.ToString() + "d";
}
else if ((ch == 'd' || ch == 'w' || ch == 'm' || ch == 'y') && int.TryParse(val, out ival))
{
propertyValue = pv;
}
else
{
throw new System.InvalidCastException(Resources.ErrorUnsupportedRowNumberIncrementValue);
}
}
}
return(base.SetOutputColumnProperty(outputID, outputColumnID, propertyName, propertyValue));
}
if (propertyName == Resources.RowNumberInitialValuePropertyName)
{
return(base.SetOutputColumnProperty(outputID, outputColumnID, propertyName, propertyValue));
}
throw new Exception(string.Format(Resources.ErrorOutputColumnPropertyCannotBeChanged, propertyName));
}
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;
}
}
}
private void form_AddOutputColumn(object sender, AddOutputColumnNameArgs args)
{
Debug.Assert(args.OutputColumnDetail.OutputColumn != null, "Invalid arguments passed from the UI");
this.ClearErrors();
try
{
// Grab the output collection, and the number of output columns
IDTSOutput output = this.ComponentMetadata.OutputCollection[0];
int locationID = output.OutputColumnCollection.Count;
// Create a new output column at the end of the set of output columns.
IDTSOutputColumn outputColumn = this.DesigntimeComponent.InsertOutputColumnAt(output.ID, locationID, args.OutputColumnDetail.OutputColumn.Name, string.Empty);
if (outputColumn == null)
{
throw new ApplicationException(Properties.Resources.UIisInconsistentState);
}
// assign the output column details into the args.
args.OutputColumnDetail.OutputColumn = new DataFlowElement(outputColumn.Name.ToString(), outputColumn);
int j = 0;
// assign the array to hold all the input columns
args.OutputColumnDetail.InputColumns = new InputColumnElement[this.ComponentMetadata.InputCollection[0].InputColumnCollection.Count];
// fill the array with unselected input columns.
foreach (IDTSInputColumn inputColumn in this.ComponentMetadata.InputCollection[0].InputColumnCollection)
{
args.OutputColumnDetail.InputColumns[j] = new InputColumnElement();
args.OutputColumnDetail.InputColumns[j].InputColumn = new DataFlowElement(inputColumn.Name, inputColumn);
args.OutputColumnDetail.InputColumns[j].LineageID = inputColumn.LineageID;
args.OutputColumnDetail.InputColumns[j].Selected = false;
args.OutputColumnDetail.InputColumns[j].SortPosition = 999999;
j++;
}
}
catch (Exception ex)
{
this.ReportErrors(ex);
args.CancelAction = true;
}
}
/// <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>
/// Validate HashColumnsTransform metadata
/// </summary>
/// <returns></returns>
public override DTSValidationStatus Validate()
{
if (ComponentMetaData.InputCollection.Count != 1)
{
FireComponentMetadataError(0, Resources.ErrorIncorrectNumberOfInputs);
return(DTSValidationStatus.VS_NEEDSNEWMETADATA);
}
if (ComponentMetaData.OutputCollection.Count != 1)
{
FireComponentMetadataError(0, Resources.ErrorIncorrectNumberOfOutputs);
return(DTSValidationStatus.VS_NEEDSNEWMETADATA);
}
IDTSInput input = ComponentMetaData.InputCollection[0];
IDTSVirtualInput vInput = input.GetVirtualInput();
foreach (IDTSInputColumn column in input.InputColumnCollection)
{
try
{
IDTSVirtualInputColumn100 vColumn = vInput.VirtualInputColumnCollection.GetVirtualInputColumnByLineageID(column.LineageID);
}
catch
{
FireComponentMetadataError(0, string.Format(Resources.ErrorInputColumnNotInUpstreamComponent, column.IdentificationString));
inputColumnsValid = false;
return(DTSValidationStatus.VS_NEEDSNEWMETADATA);
}
}
IDTSOutput output = ComponentMetaData.OutputCollection[0];
List <IDTSOutputColumn> redundantColumns = new List <IDTSOutputColumn>(output.OutputColumnCollection.Count);
bool xmlColumnExists = false;
List <int> missingLineages = new List <int>();
List <int> missingInputLineages = new List <int>();
bool missingError = false;
foreach (IDTSOutputColumn outputColumn in output.OutputColumnCollection)
{
bool isRendundant = true;
bool isInputColumn = false;
foreach (IDTSInputColumn inputColumn in input.InputColumnCollection)
{
if (inputColumn.Name == outputColumn.Name)
{
isRendundant = false;
isInputColumn = true;
break;
}
}
//Check if XML Column;
if (!isInputColumn)
{
foreach (IDTSCustomProperty prop in outputColumn.CustomPropertyCollection)
{
if (prop.Name == Resources.XmlInputColumnsPropertyname)
{
isRendundant = false;
xmlColumnExists = true;
var lineages = InputColumns.ParseInputLineages(prop.Value.ToString());
foreach (IDTSInputColumn icol in input.InputColumnCollection)
{
int l = icol.LineageID;
lineages.Remove(l);
if (lineages.Count == 0)
{
break;
}
}
if (lineages.Count > 0)
{
foreach (int l in lineages)
{
if (!missingInputLineages.Contains(l))
{
missingInputLineages.Add(l);
}
}
foreach (IDTSVirtualInputColumn vcol in vInput.VirtualInputColumnCollection)
{
int l = vcol.LineageID;
lineages.Remove(l);
if (lineages.Count == 0)
{
break;
}
}
if (lineages.Count > 0)
{
foreach (int l in lineages)
{
if (!missingLineages.Contains(l))
{
missingLineages.Add(l);
}
}
}
}
}
if (prop.Name == Resources.XmlSaveOptionPropertyName)
{
isRendundant = false;
xmlColumnExists = true;
if (!Enum.IsDefined(typeof(SaveOptions), prop.Value))
{
FireComponentMetadataError(0, string.Format(Resources.ErrorInvalidSaveOption, outputColumn.Name));
return(DTSValidationStatus.VS_NEEDSNEWMETADATA);
}
else
{
if (outputColumn.DataType != DataType.DT_NTEXT && outputColumn.DataType != DataType.DT_WSTR)
{
FireComponentMetadataError(0, string.Format(Resources.ErrorInvalidDataType, outputColumn.Name));
return(DTSValidationStatus.VS_NEEDSNEWMETADATA);
}
}
}
}
}
//add redundand column to redundand list
if (isRendundant)
{
redundantColumns.Add(outputColumn);
}
}
if (missingLineages.Count > 0)
{
string ml = string.Join(", ", missingLineages.ConvertAll <string>(l => l.ToString()).ToArray());
FireComponentMetadataError(0, string.Format("Output columns are referencing input column lineages which do not exists: {0}", ml));
missingError = true;
}
if (missingInputLineages.Count > 0)
{
string ml = string.Join(", ", missingInputLineages.ConvertAll <string>(l => l.ToString()).ToArray());
FireComponentMetadataError(0, string.Format("Output columns are referencing input column lineages which are not selected as Input columns: {0}", ml));
missingError = true;
}
if (missingError)
{
return(DTSValidationStatus.VS_NEEDSNEWMETADATA);
}
//remove redundand output columns
foreach (IDTSOutputColumn col in redundantColumns)
{
output.OutputColumnCollection.RemoveObjectByID(col.ID);
}
//If XmlColumn does not exists, create a default one
if (!xmlColumnExists)
{
IDTSOutputColumn xmlCol = output.OutputColumnCollection.New();
xmlCol.Name = Resources.XmlColumnDefaultName;
xmlCol.Description = Resources.XmlColumnDefaultDesccription;
SetXmlColumnProperties(xmlCol);
}
return(DTSValidationStatus.VS_ISVALID);
}
public static int GetColumnSizeInBytes(IDTSOutputColumnXX col)
{
//this is based on a quick interpretation of http://msdn2.microsoft.com/en-us/library/ms141036.aspx and is not guaranteed to be exactly accurate
switch (col.DataType)
{
case DataType.DT_BOOL:
case DataType.DT_I1:
case DataType.DT_UI1:
return 1;
case DataType.DT_GUID:
case DataType.DT_I2:
case DataType.DT_UI2:
return 2;
case DataType.DT_I4:
case DataType.DT_UI4:
case DataType.DT_R4:
return 4;
case DataType.DT_CY:
case DataType.DT_DATE:
case DataType.DT_DBDATE:
case DataType.DT_DBTIME:
case DataType.DT_DBTIMESTAMP:
case DataType.DT_FILETIME:
case DataType.DT_I8:
case DataType.DT_UI8:
case DataType.DT_R8:
return 8;
case DataType.DT_DECIMAL:
return 12;
case DataType.DT_NUMERIC:
return 16;
case DataType.DT_BYTES:
case DataType.DT_STR:
return col.Length + 1; //null terminated
case DataType.DT_WSTR:
return col.Length * 2 + 1; //null terminated
case DataType.DT_IMAGE:
case DataType.DT_TEXT:
case DataType.DT_NTEXT:
return 24; //don't know length based on metadata so we guess... SSIS buffer tuning may use the same guess???
#if DENALI || KATMAI || SQL2014
case DataType.DT_DBTIME2:
case DataType.DT_DBTIMESTAMP2:
return 8;
case DataType.DT_DBTIMESTAMPOFFSET:
return 10;
#endif
default:
return 4;
}
}
public LookupSourceDescriptionColumn(IDTSOutputColumn col) : base(col)
{
}
public LookupSourceIDColumn(IDTSOutputColumn col) : base(col)
{
}
public LookupErrorColumn(IDTSOutputColumn col) : base(col)
{
}
/// <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);
}
public HashOutputColumn(IDTSOutputColumn col) : base(col)
{
}
/// <summary>
/// Creates instance of the HashOutputColumn base on existing output column
/// </summary>
/// <param name="col">Output columns representing the HashOutputColumn</param>
/// <returns></returns>
public static HashOutputColumn CreateInstance(IDTSOutputColumn col)
{
return(new HashOutputColumn(col));
}
/// <summary>
/// Creates new Instance of the MetadataColumn
/// </summary>
/// <param name="col">IDTSOutputColumn to be associated wi the MetadataColumn</param>
/// <returns>MetadataColumn</returns>
public static MetadataColumn CreateInstance(IDTSOutputColumn col)
{
return(new MetadataColumn(col));
}
public MetadataColumn(IDTSOutputColumn col) : base(col)
{
}
/// <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);
}
}
internal static void SetXmlColumnProperties(IDTSOutputColumn xmlColumn)
{
SetXmlColumnProperties(xmlColumn, SetPropertyType.All, true);
}
/// <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();
}
}