/// <summary>
/// Add a custom property to an output
/// For internal use from within this library. Not for use from outside libraries
/// </summary>
/// <param name="output"></param>
/// <param name="propertyName"></param>
/// <param name="propertyValue"></param>
internal IDTSCustomProperty100 SetCustomPropertyToOutput(IDTSOutput100 output, string propertyName, object propertyValue)
{
bool propertyExists = false;
IDTSCustomProperty100 prop = null;
for (int i = 0; i < output.CustomPropertyCollection.Count; i++)
{
if (output.CustomPropertyCollection[i].Name == propertyName)
{
propertyExists = true;
break;
}
}
if (propertyExists)
{
prop = DesignTimeComponent.SetOutputProperty(output.ID, propertyName, propertyValue);
}
else
{
prop = output.CustomPropertyCollection.New();
prop.Name = propertyName;
prop.Value = propertyValue;
}
return(prop);
}
/// <summary>
/// Sets a custom property to an output column; adds it if the property does not exist.
/// For internal use within this library. Not for use from outside libraries
/// </summary>
/// <param name="output"></param>
/// <param name="outputColumn"></param>
/// <param name="propertyName"></param>
/// <param name="propertyValue"></param>
/// <param name="containsId"></param>
/// <returns></returns>
internal IDTSCustomProperty100 SetCustomPropertyToOutputColumn(IDTSOutput100 output, IDTSOutputColumn100 outputColumn, string propertyName, object propertyValue, bool containsId = false)
{
bool propertyExists = false;
IDTSCustomProperty100 customProperty = null;
for (int i = 0; i < outputColumn.CustomPropertyCollection.Count; i++)
{
if (outputColumn.CustomPropertyCollection[i].Name == propertyName)
{
propertyExists = true;
break;
}
}
if (propertyExists)
{
customProperty = DesignTimeComponent.SetOutputColumnProperty(output.ID, outputColumn.ID, propertyName, propertyValue);
customProperty.ContainsID = containsId;
}
else
{
customProperty = outputColumn.CustomPropertyCollection.New();
customProperty.Name = propertyName;
customProperty.Value = propertyValue;
customProperty.ContainsID = containsId;
}
return(customProperty);
}
private void SetOutputProperties(Condition condition, string defaultOutputPath)
{
IDTSOutput100 newPath = _component.OutputCollection.New();
newPath.Name = condition.OutputPath;
newPath.Description = condition.OutputPath;
// newPath.ExclusionGroup = _component.OutputCollection["Conditional Split Default Output"].ExclusionGroup;
// newPath.SynchronousInputID = _component.OutputCollection["Conditional Split Default Output"].SynchronousInputID;
newPath.ExclusionGroup = _component.OutputCollection[defaultOutputPath].ExclusionGroup;
newPath.SynchronousInputID = _component.OutputCollection[defaultOutputPath].SynchronousInputID;
newPath.ErrorOrTruncationOperation = "Computation";
newPath.ErrorRowDisposition = DTSRowDisposition.RD_IgnoreFailure;
newPath.TruncationRowDisposition = DTSRowDisposition.RD_FailComponent;
IDTSCustomProperty100 propEvaluationOrder = newPath.CustomPropertyCollection.New();
propEvaluationOrder.Name = "EvaluationOrder";
propEvaluationOrder.Value = _iEvaluationOrder;
IDTSCustomProperty100 propFriendlyExpression = newPath.CustomPropertyCollection.New();
propFriendlyExpression.Name = "FriendlyExpression";
propFriendlyExpression.Value = condition.Expression;
IDTSCustomProperty100 propExpression = newPath.CustomPropertyCollection.New();
propExpression.Name = "Expression";
IDTSVirtualInput100 vi = _component.InputCollection[0].GetVirtualInput();
propExpression.Value = ExpressionCleanerAndInputMapBuilder(condition.Expression, vi, DTSUsageType.UT_READONLY);;
_component.OutputCollection.SetIndex(_component.OutputCollection.GetObjectIndexByID(newPath.ID), 0);
_iEvaluationOrder++;
}
public void TestChangeRowsProcessedOutputSetColumnDataType()
{
Microsoft.SqlServer.Dts.Runtime.Package package = new Microsoft.SqlServer.Dts.Runtime.Package();
Executable exec = package.Executables.Add("STOCK:PipelineTask");
Microsoft.SqlServer.Dts.Runtime.TaskHost thMainPipe = exec as Microsoft.SqlServer.Dts.Runtime.TaskHost;
MainPipe dataFlowTask = thMainPipe.InnerObject as MainPipe;
ComponentEventHandler events = new ComponentEventHandler();
dataFlowTask.Events = DtsConvert.GetExtendedInterface(events as IDTSComponentEvents);
IDTSComponentMetaData100 textFileSplitter = dataFlowTask.ComponentMetaDataCollection.New();
textFileSplitter.Name = "Row Splitter Test";
textFileSplitter.ComponentClassID = typeof(Martin.SQLServer.Dts.TextFileSplitter).AssemblyQualifiedName;
CManagedComponentWrapper instance = textFileSplitter.Instantiate();
instance.ProvideComponentProperties();
IDTSOutput100 errorOutput = textFileSplitter.OutputCollection[3];
Boolean exceptionThrown = false;
try
{
instance.SetOutputColumnDataTypeProperties(errorOutput.ID, errorOutput.OutputColumnCollection[0].ID, DataType.DT_STR, 512, 0, 0, 1252);
}
catch (COMException ex)
{
Assert.AreEqual(MessageStrings.CantChangeOutputProperties("RowsProcessed"), ex.Message, "Exception Message Wrong");
exceptionThrown = true;
}
Assert.IsTrue(exceptionThrown, "Exception Not Thrown");
}
private IDTSPath100 ConnectToAnotherPipelineComponent(IDTSComponentMetaData100 sourceComponent, int sourceComponentOutputIndex = 0, int inputCollectionIndex = 0)
{
bool pathHasStartPoint = false;
IDTSPath100 path = null;
foreach (IDTSPath100 dtsPath in ParentDataFlowTask.MainPipe.PathCollection)
{
IDTSOutput100 output = dtsPath.StartPoint;
if (dtsPath.StartPoint.Name == sourceComponent.OutputCollection[sourceComponentOutputIndex].Name &&
dtsPath.StartPoint.Component.Name == sourceComponent.Name
)
{
pathHasStartPoint = true;
path = dtsPath;
}
}
if (pathHasStartPoint)
{
ParentDataFlowTask.MainPipe.PathCollection.RemoveObjectByID(path.ID);
}
path = ParentDataFlowTask.MainPipe.PathCollection.New();
path.AttachPathAndPropagateNotifications(
sourceComponent.OutputCollection[sourceComponentOutputIndex],
ComponentMetaData.InputCollection[inputCollectionIndex]);
return(path);
}
/// <summary>
/// Processes the only supported input.
/// </summary>
/// <param name="inputID"></param>
/// <param name="buffer"></param>
public override void ProcessInput(int inputID, PipelineBuffer buffer)
{
IDTSInput100 input = ComponentMetaData.InputCollection.GetObjectByID(inputID);
IDTSOutput100 output = ComponentMetaData.OutputCollection[OUTPUT_NAME];
IDTSOutputColumn100 rowHashColumn = output.OutputColumnCollection[ROW_HASH_COLUMN_NAME];
Int32 rowHashColumnIndex = BufferManager.FindColumnByLineageID(input.Buffer, rowHashColumn.LineageID);
// Processes each incoming pipeline row.
while (buffer.NextRow())
{
List <Byte[]> columnBytesCollection = new List <Byte[]>();
// Version 2: In the order of the input columns specified by the Ordinal custom property ...
foreach (IDTSInputColumn100 inputColumn in _columnCollection.Values)
{
Int32 inputColumnIndex = input.InputColumnCollection.GetObjectIndexByID(inputColumn.ID);
// ... retrieves the binary representation of the column value.
columnBytesCollection.Add(this.GetColumnValueBytes(buffer, inputColumn, inputColumnIndex));
}
// Writes the current row hash value to the built-in output column.
buffer.SetBytes(rowHashColumnIndex, this.ComputeHash(columnBytesCollection));
}
}
public static bool DoesOutputColumnMetaDataMatchExternalColumnMetaData(IDTSOutput100 output)
{
if (output == null)
{
throw new ArgumentNullException("output");
}
bool areAllOutputColumnsValid = true;
if (output.ExternalMetadataColumnCollection.Count == 0)
{
return(false);
}
foreach (IDTSOutputColumn100 column in output.OutputColumnCollection)
{
bool Cancel = false;
IDTSExternalMetadataColumn100 exColumn
= output.ExternalMetadataColumnCollection.GetObjectByID(
column.ExternalMetadataColumnID);
if (!DoesColumnMetaDataMatch(column, exColumn))
{
output.Component.FireError(
0,
output.Component.Name,
@"The output column " + column.IdentificationString + @" does not match the external metadata.",
@"",
0,
out Cancel);
areAllOutputColumnsValid = false;
}
}
return(areAllOutputColumnsValid);
}
public static void FixOutputColumnMetaData(IDTSOutput100 output)
{
if (output == null)
{
throw new ArgumentNullException("output");
}
if (output.ExternalMetadataColumnCollection.Count == 0)
{
return;
}
foreach (IDTSOutputColumn100 column in output.OutputColumnCollection)
{
IDTSExternalMetadataColumn100 exColumn
= output.ExternalMetadataColumnCollection.GetObjectByID(
column.ExternalMetadataColumnID);
if (!DoesColumnMetaDataMatch(column, exColumn))
{
column.SetDataTypeProperties(
exColumn.DataType,
exColumn.Length,
exColumn.Precision,
exColumn.Scale,
exColumn.CodePage);
}
}
}
public override void PreExecute()
{
IDTSInput100 input = ComponentMetaData.InputCollection[0];
this.inputColumnBufferIndex = BufferManager.FindColumnByLineageID(input.Buffer, input.InputColumnCollection[0].LineageID);
//Get output id info
int defaultOutputID = -1;
int errorOutputID = -1;
int errorOutputIndex = -1;
GetErrorOutputInfo(ref errorOutputID, ref errorOutputIndex);
if (errorOutputIndex == 0)
{
defaultOutputID = ComponentMetaData.OutputCollection[1].ID;
}
else
{
defaultOutputID = ComponentMetaData.OutputCollection[0].ID;
}
IDTSOutput100 output = ComponentMetaData.OutputCollection.GetObjectByID(defaultOutputID);
if (output.OutputColumnCollection.Count == 0)
{
this.outputColumnBufferIndex = -1;
}
else
{
this.outputColumnBufferIndex = BufferManager.FindColumnByLineageID(input.Buffer, output.OutputColumnCollection[0].LineageID);
}
}
//checks if output metadata matches external output metadata
public override bool isExternalMetadataValid()
{
//get default output
IDTSOutput100 output = null;
for (int i = 0; i < ComponentMetaData.OutputCollection.Count; i++)
{
if (!ComponentMetaData.OutputCollection[i].IsErrorOut)
{
output = ComponentMetaData.OutputCollection[i];
}
}
//get default output external metadata
IDTSExternalMetadataColumnCollection100 externalMetaData = output.ExternalMetadataColumnCollection;
//return false if output column metadata does not match external column metadata
foreach (IDTSOutputColumn100 col in output.OutputColumnCollection)
{
IDTSExternalMetadataColumn100 ecol = externalMetaData.GetObjectByID(col.ExternalMetadataColumnID);
if (col.DataType != ecol.DataType ||
col.Precision != ecol.Precision ||
col.Length != ecol.Length ||
col.Scale != ecol.Scale)
{
return(false);
}
}
return(true);
}
public override IDTSInputColumn100 SetUsageType(int inputID, IDTSVirtualInput100 virtualInput, int lineageID, DTSUsageType usageType)
{
IDTSInputColumn100 inputColumn = null;
IDTSOutput100 output = this.ComponentMetaData.OutputCollection[0];
IDTSOutputColumnCollection100 outputColumnCollection = output.OutputColumnCollection;
IDTSInput100 input = this.ComponentMetaData.InputCollection[0];
IDTSInputColumnCollection100 inputColumnCollection = input.InputColumnCollection;
if (usageType == DTSUsageType.UT_IGNORED)
{
inputColumn = inputColumnCollection.GetInputColumnByLineageID(lineageID);
IDTSOutputColumn100 outputColumn = outputColumnCollection.FindObjectByID(inputColumn.MappedColumnID);
if (outputColumn != null)
{
outputColumnCollection.RemoveObjectByID(outputColumn.ID);
}
inputColumn = base.SetUsageType(inputID, virtualInput, lineageID, usageType);
}
else
{
inputColumn = base.SetUsageType(inputID, virtualInput, lineageID, usageType);
IDTSOutputColumn100 outputColumn = outputColumnCollection.New();
outputColumn.Name = inputColumn.Name;
outputColumn.SetDataTypeProperties(inputColumn.DataType, inputColumn.Length, inputColumn.Precision, inputColumn.Scale, inputColumn.CodePage);
outputColumn.MappedColumnID = inputColumn.LineageID;
inputColumn.MappedColumnID = outputColumn.LineageID;
AddInputColumnProperty(inputColumn, GeoColumnPropName, "True if the column holds geometry objects to be joined together.", false);
AddInputColumnProperty(inputColumn, GroupByColumnPropName, "True if the column holds group by values.", false);
}
return(inputColumn);
}
public override void ProvideComponentProperties()
{
//Specify that the component has an error output
ComponentMetaData.UsesDispositions = true;
//Add the input
IDTSInput100 input = ComponentMetaData.InputCollection.New();
input.Name = "Input";
//Add the output
IDTSOutput100 output = ComponentMetaData.OutputCollection.New();
output.Name = "Output";
output.SynchronousInputID = input.ID;
output.ExclusionGroup = 1;
//Add the error output
IDTSOutput100 errorOutput = ComponentMetaData.OutputCollection.New();
errorOutput.IsErrorOut = true;
errorOutput.Name = "ErrorOutput";
errorOutput.SynchronousInputID = input.ID;
errorOutput.ExclusionGroup = 1;
}
public IDTSComponentMetaData100 AddComp_RowCount(string ComponentName,
string VarName,
IDTSOutput100 outCols
)
{
// Create
IDTSComponentMetaData100 Comp = dmp.ComponentMetaDataCollection.New();
Comp.ComponentClassID = "Microsoft.RowCount";
// Instantiate
CManagedComponentWrapper Inst = Comp.Instantiate();
Inst.ProvideComponentProperties();
Comp.Name = ComponentName;
Comp.Description = "zliczanie wierszy";
// Parametrize
Inst.SetComponentProperty("VariableName", VarName);
// Connect
IDTSPath100 pth = dmp.PathCollection.New();
pth.AttachPathAndPropagateNotifications(outCols, Comp.InputCollection[0]);
// Return
return(Comp);
}
public void AssociateWithOutputColumn(string outputColumnName)
{
ISOutputColumn outCol = new ISOutputColumn(ParentComponent, Output.Name, outputColumnName, RowDisposition.RD_FailComponent, RowDisposition.RD_FailComponent);
outCol.SetDataTypeProperties(DataType, Length, Precision, Scale, CodePage);
outCol.OutputColumn.ExternalMetadataColumnID = ID;
IDTSOutput100 errorOutput = null;
for (int o = 0; o < ParentComponent.ComponentMetaData.OutputCollection.Count; o++)
{
if (ParentComponent.ComponentMetaData.OutputCollection[o].IsErrorOut == true)
{
errorOutput = ParentComponent.ComponentMetaData.OutputCollection[o];
}
}
bool errorColExists = false;
int errorColCount = errorOutput.OutputColumnCollection.Count;
for (int e = 0; e < errorColCount; e++)
{
if (errorOutput.OutputColumnCollection[e].Name == outputColumnName)
{
errorColExists = true;
}
}
if (!(errorColExists))
{
IDTSOutputColumn100 o = errorOutput.OutputColumnCollection.NewAt(errorColCount - 2);
o.Name = outputColumnName;
}
}
/// <summary>
/// Constructs an output column and adds it to the provided output, using the column metadata from the provided BSON element.
/// </summary>
/// <param name="output"></param>
/// <param name="bsonElement"></param>
/// <returns></returns>
private IDTSOutputColumn100 BuildOutputColumn(IDTSOutput100 output, BsonElement bsonElement)
{
IDTSOutputColumn100 outColumn = output.OutputColumnCollection.New();
// Set the properties of the output column.
outColumn.Name = bsonElement.Name;
DataType dt = GetColumnDataType(bsonElement.Value);
int length = 0;
int codepage = 0;
if (dt == DataType.DT_STR)
{
length = 256;
codepage = 1252;
}
outColumn.SetDataTypeProperties(dt, length, 0, 0, codepage);
// Setting these values enables the end-user to configure the error behavior on a per-column basis
if (!output.IsErrorOut)
{
outColumn.ErrorRowDisposition = DTSRowDisposition.RD_FailComponent;
outColumn.TruncationRowDisposition = DTSRowDisposition.RD_FailComponent;
}
return(outColumn);
}
/// <summary>
/// Initalizes a new instance of the RowHashTransformation.
/// </summary>
public override void ProvideComponentProperties()
{
base.ProvideComponentProperties();
// Name the input.
IDTSInput100 input = ComponentMetaData.InputCollection[0];
input.Name = INPUT_NAME;
// Create one synchronized output, and marks it as a built-in output, which cannot be removed.
IDTSCustomProperty100 isInternal;
IDTSOutput100 output = ComponentMetaData.OutputCollection[0];
output.Name = OUTPUT_NAME;
output.SynchronousInputID = ComponentMetaData.InputCollection[INPUT_NAME].ID;
isInternal = output.CustomPropertyCollection.New();
isInternal.Name = IS_INTERNAL_CUSTOM_PROPERTY_NAME;
isInternal.State = DTSPersistState.PS_PERSISTASDEFAULT;
isInternal.TypeConverter = typeof(Boolean).AssemblyQualifiedName;
isInternal.Value = true;
// Create the output column to hold the hash value, and marks it as a built-in output column, which cannot be removed.
IDTSOutputColumn100 rowHashColumn = output.OutputColumnCollection.New();
rowHashColumn.Name = ROW_HASH_COLUMN_NAME;
// The size of a SHA1 hash value is 20 bytes.
rowHashColumn.SetDataTypeProperties(DataType.DT_BYTES, 20, 0, 0, 0);
isInternal = rowHashColumn.CustomPropertyCollection.New();
isInternal.Name = IS_INTERNAL_CUSTOM_PROPERTY_NAME;
isInternal.State = DTSPersistState.PS_PERSISTASDEFAULT;
isInternal.TypeConverter = typeof(Boolean).AssemblyQualifiedName;
isInternal.Value = true;
}
public void TestSetOutputColumnDefaults()
{
Microsoft.SqlServer.Dts.Runtime.Package package = new Microsoft.SqlServer.Dts.Runtime.Package();
Executable exec = package.Executables.Add("STOCK:PipelineTask");
Microsoft.SqlServer.Dts.Runtime.TaskHost thMainPipe = exec as Microsoft.SqlServer.Dts.Runtime.TaskHost;
MainPipe dataFlowTask = thMainPipe.InnerObject as MainPipe;
IDTSComponentMetaData100 textFileSplitter = dataFlowTask.ComponentMetaDataCollection.New();
ComponentEventHandler events = new ComponentEventHandler();
dataFlowTask.Events = DtsConvert.GetExtendedInterface(events as IDTSComponentEvents);
textFileSplitter.Name = "Row Splitter Test";
textFileSplitter.ComponentClassID = typeof(Martin.SQLServer.Dts.TextFileSplitter).AssemblyQualifiedName;
CManagedComponentWrapper instance = textFileSplitter.Instantiate();
instance.ProvideComponentProperties();
IDTSOutput100 output = textFileSplitter.OutputCollection.New();
IDTSOutputColumn100 actual = output.OutputColumnCollection.New();
ManageColumns.SetOutputColumnDefaults(actual, 1252);
Assert.AreEqual(DataType.DT_STR, actual.DataType, "DataType is wrong");
Assert.AreEqual(255, actual.Length, "Length is wrong");
Assert.AreEqual(DTSRowDisposition.RD_NotUsed, actual.ErrorRowDisposition, "Row Disposition is wrong");
Assert.AreEqual(DTSRowDisposition.RD_NotUsed, actual.TruncationRowDisposition, "Truncate Disposition is wrong");
}
/// <summary>
/// Add user and hidden properties to configure the control
/// </summary>
public override void ProvideComponentProperties()
{
base.RemoveAllInputsOutputsAndCustomProperties();
ComponentMetaData.RuntimeConnectionCollection.RemoveAll();
ComponentMetaData.Name = "Dynamics CRM Source Adapter";
ComponentMetaData.ContactInfo = "*****@*****.**";
ComponentMetaData.Description = "Allows to connect to Dynamics CRM Source";
IDTSOutput100 output = ComponentMetaData.OutputCollection.New();
output.Name = "Output";
IDTSCustomProperty100 FetchXML = ComponentMetaData.CustomPropertyCollection.New();
FetchXML.Description = "FetchXML query to get information from Dynamics";
FetchXML.Name = "FetchXML";
FetchXML.Value = String.Empty;
IDTSRuntimeConnection100 connection = ComponentMetaData.RuntimeConnectionCollection.New();
connection.Name = "CRMSSIS";
connection.ConnectionManagerID = "CRMSSIS";
}
/// <summary>
/// The add xml column.
/// </summary>
/// <param name="output">
/// The output.
/// </param>
private void AddXmlColumn(IDTSOutput100 output)
{
IDTSOutputColumn100 column = output.OutputColumnCollection.New();
column.Name = "Xml";
column.SetDataTypeProperties(DataType.DT_NTEXT, 0, 0, 0, 0);
}
//=================================================================================================
private bool AreOutputColumnsValid()
{
// Get the output and the WQL schema.
IDTSOutput100 output = ComponentMetaData.OutputCollection[0];
PropertyDataCollection wmiColumns = GetWmiColumns();
// Output columns should be subset of external columns
if (output.OutputColumnCollection.Count > wmiColumns.Count)
{
return(false);
}
// Otherwise, walk the WQL columns and validate each column
for (int x = 0; x < output.OutputColumnCollection.Count; x++)
{
IDTSOutputColumn100 column = output.OutputColumnCollection[x];
PropertyData wmiCol = GetWmiColumnByName(wmiColumns, column.Name);
// Does the column exist, by name, in the WQL schema?
if (wmiCol == null || !WmiColumnMatchesOutputColumn(wmiCol, column))
{
// Fire a warning before returning
ComponentMetaData.FireWarning(0, ComponentMetaData.Name, "The output column " + column.IdentificationString + " does not match the data source.", "", 0);
return(false);
}
}
// If this code is reached, all of the columns are valid.
return(true);
}
public override void ProvideComponentProperties()
{
// Set Component information
ComponentMetaData.Name = "Data Masker";
ComponentMetaData.Description = "A SSIS Data Flow Transformation Component to Provide Basic Data Masking";
ComponentMetaData.ContactInfo = "Arthur Zubarev";
// Reset the component
base.RemoveAllInputsOutputsAndCustomProperties();
// add input objects
IDTSInput100 input = ComponentMetaData.InputCollection.New();
input.Name = "Input";
input.Description = "Contains un-masked columns.";
// add output objects
IDTSOutput100 output = ComponentMetaData.OutputCollection.New();
output.Name = "Output";
output.Description = "Contains masked columns. gets set automatically.";
output.SynchronousInputID = input.ID; // Synchronus transformation
// Add error objects
IDTSOutput100 errorOutput = ComponentMetaData.OutputCollection.New();
errorOutput.Name = "Error";
errorOutput.IsErrorOut = true;
}
public override void ProvideComponentProperties()
{
base.ProvideComponentProperties();
base.RemoveAllInputsOutputsAndCustomProperties();
IDTSCustomProperty100 queuePath = ComponentMetaData.CustomPropertyCollection.New();
queuePath.Description = "The MSMQ path";
queuePath.Name = QueuePathName;
queuePath.Value = String.Empty;
queuePath.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
IDTSOutput100 output = ComponentMetaData.OutputCollection.New();
output.Name = "Output";
var bodyColumn = ComponentMetaData.OutputCollection[0].OutputColumnCollection.New();
bodyColumn.Name = BodyColumnName;
bodyColumn.SetDataTypeProperties(DataType.DT_NTEXT, 0, 0, 0, 0);
var labelColumn = ComponentMetaData.OutputCollection[0].OutputColumnCollection.New();
labelColumn.Name = LabelColumnName;
labelColumn.SetDataTypeProperties(DataType.DT_WSTR, 4000, 0, 0, 0);
var idColumn = ComponentMetaData.OutputCollection[0].OutputColumnCollection.New();
idColumn.Name = IdColumnName;
idColumn.SetDataTypeProperties(DataType.DT_WSTR, 100, 0, 0, 0);
}
public override void PrimeOutput(int outputs, int[] outputIDs, PipelineBuffer[] buffers)
{
//System.Diagnostics.Debugger.Launch();
base.PrimeOutput(outputs, outputIDs, buffers);
IDTSOutput100 output = ComponentMetaData.OutputCollection.FindObjectByID(outputIDs[0]);
PipelineBuffer buffer = buffers[0];
DataTable dt = new DataTable();
dt = GetData(ComponentMetaData.CustomPropertyCollection["FetchXML"].Value.ToString(), false);
foreach (DataRow row in dt.Rows)
{
buffer.AddRow();
for (int x = 0; x < mapOutputColsToBufferCols.Length; x++)
{
if (row.IsNull(x))
{
buffer.SetNull(mapOutputColsToBufferCols[x]);
}
else
{
buffer[mapOutputColsToBufferCols[x]] = row[x];
}
}
}
buffer.SetEndOfRowset();
}
public IDTSComponentMetaData100 AddComp_Multicast(string ComponentName,
IDTSOutput100 outCols
)
{
// Create
IDTSComponentMetaData100 Comp = dmp.ComponentMetaDataCollection.New();
Comp.ComponentClassID = "Microsoft.Multicast";
// Instantiate
CManagedComponentWrapper Inst = Comp.Instantiate();
Inst.ProvideComponentProperties();
Comp.Name = ComponentName;
Comp.Description = "Dodany Sort";
// Connect
IDTSPath100 pth = dmp.PathCollection.New();
pth.AttachPathAndPropagateNotifications(outCols, Comp.InputCollection[0]);
// Return
return(Comp);
}
public static void CreatePath(MainPipe dataFlowTask, IDTSOutput100 fromOutput, IDTSComponentMetaData100 toComponent, CManagedComponentWrapper toInstance)
{
// Create the path from source to destination.
IDTSPath100 path = dataFlowTask.PathCollection.New();
path.AttachPathAndPropagateNotifications(fromOutput, toComponent.InputCollection[0]);
// Get the destination's default input and virtual input.
IDTSInput100 input = toComponent.InputCollection[0];
IDTSVirtualInput100 vInput = input.GetVirtualInput();
// Iterate through the virtual input column collection.
foreach (IDTSVirtualInputColumn100 vColumn in vInput.VirtualInputColumnCollection)
{
// Find external column by name
IDTSExternalMetadataColumn100 externalColumn = null;
foreach (IDTSExternalMetadataColumn100 column in input.ExternalMetadataColumnCollection)
{
if (String.Compare(column.Name, vColumn.Name, true) == 0)
{
externalColumn = column;
break;
}
}
if (externalColumn != null)
{
// Select column, and retain new input column
IDTSInputColumn100 inputColumn = toInstance.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
// Map input column to external column
toInstance.MapInputColumn(input.ID, inputColumn.ID, externalColumn.ID);
}
}
}
public override void PreExecute()
{
//get default output
IDTSOutput100 defaultOutput = null;
this.GetErrorOutputInfo(ref errorOutputID, ref errorOutputIndex);
foreach (IDTSOutput100 output in ComponentMetaData.OutputCollection)
{
if (output.ID != errorOutputID)
{
defaultOutput = output;
}
}
//for each output column add columnInfo object to columnInformation arrayList
foreach (IDTSOutputColumn100 col in defaultOutput.OutputColumnCollection)
{
columnInfo ci = new columnInfo();
ci.bufferColumnIndex = BufferManager.FindColumnByLineageID(defaultOutput.Buffer, col.LineageID);
ci.lineageID = col.LineageID;
ci.columnName = col.Name;
ci.errorDisposition = col.ErrorRowDisposition;
ci.truncationDisposition = col.TruncationRowDisposition;
if (col.Name == (string)ComponentMetaData.CustomPropertyCollection["Geometry Column"].Value)
{
ci.geom = true;
}
else
{
ci.geom = false;
}
this.columnInformation.Add(ci);
}
}
public IDTSComponentMetaData100 AddComp_DerivedCol(string ComponentName,
IDTSOutput100 outCols
)
{
// Create
IDTSComponentMetaData100 Comp = dmp.ComponentMetaDataCollection.New();
Comp.ComponentClassID = "Microsoft.DerivedColumn";
// Instantiate
CManagedComponentWrapper Inst = Comp.Instantiate();
Inst.ProvideComponentProperties();
Comp.Name = ComponentName;
Comp.Description = "Derajw kolumn";
// Parametrize
Comp.OutputCollection[0].TruncationRowDisposition = DTSRowDisposition.RD_NotUsed;
Comp.OutputCollection[0].ErrorRowDisposition = DTSRowDisposition.RD_NotUsed;
// Connect
IDTSPath100 pth = dmp.PathCollection.New();
pth.AttachPathAndPropagateNotifications(outCols, Comp.InputCollection[0]);
// Return
return(Comp);
}
// In the PreExecute phase we will be caching the column indexes looked up by the LineageID
public override void PreExecute()
{
// Initialize the cache
columnIndexes = new Dictionary <string, Dictionary <string, int> >();
// Get the model
JSONDataModel model = getModel();
// For each table definition
foreach (DataTable table in model.Tables)
{
// Find the ouput
IDTSOutput100 output = ComponentMetaData.OutputCollection[table.TableName];
// ... and initialize the corresponding cache index
columnIndexes[table.TableName] = new Dictionary <string, int>();
// For every column
foreach (IDTSOutputColumn100 col in output.OutputColumnCollection)
{
// We wrap this in a try-catch without exception handling so that
// we allow for a subset of the outputs to be used. If an output is not
// connected, the columns defined in that output will not be in the Buffer
try
{
// Cache the buffer column index
columnIndexes[table.TableName][col.Name] = BufferManager.FindColumnByLineageID(output.Buffer, col.LineageID);
}
catch
{
// Do nothing for now
}
}
}
}
public void TestAddNumberOfRowsOutputColumns()
{
Microsoft.SqlServer.Dts.Runtime.Package package = new Microsoft.SqlServer.Dts.Runtime.Package();
Executable exec = package.Executables.Add("STOCK:PipelineTask");
Microsoft.SqlServer.Dts.Runtime.TaskHost thMainPipe = exec as Microsoft.SqlServer.Dts.Runtime.TaskHost;
MainPipe dataFlowTask = thMainPipe.InnerObject as MainPipe;
IDTSComponentMetaData100 textFileSplitter = dataFlowTask.ComponentMetaDataCollection.New();
ComponentEventHandler events = new ComponentEventHandler();
dataFlowTask.Events = DtsConvert.GetExtendedInterface(events as IDTSComponentEvents);
textFileSplitter.Name = "Row Splitter Test";
textFileSplitter.ComponentClassID = typeof(Martin.SQLServer.Dts.TextFileSplitter).AssemblyQualifiedName;
CManagedComponentWrapper instance = textFileSplitter.Instantiate();
instance.ProvideComponentProperties();
IDTSOutput100 actual = textFileSplitter.OutputCollection.New();
ManageColumns.AddNumberOfRowsOutputColumns(actual);
Assert.AreEqual(3, actual.OutputColumnCollection.Count, "Number of Columns is wrong");
Assert.AreEqual(MessageStrings.KeyValueColumnName, actual.OutputColumnCollection[0].Name, "Column Name is wrong");
Assert.AreEqual(DataType.DT_STR, actual.OutputColumnCollection[0].DataType, "DataType is wrong");
Assert.AreEqual(255, actual.OutputColumnCollection[0].Length, "Length is wrong");
Assert.AreEqual(MessageStrings.KeyValueColumnDescription, actual.OutputColumnCollection[0].Description, "Description is wrong");
Assert.AreEqual(MessageStrings.NumberOfRowsColumnName, actual.OutputColumnCollection[1].Name, "Column Name is wrong");
Assert.AreEqual(DataType.DT_I8, actual.OutputColumnCollection[1].DataType, "DataType is wrong");
Assert.AreEqual(0, actual.OutputColumnCollection[1].Length, "Length is wrong");
Assert.AreEqual(MessageStrings.NumberOfRowsColumnDescription, actual.OutputColumnCollection[1].Description, "Description is wrong");
Assert.AreEqual(MessageStrings.KeyValueStatusColumnName, actual.OutputColumnCollection[2].Name, "Column Name is wrong");
Assert.AreEqual(DataType.DT_STR, actual.OutputColumnCollection[2].DataType, "DataType is wrong");
Assert.AreEqual(255, actual.OutputColumnCollection[2].Length, "Length is wrong");
Assert.AreEqual(MessageStrings.KeyValueStatusColumnDescription, actual.OutputColumnCollection[2].Description, "Description is wrong");
}
public override void PrimeOutput(int outputs, int[] outputIDs, PipelineBuffer[] buffers)
{
base.PrimeOutput(outputs, outputIDs, buffers);
IDTSOutput100 output = ComponentMetaData.OutputCollection.FindObjectByID(outputIDs[0]);
PipelineBuffer buffer = buffers[0];
String filePathVar = ComponentMetaData.CustomPropertyCollection[_FILE_PATH_VARIABLE_FIELD].Value.ToString();
String sheetNameVar = ComponentMetaData.CustomPropertyCollection[_SHEET_NAME_FIELD].Value.ToString();
DataTable dt = GetDataTableWithInputVar(filePathVar, sheetNameVar);
foreach (DataRow row in dt.Rows)
{
buffer.AddRow();
for (int x = 0; x < mapOutputColsToBufferCols.Length; x++)
{
if (row.IsNull(x))
{
buffer.SetNull(mapOutputColsToBufferCols[x]);
}
else
{
buffer[mapOutputColsToBufferCols[x]] = row[x];
}
}
}
buffer.SetEndOfRowset();
}
public BufferSink(IComponentBufferService bufferService, IDTSOutput100 output, bool treatEmptyStringAsNull)
{
ArgumentVerifier.CheckObjectArgument(bufferService, "bufferService");
ArgumentVerifier.CheckObjectArgument(output, "output");
this.bufferService = bufferService;
this.output = output;
this.treatEmptyStringAsNull = treatEmptyStringAsNull;
}
public static void AddOutputColumn(IDTSOutput100 output, string columnName, DataType dataType, int length, int precision, int scale, int codePage)
{
bool isExist = false;
foreach (IDTSOutputColumn100 outputColumn in output.OutputColumnCollection)
{
if (outputColumn.Name == columnName)
isExist = true;
}
if (!isExist)
{
IDTSOutputColumn100 newOutputColumn = output.OutputColumnCollection.New();
newOutputColumn.Name = columnName;
newOutputColumn.Description = "";
newOutputColumn.SetDataTypeProperties(dataType, length, precision, scale, codePage);
}
}
public static IDTSOutputColumn100 FindOutputColumnByName(string name, IDTSOutput100 output, bool ignoreCase)
{
if (output != null)
{
foreach (IDTSOutputColumn100 oc in output.OutputColumnCollection)
{
StringComparison comparisonType = ignoreCase ? StringComparison.InvariantCultureIgnoreCase : StringComparison.InvariantCulture;
if (oc.Name.Equals(name, comparisonType))
{
return oc;
}
}
}
return null;
}
/// <summary>
/// For the provided output, collects the output columns in an IEnumerable
/// </summary>
/// <param name="output"></param>
/// <returns></returns>
private static IEnumerable<IDTSOutputColumn100> GetOutputColumns(IDTSOutput100 output)
{
if (output == null)
yield break;
foreach (IDTSOutputColumn100 column in output.OutputColumnCollection)
yield return column;
}
/// <summary>
/// Constructs an output column and adds it to the provided output, using the column metadata from the provided BSON element.
/// </summary>
/// <param name="output"></param>
/// <param name="bsonElement"></param>
/// <returns></returns>
private IDTSOutputColumn100 BuildOutputColumn(IDTSOutput100 output, BsonElement bsonElement)
{
IDTSOutputColumn100 outColumn = output.OutputColumnCollection.New();
// Set the properties of the output column.
outColumn.Name = bsonElement.Name;
DataType dt = GetColumnDataType(bsonElement.Value);
int length = 0;
int codepage = 0;
if (dt == DataType.DT_STR)
{
length = 256;
codepage = 1252;
}
outColumn.SetDataTypeProperties(dt, length, 0, 0, codepage);
// Setting these values enables the end-user to configure the error behavior on a per-column basis
if (!output.IsErrorOut)
{
outColumn.ErrorRowDisposition = DTSRowDisposition.RD_FailComponent;
outColumn.TruncationRowDisposition = DTSRowDisposition.RD_FailComponent;
}
return outColumn;
}
private DTSValidationStatus ValidateErrorOutput(IDTSOutput100 errorOutput, DTSValidationStatus oldStatus)
{
DTSValidationStatus returnStatus = oldStatus;
IDTSOutputColumnCollection100 outputColumnCollection = errorOutput.OutputColumnCollection;
if (outputColumnCollection.Count == 0)
{
this.PostError(MessageStrings.NoErrorOutputColumns);
returnStatus = DTSValidationStatus.VS_ISCORRUPT;
}
return returnStatus;
}
private DTSValidationStatus ValidateRegularOutput(IDTSOutput100 output, DTSValidationStatus oldStatus)
{
DTSValidationStatus returnStatus = oldStatus;
IDTSOutputColumnCollection100 outputColumnCollection = output.OutputColumnCollection;
if (outputColumnCollection.Count == 0)
{
this.PostError(MessageStrings.NoOutputColumns);
if (returnStatus != DTSValidationStatus.VS_ISCORRUPT)
{
returnStatus = DTSValidationStatus.VS_ISBROKEN;
}
}
else
{
returnStatus = ValidateOutputColumns(outputColumnCollection, returnStatus);
}
return returnStatus;
}
private void GenerateOutputColumns(RowData rowData, IDTSOutput100 output)
{
if (rowData != null && rowData.ColumnCount > 0)
{
int outputID = output.ID;
IDTSOutputColumnCollection100 outputColumnCollection = output.OutputColumnCollection;
bool isUnicode = (bool)this.GetComponentPropertyValue(PropertiesManager.IsUnicodePropName);
bool columnNamesInFirstRow = (bool)this.GetComponentPropertyValue(PropertiesManager.ColumnNamesInFirstRowPropName);
for (int i = 0; i < rowData.ColumnCount; i++)
{
string outputColumnName = string.Empty;
if (columnNamesInFirstRow)
{
outputColumnName = rowData.GetColumnData(i);
}
else
{
outputColumnName = MessageStrings.DefaultColumnName(i + 1);
}
IDTSOutputColumn100 outputColumn = this.InsertOutputColumnAt(outputID, i, outputColumnName, string.Empty);
if (isUnicode)
{
outputColumn.SetDataTypeProperties(DataType.DT_WSTR, DefaultStringColumnSize, 0, 0, 0);
}
else
{
int codePage = (int)this.GetComponentPropertyValue(PropertiesManager.CodePagePropName);
outputColumn.SetDataTypeProperties(DataType.DT_STR, DefaultStringColumnSize, 0, 0, codePage);
}
}
}
}
private void GenerateColumnsFromFile(IDTSOutput100 output)
{
if (System.IO.File.Exists(this.fileName))
{
FileReader reader = new FileReader(this.fileName, this.GetEncoding());
DelimitedFileParser parser = this.CreateParser();
try
{
parser.SkipHeaderRows(reader);
RowData rowData = new RowData();
parser.ParseNextRow(reader, rowData);
this.GenerateOutputColumns(rowData, output);
}
finally
{
reader.Close();
}
}
else
{
this.PostErrorAndThrow(MessageStrings.FileDoesNotExist(this.fileName));
}
}
internal BipsProxyIDTSOutput100(IDTSOutput100 innerObject)
{
_innerObject = innerObject;
}
private void CreateOutputAndMetaDataColumns(IDTSOutput100 output)
{
IDTSOutputColumn100 outName = output.OutputColumnCollection.New();
outName.Name = "Name";
outName.Description = "The Name value retrieved from File";
outName.SetDataTypeProperties(DataType.DT_STR, 50, 0, 0, 1252);
CreateExternalMetaDataColumn(output.ExternalMetadataColumnCollection, outName);
IDTSOutputColumn100 outAge = output.OutputColumnCollection.New();
outAge.Name = "Age";
outAge.Description = "The Age value retrieved from File";
outAge.SetDataTypeProperties(DataType.DT_I4, 0, 0, 0, 0);
//Create an external metadata column to go alongside with it
CreateExternalMetaDataColumn(output.ExternalMetadataColumnCollection, outAge);
IDTSOutputColumn100 outMarried = output.OutputColumnCollection.New();
outMarried.Name = "Married";
outMarried.Description = "The Married value retrieved from File";
outMarried.SetDataTypeProperties(DataType.DT_BOOL, 0, 0, 0, 0);
//Create an external metadata column to go alongside withit
CreateExternalMetaDataColumn(output.ExternalMetadataColumnCollection, outMarried);
IDTSOutputColumn100 outSalary = output.OutputColumnCollection.New();
outSalary.Name = "Salary";
outSalary.Description = "The Salary value retrieved from File";
outSalary.SetDataTypeProperties(DataType.DT_DECIMAL, 0, 0, 10, 0);
//Create an external metadata column to go alongside with it
CreateExternalMetaDataColumn(output.ExternalMetadataColumnCollection, outSalary);
}
public DataFlowOutputNodeFactory(IDTSOutput100 input)
{
_input = input;
}
public void AttachPathAndPropagateNotifications(IDTSOutput100 pIDTSOutput, IDTSInput100 pIDTSInput)
{
_innerObject.AttachPathAndPropagateNotifications(pIDTSOutput, pIDTSInput);
}
private void AddErrorOutputColumns(IDTSOutput100 errorOutput)
{
int errorOutputID = errorOutput.ID;
IDTSOutputColumnCollection100 outputColumnCollection = errorOutput.OutputColumnCollection;
IDTSOutputColumn100 outputColumn = outputColumnCollection.New();
outputColumn.Name = ErrorMessageColumnName;
outputColumn.SetDataTypeProperties(DataType.DT_WSTR, 4000, 0, 0, 0);
outputColumn = outputColumnCollection.New();
outputColumn.Name = ColumnDataColumnName;
outputColumn.SetDataTypeProperties(DataType.DT_WSTR, 4000, 0, 0, 0);
outputColumn = outputColumnCollection.New();
outputColumn.Name = RowDataColumnName;
outputColumn.SetDataTypeProperties(DataType.DT_WSTR, 4000, 0, 0, 0);
}
public static bool DoesExternalMetaDataMatchOutputMetaData(IDTSOutput100 output)
{
if (output == null)
{
throw new ArgumentNullException("output");
}
IDTSExternalMetadataColumnCollection100 externalMetaData
= output.ExternalMetadataColumnCollection;
foreach (IDTSOutputColumn100 column in output.OutputColumnCollection)
{
if (!DoesColumnMetaDataMatch(
column,
externalMetaData.GetObjectByID(column.ExternalMetadataColumnID)))
{
return false;
}
}
return true;
}
public static void FixExternalMetaDataColumns(IDTSOutput100 output)
{
if (output == null)
{
throw new ArgumentNullException("output");
}
IDTSExternalMetadataColumnCollection100 externalMetaData
= output.ExternalMetadataColumnCollection;
externalMetaData.RemoveAll();
foreach (IDTSOutputColumn100 column in output.OutputColumnCollection)
{
IDTSExternalMetadataColumn100 exColumn = externalMetaData.New();
exColumn.Name = column.Name;
exColumn.DataType = column.DataType;
exColumn.Precision = column.Precision;
exColumn.Scale = column.Scale;
exColumn.Length = column.Length;
column.ExternalMetadataColumnID = exColumn.ID;
}
}
public static void FixOutputColumnMetaData(IDTSOutput100 output)
{
if (output == null)
{
throw new ArgumentNullException("output");
}
if (output.ExternalMetadataColumnCollection.Count == 0)
{
return;
}
foreach (IDTSOutputColumn100 column in output.OutputColumnCollection)
{
IDTSExternalMetadataColumn100 exColumn
= output.ExternalMetadataColumnCollection.GetObjectByID(
column.ExternalMetadataColumnID);
if (!DoesColumnMetaDataMatch(column, exColumn))
{
column.SetDataTypeProperties(
exColumn.DataType,
exColumn.Length,
exColumn.Precision,
exColumn.Scale,
exColumn.CodePage);
}
}
}
public static bool DoesOutputColumnMetaDataMatchExternalColumnMetaData(IDTSOutput100 output)
{
if (output == null)
{
throw new ArgumentNullException("output");
}
bool areAllOutputColumnsValid = true;
if (output.ExternalMetadataColumnCollection.Count == 0)
{
return false;
}
foreach (IDTSOutputColumn100 column in output.OutputColumnCollection)
{
bool Cancel = false;
IDTSExternalMetadataColumn100 exColumn
= output.ExternalMetadataColumnCollection.GetObjectByID(
column.ExternalMetadataColumnID);
if (!DoesColumnMetaDataMatch(column, exColumn))
{
output.Component.FireError(
0,
output.Component.Name,
@"The output column " + column.IdentificationString + @" does not match the external metadata.",
@"",
0,
out Cancel);
areAllOutputColumnsValid = false;
}
}
return areAllOutputColumnsValid;
}
