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);
}
}
}
protected virtual void MatchInputColumns(Dictionary <string, int> converted, bool needschema)
{
IDTSComponentMetaData100 comp = this.MetadataCollection;
CManagedComponentWrapper dcomp = comp.Instantiate();
IDTSInput100 input = comp.InputCollection[0];
IDTSVirtualInput100 vInput = input.GetVirtualInput();
IDTSVirtualInputColumnCollection100 vColumns = vInput.VirtualInputColumnCollection;
IDTSExternalMetadataColumnCollection100 exColumns = input.ExternalMetadataColumnCollection;
if (exColumns != null && exColumns.Count > 0)
{
bool hasMatch = false;
foreach (IDTSExternalMetadataColumn100 exColumn in exColumns)
{
int inputColId;
if (converted.ContainsKey(exColumn.Name.ToLower()))
{
inputColId = (int)converted[exColumn.Name.ToLower()];
}
else
{
inputColId = FindVirtualInputColumnId(vColumns, exColumn.Name);
}
if (inputColId == 0)
{
//the column wasn't found if the Id is 0, so we'll print out a message and skip this row.
PrintOutput.PrintToOutput("DE could not map external column " + exColumn.Name + ". Skipping column.", DERun.Debug);
}
else
{
// create input column
IDTSInputColumn100 vCol = dcomp.SetUsageType(input.ID, vInput, inputColId, DTSUsageType.UT_READONLY);
// and then we'll map it to the input row.
dcomp.MapInputColumn(input.ID, vCol.ID, exColumn.ID);
hasMatch = true;
}
}
if (!hasMatch)
{
throw new InvalidArgumentException("Unable to map input to destination");
}
}
//if output schema is required and not provided
else if (needschema)
{
//PrintOutput.PrintToError("No destination columns available");
throw new InvalidArgumentException("No destination columns available");
}
//otherwise use virtual inputs
else
{
foreach (IDTSVirtualInputColumn100 vColumn in vColumns)
{
// create input column for all virtual input columns
dcomp.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
}
}
}
public void MapColumns(IDTSComponentMetaData100 DestinationTask
, CManagedComponentWrapper InstanceDestination
, DTSUsageType dtsUsageType, string ErrorDetail)
{
#region map the columns
IDTSInput100 input = DestinationTask.InputCollection[0];
IDTSVirtualInput100 vInput = input.GetVirtualInput();
IDTSInputColumn100 vCol = null;
string columnName = string.Empty;
try
{
if (dtsUsageType == DTSUsageType.UT_READONLY)
{
foreach (IDTSVirtualInputColumn100 vColumn in vInput.VirtualInputColumnCollection)
{
InstanceDestination.SetUsageType(input.ID, vInput, vColumn.LineageID, dtsUsageType);
}
foreach (IDTSInputColumn100 col in input.InputColumnCollection)
{
columnName = col.Name;
IDTSExternalMetadataColumn100 exCol = input.ExternalMetadataColumnCollection[col.Name];
InstanceDestination.MapInputColumn(input.ID, col.ID, exCol.ID);
}
}
else
{
foreach (IDTSVirtualInputColumn100 vColumn in vInput.VirtualInputColumnCollection)
{
vCol = InstanceDestination.SetUsageType(input.ID, vInput, vColumn.LineageID, dtsUsageType);
columnName = vCol.Name;
IDTSExternalMetadataColumn100 exCol = input.ExternalMetadataColumnCollection[vColumn.Name];
InstanceDestination.MapInputColumn(input.ID, vCol.ID, exCol.ID);
}
}
}
catch (Exception ex)
{
throw new Exception("Table Mapping failed for source to destination for the column '" + columnName + "'. " + ErrorDetail);
}
#endregion
}
public Executable AddTask <T>(EntityTypeConfiguration <T> map, ConnectionManager manager) where T : class
{
FileInfo fileInfo = new FileInfo(manager.ConnectionString);
string fileName = fileInfo.Name.Substring(0, fileInfo.Name.Length - 4);
string dataFlowName = $"Data Flow from {fileName} to {map.GetTableName()} in the {map.GetSchemaName()} Schema";
string dataFlowDescription = $"This data flow task will transfer the records from {manager.ConnectionString} to the {map.GetFullTableName()}";
Executable executable = CensusPackageExecutables.Contains(dataFlowName)
? CensusPackageExecutables[dataFlowName]
: CensusPackageExecutables.Add("STOCK:PipelineTask");
TaskHost task = executable as TaskHost;
if (task == null)
{
throw new ArgumentException("Failed to retrieve executable as TaskHost!");
}
task.Name = dataFlowName;
task.Description = dataFlowDescription;
MainPipe dataFlow = task.InnerObject as MainPipe;
if (dataFlow == null)
{
throw new ArgumentException("Unable to retrieve task as a MainPipe!");
}
dataFlow.ComponentMetaDataCollection.RemoveAll();
dataFlow.Events = DtsConvert.GetExtendedInterface(new SSISComponentEventHandler());
#region Source
IDTSComponentMetaData100 source = dataFlow.ComponentMetaDataCollection.New();
source.Name = $"{FlatFileSourceComponentInfo.Name} reference to {fileName}";
source.Description = $"This component will read all of the records located in {fileInfo.FullName}";
source.ContactInfo = "Anthony Hart | [email protected]";
source.ComponentClassID = FlatFileSourceComponentInfo.CreationName;
CManagedComponentWrapper sourceWrapper = source.Instantiate();
sourceWrapper.ProvideComponentProperties();
source.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(manager);
source.RuntimeConnectionCollection[0].ConnectionManagerID = manager.ID;
source.RuntimeConnectionCollection[0].Name = "FlatFileConnection";
source.RuntimeConnectionCollection[0].Description = manager.Description;
sourceWrapper.SetComponentProperty("RetainNulls", true);
sourceWrapper.AcquireConnections(null);
sourceWrapper.ReinitializeMetaData();
sourceWrapper.ReleaseConnections();
#endregion Source
#region Destination
IDTSComponentMetaData100 destination = dataFlow.ComponentMetaDataCollection.New();
destination.Name = $"{ADONETDestinationComponentInfo.Name} reference to {map.GetTableName()} in the {map.GetSchemaName()} Schema";
destination.Description = $"This component will import all of the records that were read from the {FlatFileSourceComponentInfo.Name} component into the {map.GetFullTableName()} table";
destination.ContactInfo = "Anthony Hart | [email protected]";
destination.ComponentClassID = ADONETDestinationComponentInfo.CreationName;
CManagedComponentWrapper destinationWrapper = destination.Instantiate();
destinationWrapper.ProvideComponentProperties();
destination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(DefaultConnection);
destination.RuntimeConnectionCollection[0].ConnectionManagerID = DefaultConnection.ID;
destination.RuntimeConnectionCollection[0].Name = "IDbConnection";
destination.RuntimeConnectionCollection[0].Description = DefaultConnection.Description;
destinationWrapper.SetComponentProperty("CommandTimeout", 600);
destinationWrapper.SetComponentProperty("TableOrViewName", map.GetFullTableName());
destinationWrapper.AcquireConnections(null);
destinationWrapper.ReinitializeMetaData();
destinationWrapper.ReleaseConnections();
#endregion Destination
#region Pathing
IDTSPath100 path = dataFlow.PathCollection.New();
path.AttachPathAndPropagateNotifications(source.OutputCollection[0], destination.InputCollection[0]);
#endregion
#region Column Mapping
IDTSInput100 input = destination.InputCollection[0];
IDTSVirtualInput100 virtualInput = input.GetVirtualInput();
foreach (IDTSVirtualInputColumn100 vColumn in virtualInput.VirtualInputColumnCollection)
{
IDTSInputColumn100 vCol = destinationWrapper.SetUsageType(input.ID, virtualInput, vColumn.LineageID, vColumn.Name == "TERMINATOR" ? DTSUsageType.UT_IGNORED : DTSUsageType.UT_READWRITE);
if (vCol != null)
{
destinationWrapper.MapInputColumn(input.ID, vCol.ID, input.ExternalMetadataColumnCollection[vCol.Name].ID);
}
}
#endregion Column Mapping
return(executable);
}
public IDTSComponentMetaData100 AddComp_OleDBCommand(string componentName,
string conManName,
IDTSOutput100 outCols,
string sqlCmd,
Dictionary <string, string> paramMapping
)
{
//
// --------------------------------------------------------------------------------------------------------
// Create
IDTSComponentMetaData100 Comp = dmp.ComponentMetaDataCollection.New();
Comp.ComponentClassID = "Microsoft.OLEDBCommand";
// Instantiate
CManagedComponentWrapper Inst = Comp.Instantiate();
Inst.ProvideComponentProperties();
Comp.Name = componentName;
Comp.Description = "bloczek OLEDBSource";
// Connect
IDTSPath100 pth = dmp.PathCollection.New();
pth.AttachPathAndPropagateNotifications(outCols, Comp.InputCollection[0]);
// GetConnectionManager
ConnectionManager cm = prj.ConnectionManagerItems[conManName + ".conmgr"].ConnectionManager;
// Parametrize #1
Comp.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(cm);
Comp.RuntimeConnectionCollection[0].ConnectionManagerID = cm.ID;
Inst.SetComponentProperty("SqlCommand", sqlCmd);
Inst.AcquireConnections(null); // Establishes a connection to a connection manager
Inst.ReinitializeMetaData(); // Called to allow the component to repair problems with the IDTSComponentMetaData100 object that were identified by the component during the Validate() method.
Inst.ReleaseConnections(); // Frees the connections established by the component during AcquireConnections(Object).
// Parametrize #2
IDTSInput100 input = Comp.InputCollection[0];
IDTSVirtualInput100 vInput = input.GetVirtualInput();
IDTSVirtualInputColumnCollection100 sourceColumns = vInput.VirtualInputColumnCollection;
foreach (KeyValuePair <string, string> kvp in paramMapping)
{
IDTSVirtualInputColumn100 vColumn = vInput.VirtualInputColumnCollection[kvp.Value];
Inst.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
IDTSExternalMetadataColumn100 exColumn = Comp.InputCollection[0].ExternalMetadataColumnCollection[kvp.Key];
IDTSInputColumn100 inColumn = Comp.InputCollection[0].InputColumnCollection[kvp.Value];
Inst.MapInputColumn(Comp.InputCollection[0].ID, inColumn.ID, exColumn.ID);
}
return(Comp);
}
public IDTSComponentMetaData100 AddComp_OleDBDestination(string componentName,
string destObjectName,
string conManName,
IDTSOutput100 outCols,
CreateTableFlag createTableFlag = CreateTableFlag.Create
)
{
// Create
IDTSComponentMetaData100 Comp = dmp.ComponentMetaDataCollection.New();
Comp.ComponentClassID = "Microsoft.OLEDBDestination";
Comp.ValidateExternalMetadata = true;
// Instantiate
CManagedComponentWrapper Inst = Comp.Instantiate();
Inst.ProvideComponentProperties();
Comp.Name = componentName;
Comp.Description = "Destinejszyn!";
// ConnManager
ConnectionManager cm = prj.ConnectionManagerItems[conManName + ".conmgr"].ConnectionManager;
Comp.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(cm);
Comp.RuntimeConnectionCollection[0].ConnectionManagerID = cm.ID;
// Connect Tasks
IDTSPath100 pth = dmp.PathCollection.New();
pth.AttachPathAndPropagateNotifications(outCols, Comp.InputCollection[0]);
if (createTableFlag != CreateTableFlag.NoAction)
{
List <DTMInput> inputColList = new List <DTMInput>();
IDTSVirtualInput100 v = Comp.InputCollection[0].GetVirtualInput();
foreach (IDTSVirtualInputColumn100 z in v.VirtualInputColumnCollection)
{
inputColList.Add(new DTMInput
(
z.Name
, z.DataType.ToString()
, z.CodePage
, z.Length
, z.Precision
, z.Scale
)
);
}
string sqlcmd;
sqlcmd = GetSQL(destObjectName, inputColList, createTableFlag, new Mapper());
ExecSQL(sqlcmd, cm);
}
// Set Destination
Inst.SetComponentProperty("AccessMode", 3);
Inst.SetComponentProperty("OpenRowset", destObjectName);
// Get Metadata
Inst.AcquireConnections(null);
Inst.ReinitializeMetaData();
Inst.ReleaseConnections();
// Match Output->Input
IDTSInput100 input = Comp.InputCollection[0];
IDTSVirtualInput100 vInput = input.GetVirtualInput();
foreach (IDTSVirtualInputColumn100 vColumn in vInput.VirtualInputColumnCollection)
{
Inst.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
}
foreach (IDTSInputColumn100 inColumn in Comp.InputCollection[0].InputColumnCollection)
{
foreach (IDTSExternalMetadataColumn100 exColumn in Comp.InputCollection[0].ExternalMetadataColumnCollection)
{
if (exColumn.Name == inColumn.Name)
{
Inst.MapInputColumn(Comp.InputCollection[0].ID, inColumn.ID, exColumn.ID);
}
}
}
// Return
Inst.AcquireConnections(null);
Inst.ReinitializeMetaData();
Inst.ReleaseConnections();
return(Comp);
}