public DerivedColumns(Packages.VulcanPackage vulcanPackage, MainPipe dataFlowTask, IDTSComponentMetaData90 parentComponent, string name, string description )
: base(vulcanPackage,
dataFlowTask,
parentComponent,
name,
description)
{
_dcCom = dataFlowTask.ComponentMetaDataCollection.New();
_dcCom.ComponentClassID = "DTSTransform.DerivedColumn";
//IMPORTANT! If you do not Instantiate() first, the component names do not get set... this is bad.
_dci = _dcCom.Instantiate();
_dci.ProvideComponentProperties();
_dcCom.Name = Name;
_dcCom.Description = Description;
dataFlowTask.PathCollection.New().AttachPathAndPropagateNotifications(
parentComponent.OutputCollection[0],
_dcCom.InputCollection[0]
);
_dci.AcquireConnections(null);
_dci.ReinitializeMetaData();
_dci.ReleaseConnections();
}
public ConditionalSplit(Packages.VulcanPackage vulcanPackage, MainPipe dataFlowTask, IDTSComponentMetaData90 parentComponent, string name, string description)
: base(vulcanPackage,
dataFlowTask,
parentComponent,
name,
description)
{
_csCom = dataFlowTask.ComponentMetaDataCollection.New();
_csCom.ComponentClassID = "DTSTransform.ConditionalSplit";
//IMPORTANT! If you do not Instantiate() first, the component names do not get set... this is bad.
_csi = _csCom.Instantiate();
_csi.ProvideComponentProperties();
_csCom.Name = Name;
_csCom.Description = Description;
_csCom.ValidateExternalMetadata = true;
_csi.AcquireConnections(null);
_csi.ReinitializeMetaData();
_csi.ReleaseConnections();
dataFlowTask.PathCollection.New().AttachPathAndPropagateNotifications(
parentComponent.OutputCollection[0],
_csCom.InputCollection[0]
);
IDTSVirtualInput90 vi = _csCom.InputCollection[0].GetVirtualInput();
foreach (IDTSVirtualInputColumn90 vic in vi.VirtualInputColumnCollection)
{
this.SetInputUsageType(vi, vic, DTSUsageType.UT_READONLY);
}
}
public OLEDBCommand(
VulcanPackage vulcanPackage,
MainPipe dataFlowTask,
IDTSComponentMetaData90
parentComponent,
string name,
string description,
Connection connection,
string command
)
: base(vulcanPackage,
dataFlowTask,
parentComponent,
name,
description)
{
_oledbCom = dataFlowTask.ComponentMetaDataCollection.New();
_oledbCom.ComponentClassID = "DTSTransform.OLEDBCommand";
//IMPORTANT! If you do not Instantiate() first, the component names do not get set... this is bad.
_oledbComI = _oledbCom.Instantiate();
_oledbComI.ProvideComponentProperties();
_oledbCom.Name = name;
_oledbCom.Description = description;
_oledbCom.RuntimeConnectionCollection[0].ConnectionManagerID = connection.ConnectionManager.ID;
_oledbCom.RuntimeConnectionCollection[0].ConnectionManager =
DTS.DtsConvert.ToConnectionManager90(connection.ConnectionManager);
_oledbComI.SetComponentProperty("SqlCommand", command);
dataFlowTask.PathCollection.New().AttachPathAndPropagateNotifications(
parentComponent.OutputCollection[0],
_oledbCom.InputCollection[0]
);
try
{
_oledbComI.AcquireConnections(null);
_oledbComI.ReinitializeMetaData();
_oledbComI.ReleaseConnections();
}
catch (System.Runtime.InteropServices.COMException ce)
{
Message.Trace(Severity.Error,ce,"OLEDBCommand: {3}: {2}: Source {0}: Command {1}", connection.ConnectionManager.Name, command, ce.Message, _oledbCom.GetErrorDescription(ce.ErrorCode));
}
catch (Exception e)
{
Message.Trace(Severity.Error,e,"OLEDBCommand: {2}: Source {0}: Command {1}", connection.ConnectionManager.Name, command, e.Message);
}
AutoMap();
}
public void TestIDTSOutputCreator()
{
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();
output.Name = "New # Output";
output.ErrorRowDisposition = DTSRowDisposition.RD_NotUsed;
ManageProperties.AddOutputProperties(output.CustomPropertyCollection);
ManageColumns.AddNumberOfRowsOutputColumns(output);
ManageProperties propertyManager = new ManageProperties();
propertyManager.PostErrorEvent += new PostErrorDelegate(this.PostError);
errorMessages = new List <string>();
SSISOutput actual = new SSISOutput(output, null);
Assert.IsNotNull(actual, "SSISOutput is null");
Assert.IsNotNull(actual.CustomPropertyCollection, "Custom Property Collection is null");
Assert.IsNotNull(actual.OutputColumnCollection, "Column Collection is null");
Assert.AreEqual(3, actual.CustomPropertyCollection.Count, "Custom Property Collection Count is Wrong");
Assert.AreEqual(3, actual.OutputColumnCollection.Count, "Output Column Collection Count is Wrong");
Assert.IsNotNull(ManageProperties.GetPropertyValue(actual.CustomPropertyCollection, ManageProperties.typeOfOutput));
Assert.IsNotNull(ManageProperties.GetPropertyValue(actual.CustomPropertyCollection, ManageProperties.rowTypeValue));
Assert.IsNotNull(ManageProperties.GetPropertyValue(actual.CustomPropertyCollection, ManageProperties.masterRecordID));
Assert.AreEqual("_NewOutput", actual.Name, "Name is incorrect");
Assert.AreEqual(DTSRowDisposition.RD_NotUsed, actual.ErrorRowDisposition, "Error Row Disposition is incorrect");
}
public TermLookup(
VulcanPackage vulcanPackage,
MainPipe dataFlowTask,
IDTSComponentMetaData90 parentComponent,
string name,
string description,
Connection connection,
bool isCaseSensitive,
string refTermColumn,
string refTermTable
)
:
base(
vulcanPackage,
dataFlowTask,
parentComponent,
name,
description
)
{
TermLookupCom = dataFlowTask.ComponentMetaDataCollection.New();
TermLookupCom.ComponentClassID = "DTSTransform.TermLookup";
TermLookupComI = TermLookupCom.Instantiate();
TermLookupComI.ProvideComponentProperties();
TermLookupCom.Name = name;
TermLookupCom.RuntimeConnectionCollection[0].ConnectionManagerID = connection.ConnectionManager.ID;
TermLookupCom.RuntimeConnectionCollection[0].ConnectionManager =
DTS.DtsConvert.ToConnectionManager90(connection.ConnectionManager);
TermLookupComI.SetComponentProperty("IsCaseSensitive", isCaseSensitive);
TermLookupComI.SetComponentProperty("RefTermColumn", refTermColumn);
TermLookupComI.SetComponentProperty("RefTermTable", refTermTable);
TermLookupCom.OutputCollection[0].ErrorRowDisposition = DTSRowDisposition.RD_IgnoreFailure;
dataFlowTask.PathCollection.New().AttachPathAndPropagateNotifications(
parentComponent.OutputCollection[0],
TermLookupCom.InputCollection[0]
);
TermLookupComI.AcquireConnections(null);
TermLookupComI.ReinitializeMetaData();
TermLookupComI.ReleaseConnections();
}
public Lookup(
VulcanPackage vulcanPackage,
MainPipe dataFlowTask,
IDTSComponentMetaData90
parentComponent,
string name,
string description,
Connection connection,
string query
)
:
base(
vulcanPackage,
dataFlowTask,
parentComponent,
name,
description
)
{
lookupCom = dataFlowTask.ComponentMetaDataCollection.New();
lookupCom.ComponentClassID = "DTSTransform.Lookup";
lookupComI = lookupCom.Instantiate();
lookupComI.ProvideComponentProperties();
lookupCom.Name = name;
lookupCom.RuntimeConnectionCollection[0].ConnectionManagerID = connection.ConnectionManager.ID;
lookupCom.RuntimeConnectionCollection[0].ConnectionManager =
DTS.DtsConvert.ToConnectionManager90(connection.ConnectionManager);
lookupComI.SetComponentProperty("SqlCommand", query);
lookupCom.OutputCollection[0].ErrorRowDisposition = DTSRowDisposition.RD_IgnoreFailure;
dataFlowTask.PathCollection.New().AttachPathAndPropagateNotifications(
parentComponent.OutputCollection[0],
lookupCom.InputCollection[0]
);
lookupComI.AcquireConnections(null);
lookupComI.ReinitializeMetaData();
lookupComI.ReleaseConnections();
}
public TermLookup(
VulcanPackage vulcanPackage,
MainPipe dataFlowTask,
IDTSComponentMetaData90 parentComponent,
string name,
string description,
Connection connection,
bool isCaseSensitive,
string refTermColumn,
string refTermTable
)
: base(vulcanPackage,
dataFlowTask,
parentComponent,
name,
description)
{
TermLookupCom = dataFlowTask.ComponentMetaDataCollection.New();
TermLookupCom.ComponentClassID = "DTSTransform.TermLookup";
TermLookupComI = TermLookupCom.Instantiate();
TermLookupComI.ProvideComponentProperties();
TermLookupCom.Name = name;
TermLookupCom.RuntimeConnectionCollection[0].ConnectionManagerID = connection.ConnectionManager.ID;
TermLookupCom.RuntimeConnectionCollection[0].ConnectionManager =
DTS.DtsConvert.ToConnectionManager90(connection.ConnectionManager);
TermLookupComI.SetComponentProperty("IsCaseSensitive", isCaseSensitive);
TermLookupComI.SetComponentProperty("RefTermColumn", refTermColumn);
TermLookupComI.SetComponentProperty("RefTermTable", refTermTable);
TermLookupCom.OutputCollection[0].ErrorRowDisposition = DTSRowDisposition.RD_IgnoreFailure;
dataFlowTask.PathCollection.New().AttachPathAndPropagateNotifications(
parentComponent.OutputCollection[0],
TermLookupCom.InputCollection[0]
);
TermLookupComI.AcquireConnections(null);
TermLookupComI.ReinitializeMetaData();
TermLookupComI.ReleaseConnections();
}
public SsisComponent(Transformation transformation, SSISEmitterContext context)
{
if (!context.HasSSISDataFlowTask)
{
// TODO: Message.Trace(Severity.Error)
}
_transformation = transformation;
_dataFlowTask = context.SSISDataFlowTask;
_context = context;
_component = _dataFlowTask.NewComponentMetaData();
_component.ComponentClassID = ClassID;
_instance = _component.Instantiate();
_instance.ProvideComponentProperties();
_component.Name = _transformation.Name;
_component.ValidateExternalMetadata = transformation.ValidateExternalMetadata;
}
public static void CreateSQLCEComponent(Microsoft.SqlServer.Dts.Runtime.Package package, MainPipe dataFlowTask, string sqlCEDatabaseName, string sqlCEPassword, String tableName, out ConnectionManager sqlCECM, out IDTSComponentMetaData100 sqlCETarget, out CManagedComponentWrapper sqlCEInstance)
{
// Add SQL CE Connection
sqlCECM = package.Connections.Add("SQLMOBILE");
sqlCECM.ConnectionString = connectionString(sqlCEDatabaseName, sqlCEPassword);
sqlCECM.Name = "SQLCE Destination " + tableName;
sqlCETarget = dataFlowTask.ComponentMetaDataCollection.New();
sqlCETarget.ComponentClassID = typeof(Microsoft.SqlServer.Dts.Pipeline.SqlCEDestinationAdapter).AssemblyQualifiedName;
sqlCEInstance = sqlCETarget.Instantiate();
sqlCEInstance.ProvideComponentProperties();
sqlCETarget.Name = "SQLCE Target " + tableName;
sqlCETarget.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(sqlCECM);
sqlCETarget.RuntimeConnectionCollection[0].ConnectionManagerID = sqlCECM.ID;
sqlCETarget.CustomPropertyCollection["Table Name"].Value = tableName;
sqlCEInstance.AcquireConnections(null);
sqlCEInstance.ReinitializeMetaData();
sqlCEInstance.ReleaseConnections();
}
public SsisComponent(Transformation transformation, SSISEmitterContext context)
{
if (!context.HasSSISDataFlowTask)
{
// TODO: Message.Trace(Severity.Error)
}
_transformation = transformation;
_dataFlowTask = context.SSISDataFlowTask;
_context = context;
_component = _dataFlowTask.NewComponentMetaData();
_component.ComponentClassID = ClassID;
_instance = _component.Instantiate();
_instance.ProvideComponentProperties();
_component.Name = _transformation.Name;
_component.ValidateExternalMetadata = transformation.ValidateExternalMetadata;
}
public Sort(
VulcanPackage vulcanPackage,
MainPipe dataFlowTask,
IDTSComponentMetaData90
parentComponent,
string name,
string description,
bool eliminateDuplicates,
int maximumThreads
)
:
base(
vulcanPackage,
dataFlowTask,
parentComponent,
name,
description
)
{
_sortOrder = 1;
_sortCom = dataFlowTask.ComponentMetaDataCollection.New();
_sortCom.ComponentClassID = "DTSTransform.sort";
_sortComI = _sortCom.Instantiate();
_sortComI.ProvideComponentProperties();
_sortCom.Name = name;
_sortComI.SetComponentProperty("EliminateDuplicates", eliminateDuplicates);
_sortComI.SetComponentProperty("MaximumThreads", maximumThreads);
_sortCom.OutputCollection[0].ErrorRowDisposition = DTSRowDisposition.RD_NotUsed;
dataFlowTask.PathCollection.New().AttachPathAndPropagateNotifications(
parentComponent.OutputCollection[0],
_sortCom.InputCollection[0]
);
_sortComI.AcquireConnections(null);
_sortComI.ReinitializeMetaData();
_sortComI.ReleaseConnections();
}
public IDTSComponentMetaData100 AddComp_UnionAll(string ComponentName)
{
// Create
IDTSComponentMetaData100 Comp = dmp.ComponentMetaDataCollection.New();
Comp.ComponentClassID = "Microsoft.UnionAll";
// Instantiate
CManagedComponentWrapper Inst = Comp.Instantiate();
Inst.ProvideComponentProperties();
Comp.Name = ComponentName;
Comp.Description = "DodanyUA";
// Return
return(Comp);
}
public IDTSComponentMetaData100 AddComp_AttunitySource(string componentName,
string conManName,
string source
)
{
// Create
IDTSComponentMetaData100 Comp = dmp.ComponentMetaDataCollection.New();
Comp.ComponentClassID = "Attunity.SSISOraSrc";
// Instantiate
CManagedComponentWrapper Inst = Comp.Instantiate();
Inst.ProvideComponentProperties();
Comp.Name = componentName;
Comp.Description = "Attunity Source Component";
Inst.SetComponentProperty("AccessMode", 0);
Inst.SetComponentProperty("TableName", source);
// GetConnectionManager
ConnectionManager cm = prj.ConnectionManagerItems[conManName + ".conmgr"].ConnectionManager;
//// Parametrize
Comp.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(cm);
Comp.RuntimeConnectionCollection[0].ConnectionManagerID = cm.ID;
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).
// Return
return(Comp);
}
public Lookup(
VulcanPackage vulcanPackage,
MainPipe dataFlowTask,
IDTSComponentMetaData90
parentComponent,
string name,
string description,
Connection connection,
string query
)
: base(vulcanPackage,
dataFlowTask,
parentComponent,
name,
description)
{
lookupCom = dataFlowTask.ComponentMetaDataCollection.New();
lookupCom.ComponentClassID = "DTSTransform.Lookup";
lookupComI = lookupCom.Instantiate();
lookupComI.ProvideComponentProperties();
lookupCom.Name = name;
lookupCom.RuntimeConnectionCollection[0].ConnectionManagerID = connection.ConnectionManager.ID;
lookupCom.RuntimeConnectionCollection[0].ConnectionManager =
DTS.DtsConvert.ToConnectionManager90(connection.ConnectionManager);
lookupComI.SetComponentProperty("SqlCommand", query);
lookupCom.OutputCollection[0].ErrorRowDisposition = DTSRowDisposition.RD_IgnoreFailure;
dataFlowTask.PathCollection.New().AttachPathAndPropagateNotifications(
parentComponent.OutputCollection[0],
lookupCom.InputCollection[0]
);
lookupComI.AcquireConnections(null);
lookupComI.ReinitializeMetaData();
lookupComI.ReleaseConnections();
}
public OLEDBDestination(
Packages.VulcanPackage vulcanPackage,
MainPipe dataFlowTask,
IDTSComponentMetaData90 parentComponent,
string name,
string description,
Connection connection,
string tableName
)
:
base(
vulcanPackage,
dataFlowTask,
parentComponent,
name,
description
)
{
//remove duplicates if you happen to enclose the table in brackets :)
_oledbDest = dataFlowTask.ComponentMetaDataCollection.New();
_oledbDest.ComponentClassID = "DTSAdapter.OleDbDestination.1";
_oledbDestI = _oledbDest.Instantiate();
_oledbDestI.ProvideComponentProperties();
_oledbDest.Name = Resources.OLEDBDestinationNamePrefix + (tableName.Replace("[", "").Replace("]", "").Replace("dbo", "").Replace(".", ""));
_oledbDest.ValidateExternalMetadata = true;
_oledbDest.RuntimeConnectionCollection[0].ConnectionManager =
DTS.DtsConvert.ToConnectionManager90(
connection.ConnectionManager
);
_oledbDest.RuntimeConnectionCollection[0].ConnectionManagerID = connection.ConnectionManager.ID;
dataFlowTask.PathCollection.New().AttachPathAndPropagateNotifications(
parentComponent.OutputCollection[0],
_oledbDest.InputCollection[0]
);
} // End Constructor
public ConditionalSplit(Packages.VulcanPackage vulcanPackage, MainPipe dataFlowTask, IDTSComponentMetaData90 parentComponent, string name, string description)
:
base(
vulcanPackage,
dataFlowTask,
parentComponent,
name,
description
)
{
_csCom = dataFlowTask.ComponentMetaDataCollection.New();
_csCom.ComponentClassID = "DTSTransform.ConditionalSplit";
//IMPORTANT! If you do not Instantiate() first, the component names do not get set... this is bad.
_csi = _csCom.Instantiate();
_csi.ProvideComponentProperties();
_csCom.Name = Name;
_csCom.Description = Description;
_csCom.ValidateExternalMetadata = true;
_csi.AcquireConnections(null);
_csi.ReinitializeMetaData();
_csi.ReleaseConnections();
dataFlowTask.PathCollection.New().AttachPathAndPropagateNotifications(
parentComponent.OutputCollection[0],
_csCom.InputCollection[0]
);
IDTSVirtualInput90 vi = _csCom.InputCollection[0].GetVirtualInput();
foreach (IDTSVirtualInputColumn90 vic in vi.VirtualInputColumnCollection)
{
this.SetInputUsageType(vi, vic, DTSUsageType.UT_READONLY);
}
}
public Sort(
VulcanPackage vulcanPackage,
MainPipe dataFlowTask,
IDTSComponentMetaData90
parentComponent,
string name,
string description,
bool eliminateDuplicates,
int maximumThreads
)
: base(vulcanPackage,
dataFlowTask,
parentComponent,
name,
description)
{
_sortOrder = 1;
_sortCom = dataFlowTask.ComponentMetaDataCollection.New();
_sortCom.ComponentClassID = "DTSTransform.sort";
_sortComI = _sortCom.Instantiate();
_sortComI.ProvideComponentProperties();
_sortCom.Name = name;
_sortComI.SetComponentProperty("EliminateDuplicates", eliminateDuplicates);
_sortComI.SetComponentProperty("MaximumThreads", maximumThreads);
_sortCom.OutputCollection[0].ErrorRowDisposition = DTSRowDisposition.RD_NotUsed;
dataFlowTask.PathCollection.New().AttachPathAndPropagateNotifications(
parentComponent.OutputCollection[0],
_sortCom.InputCollection[0]
);
_sortComI.AcquireConnections(null);
_sortComI.ReinitializeMetaData();
_sortComI.ReleaseConnections();
}
public void TestChangeRowsProcessedOutputSetColumnProperty()
{
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];
ManageProperties.AddMissingOutputColumnProperties(errorOutput.OutputColumnCollection[0].CustomPropertyCollection);
Boolean exceptionThrown = false;
try
{
instance.SetOutputColumnProperty(errorOutput.ID, errorOutput.OutputColumnCollection[0].ID, ManageProperties.dotNetFormatString, String.Empty);
}
catch (COMException ex)
{
Assert.AreEqual(MessageStrings.CantChangeOutputProperties("RowsProcessed"), ex.Message, "Exception Message Wrong");
exceptionThrown = true;
}
Assert.IsTrue(exceptionThrown, "Exception Not Thrown");
}
public OLEDBDestination(
Packages.VulcanPackage vulcanPackage,
MainPipe dataFlowTask,
IDTSComponentMetaData90 parentComponent,
string name,
string description,
Connection connection,
string tableName
)
: base(vulcanPackage,
dataFlowTask,
parentComponent,
name,
description)
{
//remove duplicates if you happen to enclose the table in brackets :)
_oledbDest = dataFlowTask.ComponentMetaDataCollection.New();
_oledbDest.ComponentClassID = "DTSAdapter.OleDbDestination.1";
_oledbDestI = _oledbDest.Instantiate();
_oledbDestI.ProvideComponentProperties();
_oledbDest.Name = Resources.OLEDBDestinationNamePrefix + (tableName.Replace("[", "").Replace("]", "").Replace("dbo", "").Replace(".",""));
_oledbDest.ValidateExternalMetadata = true;
_oledbDest.RuntimeConnectionCollection[0].ConnectionManager =
DTS.DtsConvert.ToConnectionManager90(
connection.ConnectionManager
);
_oledbDest.RuntimeConnectionCollection[0].ConnectionManagerID = connection.ConnectionManager.ID;
dataFlowTask.PathCollection.New().AttachPathAndPropagateNotifications(
parentComponent.OutputCollection[0],
_oledbDest.InputCollection[0]
);
}
public UnionAll(
VulcanPackage vulcanPackage,
MainPipe dataFlowTask,
IDTSComponentMetaData90
parentComponent,
string name,
string description
)
: base(vulcanPackage,
dataFlowTask,
parentComponent,
name,
description)
{
unionAllCom = dataFlowTask.ComponentMetaDataCollection.New();
unionAllCom.ComponentClassID = "DTSTransform.UnionAll";
unionAllComI = unionAllCom.Instantiate();
unionAllComI.ProvideComponentProperties();
unionAllCom.Name = name;
unionAllCom.OutputCollection[0].ErrorRowDisposition = DTSRowDisposition.RD_NotUsed;
}
private void ScanIntegrationServicesExecutableForPropertiesWithNonDefaultValue(DtsObject o, string FriendlyPath)
{
if (o == null)
{
return;
}
if (packageDefault == null)
{
packageDefault = new Package();
}
DtsObject defaultObject;
if (o is Package)
{
defaultObject = packageDefault;
}
else if (o is IDTSName)
{
if (dictCachedDtsObjects.ContainsKey(((IDTSName)o).CreationName))
{
defaultObject = dictCachedDtsObjects[((IDTSName)o).CreationName];
}
else if (o is DtsEventHandler)
{
defaultObject = (DtsObject)packageDefault.EventHandlers.Add(((IDTSName)o).CreationName);
dictCachedDtsObjects.Add(((IDTSName)o).CreationName, defaultObject);
}
else if (o is ConnectionManager)
{
defaultObject = (DtsObject)packageDefault.Connections.Add(((IDTSName)o).CreationName);
dictCachedDtsObjects.Add(((IDTSName)o).CreationName, defaultObject);
}
else
{
defaultObject = packageDefault.Executables.Add(((IDTSName)o).CreationName);
dictCachedDtsObjects.Add(((IDTSName)o).CreationName, defaultObject);
}
}
else
{
throw new Exception("Object " + o.GetType().FullName + " does not implement IDTSName.");
}
PropertyInfo[] properties = o.GetType().GetProperties(BindingFlags.Public | BindingFlags.FlattenHierarchy | BindingFlags.Instance);
foreach (PropertyInfo prop in properties)
{
if (!prop.CanWrite || !prop.CanRead)
{
continue;
}
//SSIS objects don't have a DefaultValueAttribute, which is wy we have to create a new control flow object (defaultObject) above and compare properties from this object to that object
object[] attrs = prop.GetCustomAttributes(typeof(System.ComponentModel.BrowsableAttribute), true);
System.ComponentModel.BrowsableAttribute browsableAttr = (System.ComponentModel.BrowsableAttribute)(attrs.Length > 0 ? attrs[0] : null);
if (browsableAttr != null && !browsableAttr.Browsable)
{
continue; //don't show attributes marked not browsable
}
attrs = prop.GetCustomAttributes(typeof(System.ComponentModel.ReadOnlyAttribute), true);
System.ComponentModel.ReadOnlyAttribute readOnlyAttr = (System.ComponentModel.ReadOnlyAttribute)(attrs.Length > 0 ? attrs[0] : null);
if (readOnlyAttr != null && readOnlyAttr.IsReadOnly)
{
continue; //don't show attributes marked read only
}
if (prop.PropertyType.Namespace != "System" && !prop.PropertyType.IsPrimitive && !prop.PropertyType.IsValueType && !prop.PropertyType.IsEnum)
{
continue;
}
if (prop.PropertyType == typeof(DateTime))
{
continue;
}
if (prop.PropertyType == typeof(string))
{
continue;
}
if (prop.Name == "VersionBuild")
{
continue;
}
if (prop.Name == "VersionMajor")
{
continue;
}
if (prop.Name == "VersionMinor")
{
continue;
}
if (prop.Name == "PackageType")
{
continue;
}
object value = prop.GetValue(o, null);
object defaultValue = prop.GetValue(defaultObject, null);
if (defaultValue != null && !defaultValue.Equals(value))
{
string sValue = (value == null ? string.Empty : value.ToString());
this.listNonDefaultProperties.Add(new NonDefaultProperty(this.DatabaseName, FriendlyPath, prop.Name, defaultValue.ToString(), sValue));
}
}
if (o is IDTSObjectHost)
{
IDTSObjectHost host = (IDTSObjectHost)o;
if (host.InnerObject is Microsoft.SqlServer.Dts.Pipeline.Wrapper.MainPipe)
{
properties = typeof(Microsoft.SqlServer.Dts.Pipeline.Wrapper.MainPipe).GetProperties(BindingFlags.Public | BindingFlags.FlattenHierarchy | BindingFlags.Instance);
}
else if (host.InnerObject is IDTSConnectionManagerDatabaseParametersXX)
{
properties = typeof(IDTSConnectionManagerDatabaseParametersXX).GetProperties(BindingFlags.Public | BindingFlags.FlattenHierarchy | BindingFlags.Instance);
}
else //probably won't turn up any properties because reflection on a COM object Type doesn't work
{
properties = host.InnerObject.GetType().GetProperties(BindingFlags.Public | BindingFlags.FlattenHierarchy | BindingFlags.Instance);
}
foreach (PropertyInfo prop in properties)
{
if (!prop.CanWrite || !prop.CanRead)
{
continue;
}
object[] attrs = prop.GetCustomAttributes(typeof(System.ComponentModel.BrowsableAttribute), true);
System.ComponentModel.BrowsableAttribute browsableAttr = (System.ComponentModel.BrowsableAttribute)(attrs.Length > 0 ? attrs[0] : null);
if (browsableAttr != null && !browsableAttr.Browsable)
{
continue; //don't show attributes marked not browsable
}
attrs = prop.GetCustomAttributes(typeof(System.ComponentModel.ReadOnlyAttribute), true);
System.ComponentModel.ReadOnlyAttribute readOnlyAttr = (System.ComponentModel.ReadOnlyAttribute)(attrs.Length > 0 ? attrs[0] : null);
if (readOnlyAttr != null && readOnlyAttr.IsReadOnly)
{
continue; //don't show attributes marked read only
}
if (prop.PropertyType.Namespace != "System" && !prop.PropertyType.IsPrimitive && !prop.PropertyType.IsValueType && !prop.PropertyType.IsEnum)
{
continue;
}
if (prop.PropertyType == typeof(DateTime))
{
continue;
}
if (prop.PropertyType == typeof(string))
{
continue;
}
if (prop.Name == "VersionBuild")
{
continue;
}
if (prop.Name == "VersionMajor")
{
continue;
}
if (prop.Name == "VersionMinor")
{
continue;
}
if (prop.Name == "PackageType")
{
continue;
}
if (prop.Name.StartsWith("IDTS"))
{
continue;
}
object value;
object defaultValue;
if (host.InnerObject is Microsoft.SqlServer.Dts.Pipeline.Wrapper.MainPipe)
{
try
{
value = host.InnerObject.GetType().InvokeMember(prop.Name, System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.GetProperty | System.Reflection.BindingFlags.FlattenHierarchy | System.Reflection.BindingFlags.Instance, null, host.InnerObject, null);
}
catch
{
continue;
}
try
{
defaultValue = ((IDTSObjectHost)defaultObject).InnerObject.GetType().InvokeMember(prop.Name, System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.GetProperty | System.Reflection.BindingFlags.FlattenHierarchy | System.Reflection.BindingFlags.Instance, null, ((IDTSObjectHost)defaultObject).InnerObject, null);
}
catch
{
defaultValue = null;
}
}
else
{
value = prop.GetValue(host.InnerObject, null);
defaultValue = prop.GetValue(((IDTSObjectHost)defaultObject).InnerObject, null);
}
if (defaultValue != null && !defaultValue.Equals(value))
{
string sValue = (value == null ? string.Empty : value.ToString());
this.listNonDefaultProperties.Add(new NonDefaultProperty(this.DatabaseName, FriendlyPath, prop.Name, defaultValue.ToString(), sValue));
}
}
//scan data flow transforms
if (host.InnerObject is Microsoft.SqlServer.Dts.Pipeline.Wrapper.MainPipe)
{
Microsoft.SqlServer.Dts.Pipeline.Wrapper.MainPipe pipe = (Microsoft.SqlServer.Dts.Pipeline.Wrapper.MainPipe)host.InnerObject;
Microsoft.SqlServer.Dts.Pipeline.Wrapper.MainPipe defaultPipe = (Microsoft.SqlServer.Dts.Pipeline.Wrapper.MainPipe)((IDTSObjectHost)defaultObject).InnerObject;
foreach (IDTSComponentMetaDataXX transform in pipe.ComponentMetaDataCollection)
{
IDTSComponentMetaDataXX defaultTransform = defaultPipe.ComponentMetaDataCollection.New();
defaultTransform.ComponentClassID = transform.ComponentClassID;
CManagedComponentWrapper defaultInst = defaultTransform.Instantiate();
try
{
defaultInst.ProvideComponentProperties();
}
catch
{
continue; //if there's a corrupt package (or if you don't have the component installed on your laptop?) then this might fail... so just move on
}
if (!transform.ValidateExternalMetadata) //this property isn't in the CustomPropertyCollection, so we have to check it manually
{
this.listNonDefaultProperties.Add(new NonDefaultProperty(this.DatabaseName, FriendlyPath + "\\" + transform.Name, "ValidateExternalMetadata", "True", "False"));
}
foreach (IDTSOutputXX output in transform.OutputCollection) //check for error row dispositions
{
if (output.ErrorRowDisposition == DTSRowDisposition.RD_IgnoreFailure)
{
this.listNonDefaultProperties.Add(new NonDefaultProperty(this.DatabaseName, FriendlyPath + "\\" + transform.Name + "\\" + output.Name, "ErrorRowDisposition", "FailComponent", "IgnoreFailure"));
}
if (output.TruncationRowDisposition == DTSRowDisposition.RD_IgnoreFailure)
{
this.listNonDefaultProperties.Add(new NonDefaultProperty(this.DatabaseName, FriendlyPath + "\\" + transform.Name + "\\" + output.Name, "TruncationRowDisposition", "FailComponent", "IgnoreFailure"));
}
}
Microsoft.DataTransformationServices.Design.PipelinePropertiesWrapper propWrapper = new Microsoft.DataTransformationServices.Design.PipelinePropertiesWrapper(transform, transform, 0);
foreach (IDTSCustomPropertyXX prop in transform.CustomPropertyCollection)
{
System.ComponentModel.PropertyDescriptor propDesc = (System.ComponentModel.PropertyDescriptor)propWrapper.GetType().InvokeMember("CreateCustomPropertyPropertyDescriptor", BindingFlags.DeclaredOnly | BindingFlags.NonPublic | BindingFlags.InvokeMethod | BindingFlags.Instance, null, propWrapper, new object[] { prop });
if (propDesc == null)
{
continue;
}
if (propDesc.IsReadOnly)
{
continue;
}
if (!propDesc.IsBrowsable)
{
continue;
}
if (prop.Value is string)
{
continue;
}
if (prop.Value is DateTime)
{
continue;
}
IDTSCustomPropertyXX defaultProp;
try
{
defaultProp = defaultTransform.CustomPropertyCollection[prop.Name];
}
catch
{
if (prop.Name == "PreCompile" && bool.Equals(prop.Value, false)) //this property doesn't show up in the new script component we created to determine defaults, so we have to check it manually
{
this.listNonDefaultProperties.Add(new NonDefaultProperty(this.DatabaseName, FriendlyPath + "\\" + transform.Name, prop.Name, "True", "False"));
}
continue;
}
System.ComponentModel.ITypeDescriptorContext context = new PipelinePropertyContext(transform, propDesc);
string sValue = propDesc.Converter.ConvertToString(context, prop.Value); //gets nice text descriptions for enums on component properties
string sDefaultValue = propDesc.Converter.ConvertToString(context, defaultProp.Value); //gets nice text descriptions for enums on component properties
if (sValue == sDefaultValue)
{
continue;
}
this.listNonDefaultProperties.Add(new NonDefaultProperty(this.DatabaseName, FriendlyPath + "\\" + transform.Name, prop.Name, sDefaultValue, sValue));
}
defaultPipe.ComponentMetaDataCollection.RemoveObjectByID(defaultTransform.ID);
}
}
}
}
public void TestProvideComponentProperties()
{
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 speechToText = dataFlowTask.ComponentMetaDataCollection.New();
speechToText.ComponentClassID = typeof(Martin.SQLServer.Dts.SSISSpeechToText).AssemblyQualifiedName;
CManagedComponentWrapper speechToTextInstance = speechToText.Instantiate();
speechToTextInstance.ProvideComponentProperties();
int intExpected = 7; // Add 1 to the number that the component added, as there is a custom MS one as well.
int intActual = speechToText.CustomPropertyCollection.Count;
Assert.AreEqual(intExpected, intActual, "Custom Property Collection Count is wrong");
IDTSCustomProperty100 cpActual = speechToText.CustomPropertyCollection[Utility.SubscriptionKeyPropName];
Assert.AreEqual(Utility.SubscriptionKeyPropName, cpActual.Name, "Property name is wrong");
Assert.AreEqual(DTSCustomPropertyExpressionType.CPET_NOTIFY, cpActual.ExpressionType, "Expression type on SubscriptionKeyPropName is wrong");
cpActual = speechToText.CustomPropertyCollection[Utility.OperationModePropName];
Assert.AreEqual(Utility.OperationModePropName, cpActual.Name, "Property name is wrong");
Assert.AreEqual(DTSCustomPropertyExpressionType.CPET_NONE, cpActual.ExpressionType, "Expression type on OperationModePropName is wrong");
Assert.AreEqual(typeof(SSISSpeechToText.OperationModeEnum).AssemblyQualifiedName, cpActual.TypeConverter, "Type Converter on OperationModePropName is wrong");
cpActual = speechToText.CustomPropertyCollection[Utility.LanguagePropName];
Assert.AreEqual(cpActual.Name, Utility.LanguagePropName, "Property name is wrong");
Assert.AreEqual(cpActual.ExpressionType, DTSCustomPropertyExpressionType.CPET_NONE, "Expression type on LanguagePropName is wrong");
Assert.AreEqual(cpActual.TypeConverter, typeof(SSISSpeechToText.SpeechLanguageEnum).AssemblyQualifiedName, "Type Converter on LanguagePropName is wrong");
cpActual = speechToText.CustomPropertyCollection[Utility.ChannelSeparationPropName];
Assert.AreEqual(cpActual.Name, Utility.ChannelSeparationPropName, "Property name is wrong");
Assert.AreEqual(cpActual.ExpressionType, DTSCustomPropertyExpressionType.CPET_NONE, "Expression type on ChannelSeparationPropName is wrong");
Assert.AreEqual(cpActual.TypeConverter, typeof(SSISSpeechToText.ChannelSeparationEnum).AssemblyQualifiedName, "Type Converter on ChannelSeparationPropName is wrong");
cpActual = speechToText.CustomPropertyCollection[Utility.ShortPhraseUrlPropName];
Assert.AreEqual(Utility.ShortPhraseUrlPropName, cpActual.Name, "Property name is wrong");
Assert.AreEqual(DTSCustomPropertyExpressionType.CPET_NOTIFY, cpActual.ExpressionType, "Expression type on ShortPhraseUrlPropName is wrong");
Assert.AreEqual(string.Empty, cpActual.TypeConverter, "Type Converter on AuthenticationUriPropName is wrong");
cpActual = speechToText.CustomPropertyCollection[Utility.LongPhraseUrlPropName];
Assert.AreEqual(Utility.LongPhraseUrlPropName, cpActual.Name, "Property name is wrong");
Assert.AreEqual(DTSCustomPropertyExpressionType.CPET_NOTIFY, cpActual.ExpressionType, "Expression type on LongPhraseUrlPropName is wrong");
Assert.AreEqual(string.Empty, cpActual.TypeConverter, "Type Converter on AuthenticationUriPropName is wrong");
intExpected = 1;
intActual = speechToText.InputCollection.Count;
Assert.AreEqual(intExpected, intActual, "Input Collection Count is wrong");
Assert.AreEqual(speechToText.InputCollection[0].Name, "Input", "Input Name is Wrong");
Assert.AreEqual(speechToText.InputCollection[0].InputColumnCollection.Count, 0, "There are input columns");
intExpected = 1;
intActual = speechToText.OutputCollection.Count;
Assert.AreEqual(intExpected, intActual, "Output Collection Count is wrong");
Assert.AreEqual(speechToText.OutputCollection[0].Name, "SpeechOutput", "Output Name is Wrong");
Assert.AreEqual(speechToText.OutputCollection[0].OutputColumnCollection.Count, 3, "The number of output columns is wrong");
Assert.AreEqual(speechToText.OutputCollection[0].OutputColumnCollection[Utility.OutputChannelColumnName].Name, Utility.OutputChannelColumnName, "Output column name is wrong");
Assert.AreEqual(speechToText.OutputCollection[0].OutputColumnCollection[Utility.OutputChannelColumnName].DataType, DataType.DT_STR, "Output column OutputSpeechColumnName datatype is wrong");
Assert.AreEqual((SSISSpeechToText.OutputTypeEnum)speechToText.OutputCollection[0].OutputColumnCollection[Utility.OutputChannelColumnName].CustomPropertyCollection[Utility.OutputColumnOutputTypePropName].Value, SSISSpeechToText.OutputTypeEnum.Channel, "Output column OutputChannelColumnName purpose is wrong");
Assert.AreEqual(speechToText.OutputCollection[0].OutputColumnCollection[Utility.OutputSpeechColumnName].Name, Utility.OutputSpeechColumnName, "Output column name is wrong");
Assert.AreEqual(speechToText.OutputCollection[0].OutputColumnCollection[Utility.OutputSpeechColumnName].DataType, DataType.DT_TEXT, "Output column OutputSpeechColumnName datatype is wrong");
Assert.AreEqual((SSISSpeechToText.OutputTypeEnum)speechToText.OutputCollection[0].OutputColumnCollection[Utility.OutputSpeechColumnName].CustomPropertyCollection[Utility.OutputColumnOutputTypePropName].Value, SSISSpeechToText.OutputTypeEnum.Speech, "Output column OutputSpeechColumnName purpose is wrong");
Assert.AreEqual(speechToText.OutputCollection[0].OutputColumnCollection[Utility.OutputTimecodeColumnName].Name, Utility.OutputTimecodeColumnName, "Output column name is wrong");
Assert.AreEqual(speechToText.OutputCollection[0].OutputColumnCollection[Utility.OutputTimecodeColumnName].DataType, DataType.DT_DBTIME2, "Output column OutputTimecodeColumnName datatype is wrong");
Assert.AreEqual((SSISSpeechToText.OutputTypeEnum)speechToText.OutputCollection[0].OutputColumnCollection[Utility.OutputTimecodeColumnName].CustomPropertyCollection[Utility.OutputColumnOutputTypePropName].Value, SSISSpeechToText.OutputTypeEnum.Timecode, "Output column OutputTimecodeColumnName purpose is wrong");
}
public IDTSComponentMetaData100 AddComp_OleDBSource(string componentName,
string conManName,
string source,
int accessMode = 0
)
{
// Create
IDTSComponentMetaData100 Comp = dmp.ComponentMetaDataCollection.New();
Comp.ComponentClassID = "Microsoft.OLEDBSource";
// Instantiate
CManagedComponentWrapper Inst = Comp.Instantiate();
Inst.ProvideComponentProperties();
Comp.Name = componentName;
Comp.Description = "bloczek OLEDBSource";
// GetConnectionManager
ConnectionManager cm = prj.ConnectionManagerItems[conManName + ".conmgr"].ConnectionManager;
// Parametrize
Comp.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(cm);
Comp.RuntimeConnectionCollection[0].ConnectionManagerID = cm.ID;
// AccessMode: 0, OpenRowset: [dbo].[DimAccount]
// AccessMode: 1, OpenRowsetVariable: User::VarTName
// AccessMode: 2, SqlCommand: SELECT* FROM DimAccount
// AccessMode: 3, SqlCommandVariable: User::VarSQLcmd
Inst.SetComponentProperty("AccessMode", accessMode);
switch (accessMode)
{
case 0:
Inst.SetComponentProperty("OpenRowset", source);
break;
case 1:
Inst.SetComponentProperty("OpenRowsetVariable", source);
break;
case 2:
Inst.SetComponentProperty("SqlCommand", source);
break;
case 3:
Inst.SetComponentProperty("SqlCommandVariable", source);
break;
}
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).
// Return
return(Comp);
}
// Creates Destination Component (Input Collection)
internal void CreateDestinationReaderComponent()
{
// 1. DataReader String: the class name of the DataReader destination.
// 2. FailOnTimeout Boolean:Indicates wheather to fail when a ReadTimeout occurs. The default value is False.
// 3. ReadeTimeout Integer: The number of milliseconds before a timeout occurs. The default value of this property is 30000 (30 seconds).
// Adds a component from MainPipe to the Destination Recordset Metadata
_objIDTSDSTReaderMetaData = _objMainPipe.ComponentMetaDataCollection.New();
// Sets the Destination recordset component name
_objIDTSDSTReaderMetaData.Name = "Test";
// Sets the Destination recordset component class id
_objIDTSDSTReaderMetaData.ComponentClassID = _strDestinationDFReaderComponentID;
IDTSCustomProperty100 _property = _objIDTSDSTReaderMetaData.CustomPropertyCollection.New();
_property.Name = "DataReader";
_property.Value = new object();
_property = _objIDTSDSTReaderMetaData.CustomPropertyCollection.New();
_property.Name = "FailOnTimeout";
_property.Value = false;
_property = _objIDTSDSTReaderMetaData.CustomPropertyCollection.New();
_property.Name = "ReadTimeout";
_property.Value = 30000;
// Instantiates the Wrapper adding Destination Recordset Metadata
_objDestinationReaderWrapper = _objIDTSDSTReaderMetaData.Instantiate();
// Provides default properties
_objDestinationReaderWrapper.ProvideComponentProperties();
// Sets the connection
_objDestinationReaderWrapper.AcquireConnections(null);
// Reinitializes the Destination Metadata
_objDestinationReaderWrapper.ReinitializeMetaData();
// Releases the Wrapper connection
_objDestinationReaderWrapper.ReleaseConnections();
// Creates the IDTSPath from the MainPipe
_objIDTSPath = _objMainPipe.PathCollection.New();
_objIDTSPath.AttachPathAndPropagateNotifications(_objIDTSSRCMetaData.OutputCollection[0], _objIDTSDSTReaderMetaData.InputCollection[0]);
_objIDTSInput = _objIDTSDSTReaderMetaData.InputCollection[0];
//Gets the Virtual Input Column Collection from the Destination Input Collection
_objIDTSVirtualInput = _objIDTSInput.GetVirtualInput();
_iDestInputID = Convert.ToInt32(_objIDTSInput.ID);
// Splits the RAW Column Names into an array of strings
if (strRAWColNames != null && strRAWColNames.Equals(string.Empty) == false && strRAWColNames != "")
{
if (strRAWColNames.EndsWith(","))
{
_iIndex = strRAWColNames.LastIndexOf(_delim);
_strIndex = strRAWColNames.Remove(_iIndex);
}
_strFilterArray = _strIndex.Split(_delim);
}
// Sets Usagetype According to FilterArray
foreach (IDTSVirtualInputColumn100 objIDTSVirtualInputColumn in _objIDTSVirtualInput.VirtualInputColumnCollection)
{
if (_strFilterArray == null)
{
// When FilterArray string is null
_objDestinationReaderWrapper.SetUsageType(_iDestInputID, _objIDTSVirtualInput, objIDTSVirtualInputColumn.LineageID, DTSUsageType.UT_READONLY);
}
else
{
if (FilterField(objIDTSVirtualInputColumn.Name, _strFilterArray) == false)
{
// When FilterArray string is not null
_objDestinationReaderWrapper.SetUsageType(_iDestInputID, _objIDTSVirtualInput, objIDTSVirtualInputColumn.LineageID, DTSUsageType.UT_READONLY);
}
}
}
// Sets the connection
_objDestinationReaderWrapper.AcquireConnections(null);
// Reinitializes the Destination Metadata
_objDestinationReaderWrapper.ReinitializeMetaData();
// Releases the Wrapper connection
_objDestinationReaderWrapper.ReleaseConnections();
}
// 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 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 override void Emit(XPathNavigator patternNavigator)
{
// Reloads invalidate the ParentContainer, so we should do it much later.
string etlName =
patternNavigator.SelectSingleNode("@Name", VulcanPackage.VulcanConfig.NamespaceManager).Value;
Message.Trace(Severity.Notification, "{0}", etlName);
bool delayValidation = patternNavigator.SelectSingleNode("@DelayValidation").ValueAsBoolean;
string sourceName = patternNavigator.SelectSingleNode("rc:SourceConnection/@Name", VulcanPackage.VulcanConfig.NamespaceManager).Value;
Connection sourceConnection =
Connection.GetExistingConnection(
VulcanPackage,
patternNavigator.SelectSingleNode("rc:SourceConnection/@Name", VulcanPackage.VulcanConfig.NamespaceManager).Value
);
if (sourceConnection != null)
{
string query = patternNavigator.SelectSingleNode("rc:Query", VulcanPackage.VulcanConfig.NamespaceManager).Value.Trim();
// Add the Data Flow Task to the Package.
DTS.TaskHost pipeHost = (DTS.TaskHost)ParentContainer.Executables.Add("STOCK:PipelineTask");
pipeHost.Properties["DelayValidation"].SetValue(pipeHost, delayValidation);
MainPipe dataFlowTask = (MainPipe)pipeHost.InnerObject;
pipeHost.Name = etlName;
// Add the Source (this is temporary and will be replaced with a new style of Source element)
IDTSComponentMetaData90 sourceDataComponent = dataFlowTask.ComponentMetaDataCollection.New();
sourceDataComponent.ComponentClassID = "DTSAdapter.OleDbSource.1";
// IMPORTANT! If you do not Instantiate() and ProvideComponentProperties first,
// the component names do not get set... this is bad.
CManagedComponentWrapper oleInstance = sourceDataComponent.Instantiate();
oleInstance.ProvideComponentProperties();
sourceDataComponent.Name = etlName + " Source";
if (sourceDataComponent.RuntimeConnectionCollection.Count > 0)
{
sourceDataComponent.RuntimeConnectionCollection[0].ConnectionManager =
DTS.DtsConvert.ToConnectionManager90(
sourceConnection.ConnectionManager
);
sourceDataComponent.RuntimeConnectionCollection[0].ConnectionManagerID =
sourceConnection.ConnectionManager.ID;
}
oleInstance.SetComponentProperty("AccessMode", 2);
oleInstance.SetComponentProperty("SqlCommand", query);
try
{
oleInstance.AcquireConnections(null);
oleInstance.ReinitializeMetaData();
oleInstance.ReleaseConnections();
}
catch (System.Runtime.InteropServices.COMException ce)
{
Message.Trace(Severity.Error, ce, "OLEDBSource:{0}: {1}: Source {2}: Query {3}", sourceDataComponent.GetErrorDescription(ce.ErrorCode), ce.Message, sourceConnection.ConnectionManager.Name, query);
}
catch (Exception e)
{
Message.Trace(Severity.Error, e, "OLEDBSource:{0}: Source {1}: Query {2}", e.Message, sourceConnection.ConnectionManager.Name, query);
}
//Map parameter variables:
StringBuilder parameterBuilder = new StringBuilder();
foreach (XPathNavigator paramNav in patternNavigator.Select("rc:Parameter", VulcanPackage.VulcanConfig.NamespaceManager))
{
string name = paramNav.SelectSingleNode("@Name").Value;
string varName = paramNav.SelectSingleNode("@VariableName").Value;
if (VulcanPackage.DTSPackage.Variables.Contains(varName))
{
DTS.Variable variable = VulcanPackage.DTSPackage.Variables[varName];
parameterBuilder.AppendFormat("\"{0}\",{1};", name, variable.ID);
}
else
{
Message.Trace(Severity.Error, "DTS Variable {0} does not exist", varName);
}
}
oleInstance.SetComponentProperty("ParameterMapping", parameterBuilder.ToString());
///Transformation Factory
IDTSComponentMetaData90 parentComponent = sourceDataComponent;
XPathNavigator transNav = patternNavigator.SelectSingleNode("rc:Transformations", VulcanPackage.VulcanConfig.NamespaceManager);
if (transNav != null)
{
foreach (XPathNavigator nav in transNav.SelectChildren(XPathNodeType.Element))
{
// this is naughty but can be fixed later :)
Transformation t = TransformationFactory.ProcessTransformation(VulcanPackage, parentComponent, dataFlowTask, nav);
if (t != null)
{
parentComponent = t.Component;
}
}
}
XPathNavigator destNav = patternNavigator.SelectSingleNode("rc:Destination", VulcanPackage.VulcanConfig.NamespaceManager);
if (destNav != null)
{
string name = destNav.SelectSingleNode("@Name").Value;
Connection destConnection = Connection.GetExistingConnection(VulcanPackage, destNav.SelectSingleNode("@ConnectionName").Value);
string tableName = String.Format(System.Globalization.CultureInfo.InvariantCulture, "{0}", destNav.SelectSingleNode("@Table").Value.Trim());
OLEDBDestination oledbDestination = new OLEDBDestination(VulcanPackage, dataFlowTask, parentComponent, name, name, destConnection, tableName);
string accessMode = destNav.SelectSingleNode("@AccessMode").Value;
bool tableLock = destNav.SelectSingleNode("@TableLock").ValueAsBoolean;
bool checkConstraints = destNav.SelectSingleNode("@CheckConstraints").ValueAsBoolean;
bool keepIdentity = destNav.SelectSingleNode("@KeepIdentity").ValueAsBoolean;
bool keepNulls = destNav.SelectSingleNode("@KeepNulls").ValueAsBoolean;
int rowsPerBatch = destNav.SelectSingleNode("@RowsPerBatch").ValueAsInt;
int maxInsertCommitSize = destNav.SelectSingleNode("@MaximumInsertCommitSize").ValueAsInt;
switch (accessMode.ToUpperInvariant())
{
case "TABLE":
oledbDestination.ComponentInstance.SetComponentProperty("AccessMode", 0);
oledbDestination.ComponentInstance.SetComponentProperty("OpenRowset", tableName);
break;
case "TABLEFASTLOAD":
oledbDestination.ComponentInstance.SetComponentProperty("AccessMode", 3);
oledbDestination.ComponentInstance.SetComponentProperty("OpenRowset", tableName);
oledbDestination.ComponentInstance.SetComponentProperty("FastLoadKeepIdentity", keepIdentity);
oledbDestination.ComponentInstance.SetComponentProperty("FastLoadKeepNulls", keepNulls);
oledbDestination.ComponentInstance.SetComponentProperty("FastLoadMaxInsertCommitSize", maxInsertCommitSize);
StringBuilder fastLoadOptions = new StringBuilder();
if (tableLock)
{
fastLoadOptions.AppendFormat("TABLOCK,");
}
if (checkConstraints)
{
fastLoadOptions.AppendFormat("CHECK_CONSTRAINTS,");
}
if (rowsPerBatch > 0)
{
fastLoadOptions.AppendFormat("ROWS_PER_BATCH = {0}", rowsPerBatch);
}
fastLoadOptions = fastLoadOptions.Replace(",", "", fastLoadOptions.Length - 5, 5);
oledbDestination.ComponentInstance.SetComponentProperty("FastLoadOptions", fastLoadOptions.ToString());
break;
default:
Message.Trace(Severity.Error, "Unknown Destination Load Type of {0}", accessMode);
break;
}
try
{
oledbDestination.InitializeAndMapDestination();
}
catch (Exception)
{
}
// Map any overrides
foreach (XPathNavigator nav in destNav.Select("rc:Map", VulcanPackage.VulcanConfig.NamespaceManager))
{
string source = nav.SelectSingleNode("@Source").Value;
string destination;
bool unMap = false;
if (nav.SelectSingleNode("@Destination") == null)
{
unMap = true;
destination = source;
}
else
{
destination = nav.SelectSingleNode("@Destination").Value;
}
oledbDestination.Map(source, destination, unMap);
}
} // end DestNav != null
this.FirstExecutableGeneratedByPattern = pipeHost;
this.LastExecutableGeneratedByPattern = pipeHost;
} //END sourceConnection != null
else
{
Message.Trace(Severity.Error, "Source Connection {0} does not exist in {1}", sourceName, etlName);
}
} //END function Emit
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_MergeJoin(string ComponentName,
IDTSOutput100 outColsL,
IDTSOutput100 outColsR,
int joinType = 2,
int keyColNum = -1
)
{
// Create
IDTSComponentMetaData100 Comp = dmp.ComponentMetaDataCollection.New();
Comp.ComponentClassID = "Microsoft.MergeJoin";
Comp.ValidateExternalMetadata = true;
// Instantiate
CManagedComponentWrapper Inst = Comp.Instantiate();
Inst.ProvideComponentProperties();
Comp.Name = ComponentName;
Comp.Description = "MJ!";
// Parametrize #1
// 0: FULL
// 1: LEFT
// 2: INNER
Comp.CustomPropertyCollection["JoinType"].Value = joinType;
Comp.CustomPropertyCollection["TreatNullsAsEqual"].Value = true;
IDTSOutput100 outt = Comp.OutputCollection[0];
// Create Paths
IDTSPath100 pthLeft = dmp.PathCollection.New();
pthLeft.AttachPathAndPropagateNotifications(outColsL, Comp.InputCollection[0]);
IDTSPath100 pthRight = dmp.PathCollection.New();
pthRight.AttachPathAndPropagateNotifications(outColsR, Comp.InputCollection[1]);
// sprawdzić czy portrzebne i zobaczyć co robi
Comp.InputCollection[0].ExternalMetadataColumnCollection.IsUsed = false;
Comp.InputCollection[0].HasSideEffects = false;
Comp.InputCollection[1].ExternalMetadataColumnCollection.IsUsed = false;
Comp.InputCollection[1].HasSideEffects = false;
// Add Columns
// --------------------------------------------------------------------
int maxSortKeyVal = 0;
IDTSInput100 mInputL = Comp.InputCollection[0];
IDTSVirtualInput100 vMInputL = mInputL.GetVirtualInput();
foreach (IDTSVirtualInputColumn100 vCol in vMInputL.VirtualInputColumnCollection)
{
Inst.SetUsageType(mInputL.ID, vMInputL, vCol.LineageID, DTSUsageType.UT_READONLY);
if (vCol.SortKeyPosition > maxSortKeyVal)
{
maxSortKeyVal = vCol.SortKeyPosition;
}
}
IDTSInput100 mInputR = Comp.InputCollection[1];
IDTSVirtualInput100 vMInputR = mInputR.GetVirtualInput();
foreach (IDTSVirtualInputColumn100 vCol in vMInputR.VirtualInputColumnCollection)
{
Inst.SetUsageType(mInputR.ID, vMInputR, vCol.LineageID, DTSUsageType.UT_READONLY);
}
Comp.CustomPropertyCollection["NumKeyColumns"].Value = keyColNum == -1 ? maxSortKeyVal : keyColNum;
// Column Names
// to możnaby podrasować:
// kolumny JOINa tylko raz z nazwą z pierwszego
// kolumny pozostałe suffiksować tylko jak są dwie takie same po lewej i prawej
// --------------------------------------------------------------------
foreach (IDTSOutputColumn100 oc in outt.OutputColumnCollection)
{
string suffix = "";
foreach (IDTSInputColumn100 icl in mInputL.InputColumnCollection)
{
if (oc.CustomPropertyCollection["InputColumnID"].Value == icl.ID)
{
suffix = "L_";
}
}
foreach (IDTSInputColumn100 icr in mInputR.InputColumnCollection)
{
if (oc.CustomPropertyCollection["InputColumnID"].Value == icr.ID)
{
suffix = "R_";
}
}
oc.Name = suffix + oc.Name;
}
Inst.AcquireConnections(null);
Inst.ReinitializeMetaData();
Inst.ReleaseConnections();
// Return
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);
}
private void build()
{
#region Package Related
// Package related
Package package = new Package();
Executable e = package.Executables.Add("STOCK:PipelineTask");
TaskHost thMainPipe = e as TaskHost;
MainPipe dataFlowTask = thMainPipe.InnerObject as MainPipe;
thMainPipe.Name = "MyDFT";
thMainPipe.DelayValidation = true;
#endregion
#region Add Connection Manager
// Add Connection Manager
ConnectionManager cm = package.Connections.Add("OLEDB");
cm.Name = "OLEDB ConnectionManager";
cm.ConnectionString = "Data Source=(local);" +
"Initial Catalog=AdventureWorks;Provider=SQLOLEDB.1;" +
"Integrated Security=SSPI;";
#endregion
#region Add a OleDB Source and set up basic properties
// Add an OLE DB source to the data flow.
IDTSComponentMetaData100 component = dataFlowTask.ComponentMetaDataCollection.New();
component.Name = "OLEDBSource";
component.ComponentClassID = "Microsoft.OLEDBSource"; // check for the exact component class ID on your machine
// Get the design time instance of the component.
CManagedComponentWrapper instance = component.Instantiate();
// Initialize the component
instance.ProvideComponentProperties();
// Specify the connection manager.
if (component.RuntimeConnectionCollection.Count > 0)
{
component.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(package.Connections[0]);
component.RuntimeConnectionCollection[0].ConnectionManagerID = package.Connections[0].ID;
}
// Set the custom properties.
instance.SetComponentProperty("AccessMode", 2);
instance.SetComponentProperty("SqlCommand", "Select * from Production.Product");
#endregion
#region Core example showcasing use of IDTSExternalMetadataColumn when external data source is not available.
// Typically here we call acquireconnection, reinitmetadata etc to get the metadata from a data source that exists.
// Instead we will populate the metadata ourselves
#region Get External Columns Metadata
// Get the collection of external columns
List<Column> externalColumns = new List<Column>();
// Hard Coding Here. But grab them from your metadata source programmatically.
Column columnA = new Column("col_a", "DT_STR", 24, 0, 0, 1252);
Column columnB = new Column("col_b", "DT_STR", 36, 0, 0, 1252);
Column columnC = new Column("col_c", "DT_STR", 48, 0, 0, 1252);
externalColumns.Add(columnA);
externalColumns.Add(columnB);
externalColumns.Add(columnC);
#endregion
#region Add External Columns to our required IDTSOutput100
// Grab the appropriate output as needed. We will be adding ExternalColumns to this Output
IDTSOutput100 output = component.OutputCollection[0];
// Add each external column to the above IDTSOutPut
foreach (Column extCol in externalColumns)
{
IDTSExternalMetadataColumn100 col = output.ExternalMetadataColumnCollection.New();
col.Name = extCol.Name;
col.Scale = extCol.Scale;
col.Precision = extCol.Precision;
col.Length = extCol.Length;
col.CodePage = extCol.CodePage;
col.DataType = (Wrapper.DataType)Enum.Parse(typeof(Wrapper.DataType), extCol.SSISDataType);
}
#endregion
#region Create OutputColumn if it does not exist/or grab the output column if it Exists. Then associate it to the External Column
// Now associate the External Column to an Output Column.
// Here, we will simply associate the external column to an output column if the name matches (because of our use case)
foreach (IDTSExternalMetadataColumn100 extCol in output.ExternalMetadataColumnCollection)
{
bool outputColExists = false;
// Set DataTypes and Associate with external column if output col exists
foreach (IDTSOutputColumn100 outputCol in output.OutputColumnCollection)
{
if (outputCol.Name == extCol.Name) // is map based on name
{
// Set the data type properties
outputCol.SetDataTypeProperties(extCol.DataType, extCol.Length, extCol.Precision, extCol.Scale, extCol.CodePage);
// Associate the external column and the output column
outputCol.ExternalMetadataColumnID = extCol.ID;
outputColExists = true;
break;
}
}
// Create an IDTSOutputColumn if not exists.
if (!(outputColExists))
{
IDTSOutputColumn100 outputCol = output.OutputColumnCollection.New();
outputCol.Name = extCol.Name; // map is based on name
// Set the data type properties
outputCol.SetDataTypeProperties(extCol.DataType, extCol.Length, extCol.Precision, extCol.Scale, extCol.CodePage);
// Associate the external column and the output column
outputCol.ExternalMetadataColumnID = extCol.ID;
}
}
#endregion
#endregion
#region Save the Package to disk
new Application().SaveToXml(@"C:\Temp\Pkg.dtsx", package, null);
#endregion
}
public static void BuildSSISPackage(out Microsoft.SqlServer.Dts.Runtime.Package package, out IDTSComponentMetaData100 multipleHash, out CManagedComponentWrapper multipleHashInstance, out String lineageString, out MainPipe dataFlowTask, out Microsoft.SqlServer.Dts.Runtime.Application app)
{
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;
dataFlowTask = thMainPipe.InnerObject as MainPipe;
ComponentEventHandler events = new ComponentEventHandler();
dataFlowTask.Events = DtsConvert.GetExtendedInterface(events as IDTSComponentEvents);
// Create a flat file source
ConnectionManager flatFileConnectionManager = package.Connections.Add("FLATFILE");
flatFileConnectionManager.Properties["Format"].SetValue(flatFileConnectionManager, "Delimited");
flatFileConnectionManager.Properties["Name"].SetValue(flatFileConnectionManager, "Flat File Connection");
flatFileConnectionManager.Properties["ConnectionString"].SetValue(flatFileConnectionManager, @".\TextDataToBeHashed.txt");
flatFileConnectionManager.Properties["ColumnNamesInFirstDataRow"].SetValue(flatFileConnectionManager, false);
flatFileConnectionManager.Properties["HeaderRowDelimiter"].SetValue(flatFileConnectionManager, "\r\n");
flatFileConnectionManager.Properties["TextQualifier"].SetValue(flatFileConnectionManager, "\"");
flatFileConnectionManager.Properties["DataRowsToSkip"].SetValue(flatFileConnectionManager, 0);
flatFileConnectionManager.Properties["Unicode"].SetValue(flatFileConnectionManager, false);
flatFileConnectionManager.Properties["CodePage"].SetValue(flatFileConnectionManager, 1252);
// Create the columns in the flat file
IDTSConnectionManagerFlatFile100 flatFileConnection = flatFileConnectionManager.InnerObject as IDTSConnectionManagerFlatFile100;
IDTSConnectionManagerFlatFileColumn100 StringDataColumn = flatFileConnection.Columns.Add();
StringDataColumn.ColumnDelimiter = ",";
StringDataColumn.ColumnType = "Delimited";
StringDataColumn.DataType = DataType.DT_STR;
StringDataColumn.DataPrecision = 0;
StringDataColumn.DataScale = 0;
StringDataColumn.MaximumWidth = 255;
((IDTSName100)StringDataColumn).Name = "StringData";
IDTSConnectionManagerFlatFileColumn100 MoreStringColumn = flatFileConnection.Columns.Add();
MoreStringColumn.ColumnDelimiter = ",";
MoreStringColumn.ColumnType = "Delimited";
MoreStringColumn.DataType = DataType.DT_STR;
MoreStringColumn.DataPrecision = 0;
MoreStringColumn.DataScale = 0;
MoreStringColumn.MaximumWidth = 255;
((IDTSName100)MoreStringColumn).Name = "MoreString";
IDTSConnectionManagerFlatFileColumn100 DateColumn = flatFileConnection.Columns.Add();
DateColumn.ColumnDelimiter = ",";
DateColumn.ColumnType = "Delimited";
DateColumn.DataType = DataType.DT_DATE;
DateColumn.DataPrecision = 0;
DateColumn.DataScale = 0;
DateColumn.MaximumWidth = 0;
((IDTSName100)DateColumn).Name = "DateColumn";
IDTSConnectionManagerFlatFileColumn100 IntegerColumn = flatFileConnection.Columns.Add();
IntegerColumn.ColumnDelimiter = ",";
IntegerColumn.ColumnType = "Delimited";
IntegerColumn.DataType = DataType.DT_I4;
IntegerColumn.DataPrecision = 0;
IntegerColumn.DataScale = 0;
IntegerColumn.MaximumWidth = 0;
((IDTSName100)IntegerColumn).Name = "IntegerColumn";
IDTSConnectionManagerFlatFileColumn100 NumericColumn = flatFileConnection.Columns.Add();
NumericColumn.ColumnDelimiter = "\r\n";
NumericColumn.ColumnType = "Delimited";
NumericColumn.DataType = DataType.DT_NUMERIC;
NumericColumn.DataPrecision = 15;
NumericColumn.DataScale = 2;
NumericColumn.MaximumWidth = 0;
((IDTSName100)NumericColumn).Name = "NumericColumn";
app = new Microsoft.SqlServer.Dts.Runtime.Application();
IDTSComponentMetaData100 flatFileSource = dataFlowTask.ComponentMetaDataCollection.New();
flatFileSource.ComponentClassID = app.PipelineComponentInfos["Flat File Source"].CreationName;
// Get the design time instance of the Flat File Source Component
var flatFileSourceInstance = flatFileSource.Instantiate();
flatFileSourceInstance.ProvideComponentProperties();
flatFileSource.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(flatFileConnectionManager);
flatFileSource.RuntimeConnectionCollection[0].ConnectionManagerID = flatFileConnectionManager.ID;
// Reinitialize the metadata.
flatFileSourceInstance.AcquireConnections(null);
flatFileSourceInstance.ReinitializeMetaData();
flatFileSourceInstance.ReleaseConnections();
flatFileSource.CustomPropertyCollection["RetainNulls"].Value = true;
//[MD5BinaryOutput] varbinary(16), [MD5HexOutput] varchar(34), [MD5BaseOutput] varchar(24))";
multipleHash = dataFlowTask.ComponentMetaDataCollection.New();
multipleHash.ComponentClassID = typeof(Martin.SQLServer.Dts.MultipleHash).AssemblyQualifiedName;
multipleHashInstance = multipleHash.Instantiate();
multipleHashInstance.ProvideComponentProperties();
multipleHash.Name = "Multiple Hash Test";
multipleHashInstance.ReinitializeMetaData();
// Create the path from source to destination.
StaticTestUtilities.CreatePath(dataFlowTask, flatFileSource.OutputCollection[0], multipleHash, multipleHashInstance);
// Select the input columns.
IDTSInput100 multipleHashInput = multipleHash.InputCollection[0];
IDTSVirtualInput100 multipleHashvInput = multipleHashInput.GetVirtualInput();
foreach (IDTSVirtualInputColumn100 vColumn in multipleHashvInput.VirtualInputColumnCollection)
{
multipleHashInstance.SetUsageType(multipleHashInput.ID, multipleHashvInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
}
// Add the output columns
// Generate the Lineage String
lineageString = String.Empty;
foreach (IDTSInputColumn100 inputColumn in multipleHashInput.InputColumnCollection)
{
if (lineageString == String.Empty)
{
lineageString = String.Format("#{0}", inputColumn.LineageID);
}
else
{
lineageString = String.Format("{0},#{1}", lineageString, inputColumn.LineageID);
}
}
}