public static DerivedColumns CreateDerivedColumnsFromXml(Packages.VulcanPackage vulcanPackage, IDTSComponentMetaData90 parentComponent, MainPipe dataFlowTask, XPathNavigator derivedNav) { if (derivedNav == null || derivedNav.Name.ToUpperInvariant() != "DerivedColumns".ToUpperInvariant()) { //We don't handle this. return null; } string componentName = derivedNav.SelectSingleNode("@Name", vulcanPackage.VulcanConfig.NamespaceManager).Value; Message.Trace(Severity.Debug, "Begin: DerivedColumns Transformation {0}", componentName); DerivedColumns dc = new DerivedColumns(vulcanPackage, dataFlowTask, parentComponent, componentName, componentName); foreach (XPathNavigator nav in derivedNav.Select("rc:Column", vulcanPackage.VulcanConfig.NamespaceManager)) { string colName = nav.SelectSingleNode("@Name").Value; string typeAsString = nav.SelectSingleNode("@Type").Value; int length = nav.SelectSingleNode("@Length").ValueAsInt; int precision = nav.SelectSingleNode("@Precision").ValueAsInt; int scale = nav.SelectSingleNode("@Scale").ValueAsInt; int codepage = nav.SelectSingleNode("@Codepage").ValueAsInt; string expression = nav.Value; DataType type = TransformationFactory.GetDataTypeFromString(typeAsString); dc.AddOutputColumn(colName, type, expression, length, precision, scale, codepage); } return dc; }
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 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; }
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(); }
internal void MapInput(string sourceComponentName, MainPipe dataFlowTask) { IDTSComponentMetaData90 sourceComponent = DataFlowTask.ComponentMetaDataCollection[sourceComponentName]; dataFlowTask.PathCollection.New().AttachPathAndPropagateNotifications( sourceComponent.OutputCollection[0], unionAllCom.InputCollection["Input for " + sourceComponentName] ); InitializeAndMapDestination(); }
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(); }
protected Transformation( Packages.VulcanPackage vulcanPackage, MainPipe dataFlowTask, IDTSComponentMetaData90 parentComponent, string name, string description ) { this._vulcanPackage = vulcanPackage; this._dataFlowTask = dataFlowTask; this._parentComponent = parentComponent; this._name = name; this._description = description; }
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 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 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 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 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 static DerivedColumns CreateIsNullPatcherFromXml(Packages.VulcanPackage vulcanPackage, IDTSComponentMetaData90 parentComponent, MainPipe dataFlowTask, XPathNavigator nullNav) { if (nullNav == null || nullNav.Name.ToUpperInvariant() != "IsNullPatcher".ToUpperInvariant()) { return null; } string componentName = nullNav.SelectSingleNode("@Name", vulcanPackage.VulcanConfig.NamespaceManager).Value; Message.Trace(Severity.Debug, "Begin: IsNullPatcher variant DerivedColumns Transformation {0}", componentName); DerivedColumns dc = new DerivedColumns(vulcanPackage, dataFlowTask, parentComponent, componentName, componentName); IDTSVirtualInput90 vi = dc.Component.InputCollection[0].GetVirtualInput(); TemplateEmitter te = new TemplateEmitter("NullPatcherIsnullTemplate", vulcanPackage, null); foreach (XPathNavigator nav in nullNav.Select("rc:Column", vulcanPackage.VulcanConfig.NamespaceManager)) { string name = nav.SelectSingleNode("@Name").Value; string defaultValue = nav.SelectSingleNode("@DefaultValue").Value; dc.SetInputUsageType(vi, vi.VirtualInputColumnCollection[name], DTSUsageType.UT_READWRITE); IDTSInputColumn90 inputCol = dc.Component.InputCollection[0].InputColumnCollection[name]; string expression; te.SetParameters("#" + vi.VirtualInputColumnCollection[name].LineageID.ToString(), defaultValue); te.Emit(out expression); string friendlyExpression; te.SetParameters(name, defaultValue); te.Emit(out friendlyExpression); inputCol.CustomPropertyCollection["Expression"].Value = expression; inputCol.CustomPropertyCollection["FriendlyExpression"].Value = friendlyExpression; } return dc; }
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; }
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 static Lookup CreateLookupFromXml(Packages.VulcanPackage vulcanPackage, IDTSComponentMetaData90 parentComponent, MainPipe dataFlowTask, XPathNavigator lookupNav) { if (lookupNav == null || lookupNav.Name.ToUpperInvariant() != "Lookup".ToUpperInvariant()) { return(null); } string lookupName = lookupNav.SelectSingleNode("@Name", vulcanPackage.VulcanConfig.NamespaceManager).Value; Message.Trace(Severity.Debug, "Begin: Lookup Transformation {0}", lookupName); Connection sourceConnection = Connection.GetExistingConnection( vulcanPackage, lookupNav ); string query = lookupNav.SelectSingleNode("rc:Query", vulcanPackage.VulcanConfig.NamespaceManager) == null ? null : lookupNav.SelectSingleNode("rc:Query", vulcanPackage.VulcanConfig.NamespaceManager).Value; string table = lookupNav.SelectSingleNode("rc:Table", vulcanPackage.VulcanConfig.NamespaceManager) == null ? null : lookupNav.SelectSingleNode("rc:Table", vulcanPackage.VulcanConfig.NamespaceManager).Value; if (table != null) { SelectEmitter se = new SelectEmitter(vulcanPackage, table, "*"); se.Emit(out query); } Lookup l = new Lookup(vulcanPackage, dataFlowTask, parentComponent, lookupName, lookupName, sourceConnection, query); foreach (XPathNavigator inputNav in lookupNav.Select("rc:Input", vulcanPackage.VulcanConfig.NamespaceManager)) { string inputName = inputNav.SelectSingleNode("@Name", vulcanPackage.VulcanConfig.NamespaceManager).Value; string joinToName = inputNav.SelectSingleNode("rc:JoinToReferenceColumn", vulcanPackage.VulcanConfig.NamespaceManager) == null ? null : inputNav.SelectSingleNode("rc:JoinToReferenceColumn", vulcanPackage.VulcanConfig.NamespaceManager).Value; string copyFromName = inputNav.SelectSingleNode("rc:CopyFromReferenceColumn", vulcanPackage.VulcanConfig.NamespaceManager) == null ? null : inputNav.SelectSingleNode("rc:CopyFromReferenceColumn", vulcanPackage.VulcanConfig.NamespaceManager).Value; if (joinToName != null) { l.MapInput(inputName, joinToName, Lookup.MapType.JoinToReferenceColumn); } else if (copyFromName != null) { l.MapInput(inputName, copyFromName, Lookup.MapType.CopyFromReferenceColumn); } else { Message.Trace(Severity.Error, "Adding Lookup Type: Must supply either a JoinToName or CopyFromName when mapping inputs."); } } foreach (XPathNavigator outputNav in lookupNav.Select("rc:Output", vulcanPackage.VulcanConfig.NamespaceManager)) { string outputName = outputNav.SelectSingleNode("@Name", vulcanPackage.VulcanConfig.NamespaceManager).Value; string joinToName = outputNav.SelectSingleNode("rc:JoinToReferenceColumn", vulcanPackage.VulcanConfig.NamespaceManager) == null ? null : outputNav.SelectSingleNode("rc:JoinToReferenceColumn", vulcanPackage.VulcanConfig.NamespaceManager).Value; string copyFromName = outputNav.SelectSingleNode("rc:CopyFromReferenceColumn", vulcanPackage.VulcanConfig.NamespaceManager) == null ? null : outputNav.SelectSingleNode("rc:CopyFromReferenceColumn", vulcanPackage.VulcanConfig.NamespaceManager).Value; if (joinToName != null) { l.MapOutput(outputName, joinToName, Lookup.MapType.JoinToReferenceColumn); } else if (copyFromName != null) { l.MapOutput(outputName, copyFromName, Lookup.MapType.CopyFromReferenceColumn); } else { throw new Exception("Vulcan: ETLPattern: Adding Lookup Type: Must supply either a JoinToName or CopyFromName when mapping outputs."); } } return(l); }
public static Transformation ProcessTransformation(Packages.VulcanPackage vulcanPackage, IDTSComponentMetaData90 parentComponent, MainPipe dataFlowTask, XPathNavigator transformationsNav) { Transformation t = null; if (transformationsNav != null) { try { switch (transformationsNav.Name) { case "OLEDBCommand": t = CreateOLEDBCommandFromXml(vulcanPackage, parentComponent, dataFlowTask, transformationsNav); break; case "Lookup": t = CreateLookupFromXml(vulcanPackage, parentComponent, dataFlowTask, transformationsNav); break; case "DerivedColumns": t = CreateDerivedColumnsFromXml(vulcanPackage, parentComponent, dataFlowTask, transformationsNav); break; case "IsNullPatcher": t = CreateIsNullPatcherFromXml(vulcanPackage, parentComponent, dataFlowTask, transformationsNav); break; case "TermLookup": t = CreateTermLookupFromXml(vulcanPackage, parentComponent, dataFlowTask, transformationsNav); break; case "ConditionalSplit": t = CreateConditionalSplitFromXml(vulcanPackage, parentComponent, dataFlowTask, transformationsNav); break; case "UnionAll": t = CreateUnionAllFromXml(vulcanPackage, parentComponent, dataFlowTask, transformationsNav); break; case "Sort": t = CreateSortFromXml(vulcanPackage, parentComponent, dataFlowTask, transformationsNav); break; default: break; } } catch (System.Runtime.InteropServices.COMException ce) { Message.Trace(Severity.Error, ce, "COMException in transformation {0}\n {1}\n", transformationsNav.Name, ce.Message); } catch (Exception e) { Message.Trace(Severity.Error, e, "Exception in transformation {0}\n {1}\n", transformationsNav.Name, e.Message); } } return(t); }
public static OLEDBCommand CreateOLEDBCommandFromXml(Packages.VulcanPackage vulcanPackage, IDTSComponentMetaData90 parentComponent, MainPipe dataFlowTask, XPathNavigator commandNav) { if (commandNav == null || commandNav.Name.ToUpperInvariant() != "OLEDBCommand".ToUpperInvariant()) { return(null); } string commandName = commandNav.SelectSingleNode("@Name", vulcanPackage.VulcanConfig.NamespaceManager).Value; Message.Trace(Severity.Debug, "Begin: OLEDB Transformation {0}", commandName); Connection destConnection = Connection.GetExistingConnection( vulcanPackage, commandNav.SelectSingleNode("rc:DestinationConnection/@Name", vulcanPackage.VulcanConfig.NamespaceManager).Value ); string command = commandNav.SelectSingleNode("rc:Command", vulcanPackage.VulcanConfig.NamespaceManager).Value; OLEDBCommand dbc = new OLEDBCommand(vulcanPackage, dataFlowTask, parentComponent, commandName, commandName, destConnection, command); foreach (XPathNavigator nav in commandNav.Select("rc:Map", vulcanPackage.VulcanConfig.NamespaceManager)) { string source = nav.SelectSingleNode("@Source").Value; string destination = nav.SelectSingleNode("@Destination") == null ? "@" + source : nav.SelectSingleNode("@Destination").Value; dbc.Map(source, destination, false); } return(dbc); }
public static DerivedColumns CreateIsNullPatcherFromXml(Packages.VulcanPackage vulcanPackage, IDTSComponentMetaData90 parentComponent, MainPipe dataFlowTask, XPathNavigator nullNav) { if (nullNav == null || nullNav.Name.ToUpperInvariant() != "IsNullPatcher".ToUpperInvariant()) { return(null); } string componentName = nullNav.SelectSingleNode("@Name", vulcanPackage.VulcanConfig.NamespaceManager).Value; Message.Trace(Severity.Debug, "Begin: IsNullPatcher variant DerivedColumns Transformation {0}", componentName); DerivedColumns dc = new DerivedColumns(vulcanPackage, dataFlowTask, parentComponent, componentName, componentName); IDTSVirtualInput90 vi = dc.Component.InputCollection[0].GetVirtualInput(); TemplateEmitter te = new TemplateEmitter("NullPatcherIsnullTemplate", vulcanPackage, null); foreach (XPathNavigator nav in nullNav.Select("rc:Column", vulcanPackage.VulcanConfig.NamespaceManager)) { string name = nav.SelectSingleNode("@Name").Value; string defaultValue = nav.SelectSingleNode("@DefaultValue").Value; dc.SetInputUsageType(vi, vi.VirtualInputColumnCollection[name], DTSUsageType.UT_READWRITE); IDTSInputColumn90 inputCol = dc.Component.InputCollection[0].InputColumnCollection[name]; string expression; te.SetParameters("#" + vi.VirtualInputColumnCollection[name].LineageID.ToString(), defaultValue); te.Emit(out expression); string friendlyExpression; te.SetParameters(name, defaultValue); te.Emit(out friendlyExpression); inputCol.CustomPropertyCollection["Expression"].Value = expression; inputCol.CustomPropertyCollection["FriendlyExpression"].Value = friendlyExpression; } return(dc); }
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
private static Transformation CreateTermLookupFromXml(Vulcan.Packages.VulcanPackage vulcanPackage, IDTSComponentMetaData90 parentComponent, MainPipe dataFlowTask, XPathNavigator termLookupNav) { if (termLookupNav == null || termLookupNav.Name.ToUpperInvariant() != "TermLookup".ToUpperInvariant()) { return null; } string termLookupName = termLookupNav.SelectSingleNode("@Name", vulcanPackage.VulcanConfig.NamespaceManager).Value; bool isCaseSensitive = termLookupNav.SelectSingleNode("@IsCaseSensitive").ValueAsBoolean; string refTermColumn = termLookupNav.SelectSingleNode("@RefTermColumn").Value; string refTermTable = termLookupNav.SelectSingleNode("@RefTermTable").Value; Message.Trace(Severity.Debug, "Begin: TermLookup Transformation {0}", termLookupName); Connection sourceConnection = Connection.GetExistingConnection(vulcanPackage, termLookupNav.SelectSingleNode("@ConnectionName").Value); TermLookup tl = new TermLookup( vulcanPackage, dataFlowTask, parentComponent, termLookupName, termLookupName, sourceConnection, isCaseSensitive, refTermColumn, refTermTable ); foreach (XPathNavigator nav in termLookupNav.Select("rc:InputColumn", vulcanPackage.VulcanConfig.NamespaceManager)) { string inputColumnName = nav.SelectSingleNode("@InputColumnName").Value; TermLookup.InputColumnUsageType inputColumnUsageType = (TermLookup.InputColumnUsageType)Enum.Parse(typeof(TermLookup.InputColumnUsageType), nav.SelectSingleNode("@InputColumnUsageType").Value); tl.MapInput(inputColumnName, inputColumnUsageType); } return tl; }
private static Transformation CreateSortFromXml(Vulcan.Packages.VulcanPackage vulcanPackage, IDTSComponentMetaData90 parentComponent, MainPipe dataFlowTask, XPathNavigator sortNav) { if (sortNav == null || sortNav.Name.ToUpperInvariant() != "Sort".ToUpperInvariant()) { return null; } string sortName = sortNav.SelectSingleNode("@Name", vulcanPackage.VulcanConfig.NamespaceManager).Value; bool eliminateDuplicates = sortNav.SelectSingleNode("@EliminateDuplicates", vulcanPackage.VulcanConfig.NamespaceManager).ValueAsBoolean; int maximumThreads = sortNav.SelectSingleNode("@MaximumThreads", vulcanPackage.VulcanConfig.NamespaceManager).ValueAsInt; //MaximumThreads can not be 0, according to SSIS if (maximumThreads == 0) { maximumThreads = -1; } Message.Trace(Severity.Debug, "Begin: UnionAll Transformation {0}", sortName); Sort sortTask = new Sort( vulcanPackage, dataFlowTask, parentComponent, sortName, sortName, eliminateDuplicates, maximumThreads ); foreach (XPathNavigator navInput in sortNav.Select("rc:InputColumn", vulcanPackage.VulcanConfig.NamespaceManager)) { string inputColumnName = navInput.SelectSingleNode("@InputColumnName", vulcanPackage.VulcanConfig.NamespaceManager).Value; Sort.InputColumnUsageType inputColumnUsageType = (Sort.InputColumnUsageType)Enum.Parse(typeof(Sort.InputColumnUsageType), navInput.SelectSingleNode("@InputColumnUsageType").Value); Sort.SortType SortType = (Sort.SortType)Enum.Parse(typeof(Sort.SortType), navInput.SelectSingleNode("@SortType").Value); List<Sort.ComparisonFlag> comparisonFlagList = new List<Sort.ComparisonFlag>(); foreach (XPathNavigator navComparisonFlag in navInput.Select("rc:ComparisonFlag", vulcanPackage.VulcanConfig.NamespaceManager)) { comparisonFlagList.Add((Sort.ComparisonFlag)Enum.Parse(typeof(Sort.ComparisonFlag), navComparisonFlag.Value)); } sortTask.SetInputColumnProperty(inputColumnName, inputColumnUsageType, SortType, comparisonFlagList); } return sortTask; }
private static Transformation CreateConditionalSplitFromXml(Vulcan.Packages.VulcanPackage vulcanPackage, IDTSComponentMetaData90 parentComponent, MainPipe dataFlowTask, XPathNavigator conditionalSplitNav) { if (conditionalSplitNav == null || conditionalSplitNav.Name.ToUpperInvariant() != "ConditionalSplit".ToUpperInvariant()) { return null; } string conditionalSplitName = conditionalSplitNav.SelectSingleNode("@Name", vulcanPackage.VulcanConfig.NamespaceManager).Value; Message.Trace(Severity.Debug, "Begin: ConditionalSplit Transformation {0}", conditionalSplitName); ConditionalSplit cs = new ConditionalSplit( vulcanPackage, dataFlowTask, parentComponent, conditionalSplitName, conditionalSplitName ); int intEvaluationOrder = 0; string expression = string.Empty; foreach (XPathNavigator nav in conditionalSplitNav.Select("//rc:Output|//rc:DefaultOutput", vulcanPackage.VulcanConfig.NamespaceManager)) { if (nav.Name == "DefaultOutput") { cs.Component.OutputCollection.SetIndex(cs.Component.OutputCollection.GetObjectIndexByID(cs.Component.OutputCollection["Conditional Split Default Output"].ID), 0); } else { expression = nav.SelectSingleNode("rc:Expression", vulcanPackage.VulcanConfig.NamespaceManager).Value; IDTSOutput90 newPath = cs.Component.OutputCollection.New(); newPath.Name = nav.SelectSingleNode("@Name", vulcanPackage.VulcanConfig.NamespaceManager).Value; newPath.Description = nav.SelectSingleNode("@Name", vulcanPackage.VulcanConfig.NamespaceManager).Value; newPath.ExclusionGroup = cs.Component.OutputCollection["Conditional Split Default Output"].ExclusionGroup; newPath.SynchronousInputID = cs.Component.OutputCollection["Conditional Split Default Output"].SynchronousInputID; newPath.ErrorOrTruncationOperation = "Computation"; newPath.ErrorRowDisposition = DTSRowDisposition.RD_IgnoreFailure; newPath.TruncationRowDisposition = DTSRowDisposition.RD_FailComponent; IDTSCustomProperty90 propEvaluationOrder = newPath.CustomPropertyCollection.New(); propEvaluationOrder.Name = "EvaluationOrder"; propEvaluationOrder.Value = intEvaluationOrder; IDTSCustomProperty90 propFriendlyExpression = newPath.CustomPropertyCollection.New(); propFriendlyExpression.Name = "FriendlyExpression"; propFriendlyExpression.Value = expression; IDTSCustomProperty90 propExpression = newPath.CustomPropertyCollection.New(); propExpression.Name = "Expression"; propExpression.Value = expression; //A workaround to connect the path to Conditional Spit's output, //because we always connect the current task to the previous task's first output cs.Component.OutputCollection.SetIndex(cs.Component.OutputCollection.GetObjectIndexByID(newPath.ID), 0); intEvaluationOrder++; } IDTSComponentMetaData90 startComponent = cs.Component; XPathNavigator transNav = nav.SelectSingleNode("rc:ConditionalSplitOutputPath/rc:Transformations", vulcanPackage.VulcanConfig.NamespaceManager); if (transNav != null) { foreach (XPathNavigator etlNav in transNav.SelectChildren(XPathNodeType.Element)) { // this is naughty but can be fixed later :) Transformation t = TransformationFactory.ProcessTransformation(vulcanPackage, startComponent, dataFlowTask, etlNav); if (t != null) { startComponent = t.Component; } } } XPathNavigator destNav = nav.SelectSingleNode("rc:ConditionalSplitOutputPath/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, startComponent, 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 navMap in destNav.Select("rc:Map", vulcanPackage.VulcanConfig.NamespaceManager)) { string source = navMap.SelectSingleNode("@Source").Value; string destination; bool unMap = false; if (navMap.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; } return cs; }
private static Transformation CreateTermLookupFromXml(Vulcan.Packages.VulcanPackage vulcanPackage, IDTSComponentMetaData90 parentComponent, MainPipe dataFlowTask, XPathNavigator termLookupNav) { if (termLookupNav == null || termLookupNav.Name.ToUpperInvariant() != "TermLookup".ToUpperInvariant()) { return(null); } string termLookupName = termLookupNav.SelectSingleNode("@Name", vulcanPackage.VulcanConfig.NamespaceManager).Value; bool isCaseSensitive = termLookupNav.SelectSingleNode("@IsCaseSensitive").ValueAsBoolean; string refTermColumn = termLookupNav.SelectSingleNode("@RefTermColumn").Value; string refTermTable = termLookupNav.SelectSingleNode("@RefTermTable").Value; Message.Trace(Severity.Debug, "Begin: TermLookup Transformation {0}", termLookupName); Connection sourceConnection = Connection.GetExistingConnection(vulcanPackage, termLookupNav.SelectSingleNode("@ConnectionName").Value); TermLookup tl = new TermLookup( vulcanPackage, dataFlowTask, parentComponent, termLookupName, termLookupName, sourceConnection, isCaseSensitive, refTermColumn, refTermTable ); foreach (XPathNavigator nav in termLookupNav.Select("rc:InputColumn", vulcanPackage.VulcanConfig.NamespaceManager)) { string inputColumnName = nav.SelectSingleNode("@InputColumnName").Value; TermLookup.InputColumnUsageType inputColumnUsageType = (TermLookup.InputColumnUsageType)Enum.Parse(typeof(TermLookup.InputColumnUsageType), nav.SelectSingleNode("@InputColumnUsageType").Value); tl.MapInput(inputColumnName, inputColumnUsageType); } return(tl); }
public static Lookup CreateLookupFromXml(Packages.VulcanPackage vulcanPackage, IDTSComponentMetaData90 parentComponent, MainPipe dataFlowTask, XPathNavigator lookupNav) { if (lookupNav == null || lookupNav.Name.ToUpperInvariant() != "Lookup".ToUpperInvariant()) { return null; } string lookupName = lookupNav.SelectSingleNode("@Name", vulcanPackage.VulcanConfig.NamespaceManager).Value; Message.Trace(Severity.Debug, "Begin: Lookup Transformation {0}", lookupName); Connection sourceConnection = Connection.GetExistingConnection( vulcanPackage, lookupNav ); string query = lookupNav.SelectSingleNode("rc:Query", vulcanPackage.VulcanConfig.NamespaceManager) == null ? null : lookupNav.SelectSingleNode("rc:Query", vulcanPackage.VulcanConfig.NamespaceManager).Value; string table = lookupNav.SelectSingleNode("rc:Table", vulcanPackage.VulcanConfig.NamespaceManager) == null ? null : lookupNav.SelectSingleNode("rc:Table", vulcanPackage.VulcanConfig.NamespaceManager).Value; if (table != null) { SelectEmitter se = new SelectEmitter(vulcanPackage, table, "*"); se.Emit(out query); } Lookup l = new Lookup(vulcanPackage, dataFlowTask, parentComponent, lookupName, lookupName, sourceConnection, query); foreach (XPathNavigator inputNav in lookupNav.Select("rc:Input", vulcanPackage.VulcanConfig.NamespaceManager)) { string inputName = inputNav.SelectSingleNode("@Name", vulcanPackage.VulcanConfig.NamespaceManager).Value; string joinToName = inputNav.SelectSingleNode("rc:JoinToReferenceColumn", vulcanPackage.VulcanConfig.NamespaceManager) == null ? null : inputNav.SelectSingleNode("rc:JoinToReferenceColumn", vulcanPackage.VulcanConfig.NamespaceManager).Value; string copyFromName = inputNav.SelectSingleNode("rc:CopyFromReferenceColumn", vulcanPackage.VulcanConfig.NamespaceManager) == null ? null : inputNav.SelectSingleNode("rc:CopyFromReferenceColumn", vulcanPackage.VulcanConfig.NamespaceManager).Value; if (joinToName != null) { l.MapInput(inputName, joinToName, Lookup.MapType.JoinToReferenceColumn); } else if (copyFromName != null) { l.MapInput(inputName, copyFromName, Lookup.MapType.CopyFromReferenceColumn); } else { Message.Trace(Severity.Error, "Adding Lookup Type: Must supply either a JoinToName or CopyFromName when mapping inputs."); } } foreach (XPathNavigator outputNav in lookupNav.Select("rc:Output", vulcanPackage.VulcanConfig.NamespaceManager)) { string outputName = outputNav.SelectSingleNode("@Name", vulcanPackage.VulcanConfig.NamespaceManager).Value; string joinToName = outputNav.SelectSingleNode("rc:JoinToReferenceColumn", vulcanPackage.VulcanConfig.NamespaceManager) == null ? null : outputNav.SelectSingleNode("rc:JoinToReferenceColumn", vulcanPackage.VulcanConfig.NamespaceManager).Value; string copyFromName = outputNav.SelectSingleNode("rc:CopyFromReferenceColumn", vulcanPackage.VulcanConfig.NamespaceManager) == null ? null : outputNav.SelectSingleNode("rc:CopyFromReferenceColumn", vulcanPackage.VulcanConfig.NamespaceManager).Value; if (joinToName != null) { l.MapOutput(outputName, joinToName, Lookup.MapType.JoinToReferenceColumn); } else if (copyFromName != null) { l.MapOutput(outputName, copyFromName, Lookup.MapType.CopyFromReferenceColumn); } else { throw new Exception("Vulcan: ETLPattern: Adding Lookup Type: Must supply either a JoinToName or CopyFromName when mapping outputs."); } } return l; }
private static Transformation CreateSortFromXml(Vulcan.Packages.VulcanPackage vulcanPackage, IDTSComponentMetaData90 parentComponent, MainPipe dataFlowTask, XPathNavigator sortNav) { if (sortNav == null || sortNav.Name.ToUpperInvariant() != "Sort".ToUpperInvariant()) { return(null); } string sortName = sortNav.SelectSingleNode("@Name", vulcanPackage.VulcanConfig.NamespaceManager).Value; bool eliminateDuplicates = sortNav.SelectSingleNode("@EliminateDuplicates", vulcanPackage.VulcanConfig.NamespaceManager).ValueAsBoolean; int maximumThreads = sortNav.SelectSingleNode("@MaximumThreads", vulcanPackage.VulcanConfig.NamespaceManager).ValueAsInt; //MaximumThreads can not be 0, according to SSIS if (maximumThreads == 0) { maximumThreads = -1; } Message.Trace(Severity.Debug, "Begin: UnionAll Transformation {0}", sortName); Sort sortTask = new Sort( vulcanPackage, dataFlowTask, parentComponent, sortName, sortName, eliminateDuplicates, maximumThreads ); foreach (XPathNavigator navInput in sortNav.Select("rc:InputColumn", vulcanPackage.VulcanConfig.NamespaceManager)) { string inputColumnName = navInput.SelectSingleNode("@InputColumnName", vulcanPackage.VulcanConfig.NamespaceManager).Value; Sort.InputColumnUsageType inputColumnUsageType = (Sort.InputColumnUsageType)Enum.Parse(typeof(Sort.InputColumnUsageType), navInput.SelectSingleNode("@InputColumnUsageType").Value); Sort.SortType SortType = (Sort.SortType)Enum.Parse(typeof(Sort.SortType), navInput.SelectSingleNode("@SortType").Value); List <Sort.ComparisonFlag> comparisonFlagList = new List <Sort.ComparisonFlag>(); foreach (XPathNavigator navComparisonFlag in navInput.Select("rc:ComparisonFlag", vulcanPackage.VulcanConfig.NamespaceManager)) { comparisonFlagList.Add((Sort.ComparisonFlag)Enum.Parse(typeof(Sort.ComparisonFlag), navComparisonFlag.Value)); } sortTask.SetInputColumnProperty(inputColumnName, inputColumnUsageType, SortType, comparisonFlagList); } return(sortTask); }
public static DerivedColumns CreateDerivedColumnsFromXml(Packages.VulcanPackage vulcanPackage, IDTSComponentMetaData90 parentComponent, MainPipe dataFlowTask, XPathNavigator derivedNav) { if (derivedNav == null || derivedNav.Name.ToUpperInvariant() != "DerivedColumns".ToUpperInvariant()) { //We don't handle this. return(null); } string componentName = derivedNav.SelectSingleNode("@Name", vulcanPackage.VulcanConfig.NamespaceManager).Value; Message.Trace(Severity.Debug, "Begin: DerivedColumns Transformation {0}", componentName); DerivedColumns dc = new DerivedColumns(vulcanPackage, dataFlowTask, parentComponent, componentName, componentName); foreach (XPathNavigator nav in derivedNav.Select("rc:Column", vulcanPackage.VulcanConfig.NamespaceManager)) { string colName = nav.SelectSingleNode("@Name").Value; string typeAsString = nav.SelectSingleNode("@Type").Value; int length = nav.SelectSingleNode("@Length").ValueAsInt; int precision = nav.SelectSingleNode("@Precision").ValueAsInt; int scale = nav.SelectSingleNode("@Scale").ValueAsInt; int codepage = nav.SelectSingleNode("@Codepage").ValueAsInt; string expression = nav.Value; DataType type = TransformationFactory.GetDataTypeFromString(typeAsString); dc.AddOutputColumn(colName, type, expression, length, precision, scale, codepage); } return(dc); }
private static Transformation CreateUnionAllFromXml(Vulcan.Packages.VulcanPackage vulcanPackage, IDTSComponentMetaData90 parentComponent, MainPipe dataFlowTask, XPathNavigator unionAllNav) { if (unionAllNav == null || unionAllNav.Name.ToUpperInvariant() != "UnionAll".ToUpperInvariant()) { return null; } string unionAllName = unionAllNav.SelectSingleNode("@Name", vulcanPackage.VulcanConfig.NamespaceManager).Value; Message.Trace(Severity.Debug, "Begin: UnionAll Transformation {0}", unionAllName); UnionAll ua = new UnionAll( vulcanPackage, dataFlowTask, parentComponent, unionAllName, unionAllName ); foreach (XPathNavigator navInput in unionAllNav.Select("rc:SourceComponent", vulcanPackage.VulcanConfig.NamespaceManager)) { string sourceComponentName = navInput.SelectSingleNode("@SourceComponentName", vulcanPackage.VulcanConfig.NamespaceManager).Value; ua.Component.InputCollection.New().Name = "Input for " + sourceComponentName; ua.MapInput(sourceComponentName, dataFlowTask); foreach (XPathNavigator navInputColumn in navInput.Select("rc:Map", vulcanPackage.VulcanConfig.NamespaceManager)) { string sourceColumnName = navInputColumn.SelectSingleNode("@Source", vulcanPackage.VulcanConfig.NamespaceManager).Value; string destinationColumnName; bool unMap = false; if (navInputColumn.SelectSingleNode("@Destination") == null) { unMap = true; destinationColumnName = sourceColumnName; } else { destinationColumnName = navInputColumn.SelectSingleNode("@Destination", vulcanPackage.VulcanConfig.NamespaceManager).Value; } ua.MapInputColumn(sourceComponentName, sourceColumnName, destinationColumnName, unMap); } } ua.InitializeAndMapDestination(); return ua; }
public static OLEDBCommand CreateOLEDBCommandFromXml(Packages.VulcanPackage vulcanPackage, IDTSComponentMetaData90 parentComponent, MainPipe dataFlowTask, XPathNavigator commandNav) { if (commandNav == null || commandNav.Name.ToUpperInvariant() != "OLEDBCommand".ToUpperInvariant()) { return null; } string commandName = commandNav.SelectSingleNode("@Name", vulcanPackage.VulcanConfig.NamespaceManager).Value; Message.Trace(Severity.Debug, "Begin: OLEDB Transformation {0}", commandName); Connection destConnection = Connection.GetExistingConnection( vulcanPackage, commandNav.SelectSingleNode("rc:DestinationConnection/@Name", vulcanPackage.VulcanConfig.NamespaceManager).Value ); string command = commandNav.SelectSingleNode("rc:Command", vulcanPackage.VulcanConfig.NamespaceManager).Value; OLEDBCommand dbc = new OLEDBCommand(vulcanPackage, dataFlowTask, parentComponent, commandName, commandName, destConnection, command); foreach (XPathNavigator nav in commandNav.Select("rc:Map", vulcanPackage.VulcanConfig.NamespaceManager)) { string source = nav.SelectSingleNode("@Source").Value; string destination = nav.SelectSingleNode("@Destination") == null ? "@" + source : nav.SelectSingleNode("@Destination").Value; dbc.Map(source, destination, false); } return dbc; }
private static Transformation CreateUnionAllFromXml(Vulcan.Packages.VulcanPackage vulcanPackage, IDTSComponentMetaData90 parentComponent, MainPipe dataFlowTask, XPathNavigator unionAllNav) { if (unionAllNav == null || unionAllNav.Name.ToUpperInvariant() != "UnionAll".ToUpperInvariant()) { return(null); } string unionAllName = unionAllNav.SelectSingleNode("@Name", vulcanPackage.VulcanConfig.NamespaceManager).Value; Message.Trace(Severity.Debug, "Begin: UnionAll Transformation {0}", unionAllName); UnionAll ua = new UnionAll( vulcanPackage, dataFlowTask, parentComponent, unionAllName, unionAllName ); foreach (XPathNavigator navInput in unionAllNav.Select("rc:SourceComponent", vulcanPackage.VulcanConfig.NamespaceManager)) { string sourceComponentName = navInput.SelectSingleNode("@SourceComponentName", vulcanPackage.VulcanConfig.NamespaceManager).Value; ua.Component.InputCollection.New().Name = "Input for " + sourceComponentName; ua.MapInput(sourceComponentName, dataFlowTask); foreach (XPathNavigator navInputColumn in navInput.Select("rc:Map", vulcanPackage.VulcanConfig.NamespaceManager)) { string sourceColumnName = navInputColumn.SelectSingleNode("@Source", vulcanPackage.VulcanConfig.NamespaceManager).Value; string destinationColumnName; bool unMap = false; if (navInputColumn.SelectSingleNode("@Destination") == null) { unMap = true; destinationColumnName = sourceColumnName; } else { destinationColumnName = navInputColumn.SelectSingleNode("@Destination", vulcanPackage.VulcanConfig.NamespaceManager).Value; } ua.MapInputColumn(sourceComponentName, sourceColumnName, destinationColumnName, unMap); } } ua.InitializeAndMapDestination(); return(ua); }
public static Transformation ProcessTransformation(Packages.VulcanPackage vulcanPackage, IDTSComponentMetaData90 parentComponent, MainPipe dataFlowTask, XPathNavigator transformationsNav) { Transformation t = null; if (transformationsNav != null) { try { switch (transformationsNav.Name) { case "OLEDBCommand": t = CreateOLEDBCommandFromXml(vulcanPackage, parentComponent, dataFlowTask, transformationsNav); break; case "Lookup": t = CreateLookupFromXml(vulcanPackage, parentComponent, dataFlowTask, transformationsNav); break; case "DerivedColumns": t = CreateDerivedColumnsFromXml(vulcanPackage, parentComponent, dataFlowTask, transformationsNav); break; case "IsNullPatcher": t = CreateIsNullPatcherFromXml(vulcanPackage, parentComponent, dataFlowTask, transformationsNav); break; case "TermLookup": t = CreateTermLookupFromXml(vulcanPackage, parentComponent, dataFlowTask, transformationsNav); break; case "ConditionalSplit": t = CreateConditionalSplitFromXml(vulcanPackage, parentComponent, dataFlowTask, transformationsNav); break; case "UnionAll": t = CreateUnionAllFromXml(vulcanPackage, parentComponent, dataFlowTask, transformationsNav); break; case "Sort": t = CreateSortFromXml(vulcanPackage, parentComponent, dataFlowTask, transformationsNav); break; default: break; } } catch (System.Runtime.InteropServices.COMException ce) { Message.Trace(Severity.Error, ce, "COMException in transformation {0}\n {1}\n", transformationsNav.Name, ce.Message); } catch (Exception e) { Message.Trace(Severity.Error, e, "Exception in transformation {0}\n {1}\n", transformationsNav.Name, e.Message); } } return t; }
private static Transformation CreateConditionalSplitFromXml(Vulcan.Packages.VulcanPackage vulcanPackage, IDTSComponentMetaData90 parentComponent, MainPipe dataFlowTask, XPathNavigator conditionalSplitNav) { if (conditionalSplitNav == null || conditionalSplitNav.Name.ToUpperInvariant() != "ConditionalSplit".ToUpperInvariant()) { return(null); } string conditionalSplitName = conditionalSplitNav.SelectSingleNode("@Name", vulcanPackage.VulcanConfig.NamespaceManager).Value; Message.Trace(Severity.Debug, "Begin: ConditionalSplit Transformation {0}", conditionalSplitName); ConditionalSplit cs = new ConditionalSplit( vulcanPackage, dataFlowTask, parentComponent, conditionalSplitName, conditionalSplitName ); int intEvaluationOrder = 0; string expression = string.Empty; foreach (XPathNavigator nav in conditionalSplitNav.Select("//rc:Output|//rc:DefaultOutput", vulcanPackage.VulcanConfig.NamespaceManager)) { if (nav.Name == "DefaultOutput") { cs.Component.OutputCollection.SetIndex(cs.Component.OutputCollection.GetObjectIndexByID(cs.Component.OutputCollection["Conditional Split Default Output"].ID), 0); } else { expression = nav.SelectSingleNode("rc:Expression", vulcanPackage.VulcanConfig.NamespaceManager).Value; IDTSOutput90 newPath = cs.Component.OutputCollection.New(); newPath.Name = nav.SelectSingleNode("@Name", vulcanPackage.VulcanConfig.NamespaceManager).Value; newPath.Description = nav.SelectSingleNode("@Name", vulcanPackage.VulcanConfig.NamespaceManager).Value; newPath.ExclusionGroup = cs.Component.OutputCollection["Conditional Split Default Output"].ExclusionGroup; newPath.SynchronousInputID = cs.Component.OutputCollection["Conditional Split Default Output"].SynchronousInputID; newPath.ErrorOrTruncationOperation = "Computation"; newPath.ErrorRowDisposition = DTSRowDisposition.RD_IgnoreFailure; newPath.TruncationRowDisposition = DTSRowDisposition.RD_FailComponent; IDTSCustomProperty90 propEvaluationOrder = newPath.CustomPropertyCollection.New(); propEvaluationOrder.Name = "EvaluationOrder"; propEvaluationOrder.Value = intEvaluationOrder; IDTSCustomProperty90 propFriendlyExpression = newPath.CustomPropertyCollection.New(); propFriendlyExpression.Name = "FriendlyExpression"; propFriendlyExpression.Value = expression; IDTSCustomProperty90 propExpression = newPath.CustomPropertyCollection.New(); propExpression.Name = "Expression"; propExpression.Value = expression; //A workaround to connect the path to Conditional Spit's output, //because we always connect the current task to the previous task's first output cs.Component.OutputCollection.SetIndex(cs.Component.OutputCollection.GetObjectIndexByID(newPath.ID), 0); intEvaluationOrder++; } IDTSComponentMetaData90 startComponent = cs.Component; XPathNavigator transNav = nav.SelectSingleNode("rc:ConditionalSplitOutputPath/rc:Transformations", vulcanPackage.VulcanConfig.NamespaceManager); if (transNav != null) { foreach (XPathNavigator etlNav in transNav.SelectChildren(XPathNodeType.Element)) { // this is naughty but can be fixed later :) Transformation t = TransformationFactory.ProcessTransformation(vulcanPackage, startComponent, dataFlowTask, etlNav); if (t != null) { startComponent = t.Component; } } } XPathNavigator destNav = nav.SelectSingleNode("rc:ConditionalSplitOutputPath/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, startComponent, 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 navMap in destNav.Select("rc:Map", vulcanPackage.VulcanConfig.NamespaceManager)) { string source = navMap.SelectSingleNode("@Source").Value; string destination; bool unMap = false; if (navMap.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; } return(cs); }