public void TestChangeRowsProcessedOutputAddColumn()
{
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();
Boolean exceptionThrown = false;
try
{
instance.InsertOutputColumnAt(textFileSplitter.OutputCollection[3].ID, 0, "New Error Message", String.Empty);
}
catch (COMException ex)
{
Assert.AreEqual(MessageStrings.CantChangeOutputProperties("RowsProcessed"), ex.Message, "Exception Message Wrong");
exceptionThrown = true;
}
Assert.IsTrue(exceptionThrown, "Exception Not Thrown");
}
// todo:
// CacheMode
// https://blogs.msdn.microsoft.com/mattm/2009/01/02/api-sample-lookup-transform/
public IDTSComponentMetaData100 AddComp_Lookup(string componentName,
string conManName,
string sqlComm,
IDTSOutput100 outCols,
List <string> joinColumns,
Dictionary <string, string> newColumns,
DTSRowDisposition rowDisposition = DTSRowDisposition.RD_IgnoreFailure,
int redirectRowsToNoMatchOutput = 0
)
{
// Create
IDTSComponentMetaData100 Comp = dmp.ComponentMetaDataCollection.New();
Comp.ComponentClassID = "Microsoft.Lookup";
// Instantiate
CManagedComponentWrapper Inst = Comp.Instantiate();
Inst.ProvideComponentProperties();
Comp.Name = componentName;
Comp.Description = "Dodany Lookup";
// Connect
IDTSPath100 pth = dmp.PathCollection.New();
pth.AttachPathAndPropagateNotifications(outCols, Comp.InputCollection[0]);
// GetConnectionManager
ConnectionManager cm = prj.ConnectionManagerItems[conManName + ".conmgr"].ConnectionManager;
Comp.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(cm);
Comp.RuntimeConnectionCollection[0].ConnectionManagerID = cm.ID;
// Parametrize #1
IDTSOutput100 lmo = Comp.OutputCollection["Lookup Match Output"];
lmo.ErrorRowDisposition = rowDisposition;
// Cache Type:
// 0 = Full
// 1 = Partial
// 2 = None
Inst.SetComponentProperty("CacheType", 0);
// 0 = FailComponent, IgnoreFailure, RedirectRowsToErrorOutput
// 1 = RedirectRowsToNoMatchOutput
Inst.SetComponentProperty("NoMatchBehavior", redirectRowsToNoMatchOutput);
Inst.SetComponentProperty("SqlCommand", sqlComm);
// MetaData
Inst.AcquireConnections(null);
Inst.ReinitializeMetaData();
Inst.ReleaseConnections();
// Parametrize #2 - Join Columns
IDTSInput100 lkpInput = Comp.InputCollection[0];
IDTSInputColumnCollection100 lkpInputCols = lkpInput.InputColumnCollection;
IDTSVirtualInput100 lkpVirtInput = lkpInput.GetVirtualInput();
IDTSVirtualInputColumnCollection100 lkpVirtInputCols = lkpVirtInput.VirtualInputColumnCollection;
foreach (string columnName in joinColumns)
{
IDTSVirtualInputColumn100 vColumn = lkpVirtInputCols[columnName];
IDTSInputColumn100 inputColumn = Inst.SetUsageType(lkpInput.ID, lkpVirtInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
Inst.SetInputColumnProperty(lkpInput.ID, inputColumn.ID, "JoinToReferenceColumn", columnName);
}
// Parametrize #3 - New Lookup Columns
IDTSOutput100 lookupMatchOutput = Comp.OutputCollection["Lookup Match Output"];
foreach (string srcCol in newColumns.Keys)
{
string newColumnName = newColumns[srcCol];
string description = string.Format("Copy of {0}", srcCol);
IDTSOutputColumn100 outputColumn = Inst.InsertOutputColumnAt(lookupMatchOutput.ID, 0, newColumnName, description);
Inst.SetOutputColumnProperty(lookupMatchOutput.ID, outputColumn.ID, "CopyFromReferenceColumn", srcCol);
}
// Return
return(Comp);
}
public IDictionary <int, int> PropagateInputColumns(IDTSExternalMetadataColumnCollection100 exColumns)
{
Dictionary <int, int> converted = new Dictionary <int, int>();
IDTSComponentMetaData100 comp = MetadataCollection;
CManagedComponentWrapper dcomp = comp.Instantiate();
IDTSInput100 input = comp.InputCollection[0];
IDTSVirtualInput100 vInput = input.GetVirtualInput();
IDTSVirtualInputColumnCollection100 vColumns = vInput.VirtualInputColumnCollection;
IDTSOutput100 output = comp.OutputCollection[0];
output.TruncationRowDisposition = DTSRowDisposition.RD_NotUsed;
output.ErrorRowDisposition = DTSRowDisposition.RD_NotUsed;
//create input columns for destination external
foreach (IDTSExternalMetadataColumn100 exColumn in exColumns)
{
int vColumnID = FindVirtualInputColumnId(vColumns, exColumn.Name);
if (vColumnID != 0)
{
//do type conversion
IDTSVirtualInputColumn100 vColumn = vInput.VirtualInputColumnCollection.GetVirtualInputColumnByLineageID(vColumnID);
if (exColumn.DataType != vColumn.DataType ||
vColumn.Length != exColumn.Length ||
exColumn.Precision != vColumn.Precision ||
exColumn.Scale != vColumn.Scale)
{
dcomp.SetUsageType(input.ID, vInput, vColumnID, DTSUsageType.UT_READONLY);
IDTSOutputColumn100 oColumn = dcomp.InsertOutputColumnAt(output.ID, 0, exColumn.Name, String.Empty);
//IDTSOutputColumn100 oColumn = output.OutputColumnCollection.New();
//oColumn.Name = exColumn.Name;
oColumn.SetDataTypeProperties(exColumn.DataType, exColumn.Length, exColumn.Precision, exColumn.Scale, exColumn.CodePage);
oColumn.ExternalMetadataColumnID = 0;
oColumn.MappedColumnID = 0;
oColumn.ErrorRowDisposition = DTSRowDisposition.RD_FailComponent;
oColumn.TruncationRowDisposition = DTSRowDisposition.RD_FailComponent;
//IDTSCustomProperty100 property = oColumn.CustomPropertyCollection.New();
//property.Name = "SourceInputColumnLineageID";
//property.Value = vColumnID;
//property = oColumn.CustomPropertyCollection.New();
//property.Name = "FastParse";
//property.Value = false;
dcomp.SetOutputColumnProperty(
output.ID,
oColumn.ID,
"SourceInputColumnLineageID",
vColumnID);
dcomp.SetOutputColumnProperty(
output.ID,
oColumn.ID,
"FastParse",
false);
//set of derived columns
converted.Add(exColumn.ID, oColumn.LineageID);
}
//else
//{
// m_converted.Add(exColumn.ID, vColumnID);
//}
}
else
{
//the column wasn't found if the Id is 0, so we'll print out a message and skip this row.
//PrintOutput.PrintToOutput("Converter: Could not map external column " + exColumn.Key + ". Skipping column.");
}
}
return(converted);
}