private string GetSQL(string tn, List <DTMInput> ld, CreateTableFlag createTableFlag, IMapper m)
{
StringBuilder sb = new StringBuilder();
foreach (DTMInput d in ld)
{
sb.Append("\n,\t");
sb.Append(m.Map(d));
}
sb.Remove(0, 2);
sb.Append("\n)\n;");
switch (createTableFlag)
{
case CreateTableFlag.Create:
sb.Insert(0, $"CREATE TABLE {tn}\n(\n");
break;
case CreateTableFlag.CreateIfNotExists:
sb.Insert(0, $"IF OBJECT_ID('{tn}') IS NOT NULL RETURN;\nCREATE TABLE {tn}\n(\n");
break;
case CreateTableFlag.DropAndCreate:
sb.Insert(0, $"IF OBJECT_ID('{tn}') IS NOT NULL DROP TABLE {tn};\nCREATE TABLE {tn}\n(\n");
break;
}
return(sb.ToString());
}
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);
}