public IDTSOutputColumn100 ModifyComp_DerivedCol_AddCol(IDTSComponentMetaData100 Comp, string DCName, string DCExpression, DataType DTDataType, int DCDTLength = 0, int DCDTPrecision = 0, int DCDTScale = 0, int DCDTCodePage = 0 ) { IDTSOutputColumn100 Col = Comp.OutputCollection[0].OutputColumnCollection.New(); Col.Name = DCName; Col.SetDataTypeProperties(DTDataType, DCDTLength, DCDTPrecision, DCDTScale, DCDTCodePage); Col.ExternalMetadataColumnID = 0; Col.ErrorRowDisposition = DTSRowDisposition.RD_FailComponent; Col.TruncationRowDisposition = DTSRowDisposition.RD_FailComponent; IDTSCustomProperty100 propExp = Col.CustomPropertyCollection.New(); propExp.Name = "Expression"; propExp.Value = DCExpression; IDTSCustomProperty100 propFrExp = Col.CustomPropertyCollection.New(); propFrExp.Name = "FriendlyExpression"; propFrExp.Value = DCExpression; return(Col); }
public void Initialize(Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSComponentMetaData100 dtsComponentMetadata, IServiceProvider serviceProvider) { this.serviceProvider = serviceProvider; this.metaData = dtsComponentMetadata; this.connectionService = (IDtsConnectionService)serviceProvider.GetService(typeof(IDtsConnectionService)); }
private IDTSPath100 ConnectToAnotherPipelineComponent(IDTSComponentMetaData100 sourceComponent, int sourceComponentOutputIndex = 0, int inputCollectionIndex = 0) { bool pathHasStartPoint = false; IDTSPath100 path = null; foreach (IDTSPath100 dtsPath in ParentDataFlowTask.MainPipe.PathCollection) { IDTSOutput100 output = dtsPath.StartPoint; if (dtsPath.StartPoint.Name == sourceComponent.OutputCollection[sourceComponentOutputIndex].Name && dtsPath.StartPoint.Component.Name == sourceComponent.Name ) { pathHasStartPoint = true; path = dtsPath; } } if (pathHasStartPoint) { ParentDataFlowTask.MainPipe.PathCollection.RemoveObjectByID(path.ID); } path = ParentDataFlowTask.MainPipe.PathCollection.New(); path.AttachPathAndPropagateNotifications( sourceComponent.OutputCollection[sourceComponentOutputIndex], ComponentMetaData.InputCollection[inputCollectionIndex]); return(path); }
public void TestChangeRowsProcessedOutputTypeToData() { Microsoft.SqlServer.Dts.Runtime.Package package = new Microsoft.SqlServer.Dts.Runtime.Package(); Executable exec = package.Executables.Add("STOCK:PipelineTask"); Microsoft.SqlServer.Dts.Runtime.TaskHost thMainPipe = exec as Microsoft.SqlServer.Dts.Runtime.TaskHost; MainPipe dataFlowTask = thMainPipe.InnerObject as MainPipe; ComponentEventHandler events = new ComponentEventHandler(); dataFlowTask.Events = DtsConvert.GetExtendedInterface(events as IDTSComponentEvents); IDTSComponentMetaData100 textFileSplitter = dataFlowTask.ComponentMetaDataCollection.New(); textFileSplitter.Name = "Row Splitter Test"; textFileSplitter.ComponentClassID = typeof(Martin.SQLServer.Dts.TextFileSplitter).AssemblyQualifiedName; CManagedComponentWrapper instance = textFileSplitter.Instantiate(); instance.ProvideComponentProperties(); Boolean exceptionThrown = false; try { instance.SetOutputProperty(textFileSplitter.OutputCollection[3].ID, ManageProperties.typeOfOutput, Utilities.typeOfOutputEnum.DataRecords); } catch (COMException ex) { Assert.AreEqual(MessageStrings.CantChangeOutputProperties("RowsProcessed"), ex.Message, "Exception Message Wrong"); exceptionThrown = true; } Assert.IsTrue(exceptionThrown, "Exception Not Thrown"); }
public IDTSComponentMetaData100 AddComp_RowCount(string ComponentName, string VarName, IDTSOutput100 outCols ) { // Create IDTSComponentMetaData100 Comp = dmp.ComponentMetaDataCollection.New(); Comp.ComponentClassID = "Microsoft.RowCount"; // Instantiate CManagedComponentWrapper Inst = Comp.Instantiate(); Inst.ProvideComponentProperties(); Comp.Name = ComponentName; Comp.Description = "zliczanie wierszy"; // Parametrize Inst.SetComponentProperty("VariableName", VarName); // Connect IDTSPath100 pth = dmp.PathCollection.New(); pth.AttachPathAndPropagateNotifications(outCols, Comp.InputCollection[0]); // Return return(Comp); }
public void TestOutputColumn_Timecode_Missing() { 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(); speechToText.CustomPropertyCollection[Utility.SubscriptionKeyPropName].Value = "NotTheDefault"; // Before this is default setup for a clean component speechToText.OutputCollection[0].OutputColumnCollection.RemoveObjectByID(speechToText.OutputCollection[0].OutputColumnCollection[Utility.OutputTimecodeColumnName].ID); IDTSOutputColumn100 tempCol = speechToText.OutputCollection[0].OutputColumnCollection.New(); tempCol.Name = "TempCol"; tempCol.SetDataTypeProperties(DataType.DT_STR, 10, 0, 0, 1252); DTSValidationStatus actual = speechToTextInstance.Validate(); DTSValidationStatus expected = DTSValidationStatus.VS_ISCORRUPT; Assert.AreEqual(expected, actual); Assert.AreEqual("[Error] SSIS Speech To Text: Required Output Column Timecode is missing.", events.errorMessages[0]); }
/// <summary> /// constructor of the GUI /// </summary> /// <param name="metadata">SSIS metadata for the component</param> /// <param name="connections">availabe SSIS connectionmanagers</param> /// <param name="serviceProvider">SSIS service provider</param> /// <param name="variables">SSIS variables</param> public frmLookupUI(IDTSComponentMetaData100 metadata, Connections connections, IServiceProvider serviceProvider, Variables variables) { _metadata = metadata; _serviceProvider = serviceProvider; _connections = connections; _variables = variables; LoadCustomProperties(); //initialize gui components & databindings InitializeComponent(); PopulateDftColumnItemlists(); InitializeCustomComponents(); CreateBindings(); PopulateSqlTableItemList(); //all input columns are always marked as readwrite IDTSVirtualInput100 virtualInput = _metadata.InputCollection[Constants.INPUT_NAME].GetVirtualInput(); for (int i = 0; i < virtualInput.VirtualInputColumnCollection.Count; i++) { virtualInput.SetUsageType(virtualInput.VirtualInputColumnCollection[i].LineageID, DTSUsageType.UT_READWRITE); } //set the window title this.Text = this.Text + " " + _isagCustomProperties.Version; }
public void TestOutputColumn_InvalidCustomProperty() { 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(); speechToText.CustomPropertyCollection[Utility.SubscriptionKeyPropName].Value = "NotTheDefault"; // Before this is default setup for a clean component IDTSCustomProperty100 cp = speechToText.OutputCollection[0].OutputColumnCollection[0].CustomPropertyCollection.New(); cp.Name = "IAmInvalid"; cp.Value = "IAmInvalid"; DTSValidationStatus actual = speechToTextInstance.Validate(); DTSValidationStatus expected = DTSValidationStatus.VS_ISCORRUPT; Assert.AreEqual(expected, actual); Assert.AreEqual("[Error] SSIS Speech To Text: Output Column InputChannel has invalid property IAmInvalid.", events.errorMessages[0]); }
public void TestValidateOK() { 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(); speechToText.CustomPropertyCollection[Utility.SubscriptionKeyPropName].Value = "NotTheDefault"; DTSValidationStatus actual = speechToTextInstance.Validate(); DTSValidationStatus expected = DTSValidationStatus.VS_ISVALID; Assert.AreEqual(expected, actual); Assert.AreEqual(0, events.errorMessages.Count, "There are error messages"); }
/// <summary> /// This method is ivoked once, when the user double clicks on it at design time. /// </summary> /// <param name="dtsComponentMetadata"></param> /// <param name="serviceProvider"></param> public void Initialize(IDTSComponentMetaData100 dtsComponentMetadata, IServiceProvider serviceProvider) { // Save a reference to the components metadata and service provider _sp = serviceProvider; _md = dtsComponentMetadata; // Check model: if no model was specified, add it one now. IDTSCustomProperty100 model = null; try { model = dtsComponentMetadata.CustomPropertyCollection[ComponentConstants.PROPERTY_KEY_MODEL]; _model = JSONSourceComponentModel.LoadFromJson(model.Value.ToString()); } catch (Exception e) { // No model found. Add a new now. _model = new JSONSourceComponentModel(); model = dtsComponentMetadata.CustomPropertyCollection.New(); model.Name = ComponentConstants.PROPERTY_KEY_MODEL; model.Value = _model.ToJsonConfig(); } if (_md == null) { _md = (IDTSComponentMetaData100)_md.Instantiate(); } _virtualInputLane = dtsComponentMetadata.InputCollection[ComponentConstants.NAME_INPUT_LANE_PARAMS].GetVirtualInput(); }
public void Initialize(IDTSComponentMetaData100 dtsComponentMetadata, IServiceProvider serviceProvider) { this.serviceProvider = serviceProvider; this.metaData = dtsComponentMetadata; this.connectionService = (IDtsConnectionService)serviceProvider.GetService(typeof(IDtsConnectionService)); }
/// <summary> /// fires an SSIS event /// </summary> /// <param name="ComponentMetaData">componets SSIS metadata</param> /// <param name="eventType">event type</param> /// <param name="sqlType">sql type (always</param> /// <param name="description">SSIS event description</param> private static void Fire(IDTSComponentMetaData100 ComponentMetaData, Type eventType, int sqlType, string description) { bool cancel = false; switch (eventType) { case Type.Information: ComponentMetaData.FireInformation(sqlType, ComponentMetaData.Name, description, "", 0, ref cancel); break; case Type.Progress: throw new NotImplementedException("Progress messages are not implemented"); case Type.Warning: ComponentMetaData.FireWarning(sqlType, ComponentMetaData.Name, description, string.Empty, 0); break; case Type.Error: ComponentMetaData.FireError(sqlType, ComponentMetaData.Name, description, string.Empty, 0, out cancel); break; default: ComponentMetaData.FireError(sqlType, ComponentMetaData.Name, description, string.Empty, 0, out cancel); break; } }
public override IDTSComponentMetaData100 AddDestAdapter(IDTSPipeline100 pipeline, ConnectionManager destConnMgr, out IDTSDesigntimeComponent100 destDesignTimeComp) { if (String.IsNullOrEmpty(quotedTableName)) { throw new ArgumentException("Destination table name is empty"); } IDTSComponentMetaData100 destComp = pipeline.ComponentMetaDataCollection.New(); destComp.ComponentClassID = OLEDB_DEST_GUID; destComp.ValidateExternalMetadata = true; destDesignTimeComp = destComp.Instantiate(); destDesignTimeComp.ProvideComponentProperties(); destComp.Name = "OleDB Destination - Sql Server"; destDesignTimeComp.SetComponentProperty("AccessMode", 0); destDesignTimeComp.SetComponentProperty("OpenRowset", quotedTableName); // set connection destComp.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(destConnMgr); destComp.RuntimeConnectionCollection[0].ConnectionManagerID = destConnMgr.ID; // get metadata destDesignTimeComp.AcquireConnections(null); destDesignTimeComp.ReinitializeMetaData(); destDesignTimeComp.ReleaseConnections(); extCols = destComp.InputCollection[0].ExternalMetadataColumnCollection; return(destComp); }
public override IDTSComponentMetaData100 Initialize() { // create the odbc source IDTSComponentMetaData100 comp = base.Initialize(); //set connection properies _cm.Name = "ODBC Source Connection Manager"; _cm.ConnectionString = _src.ConnectionString; _cm.Description = _src.Description; //do not require Qualifier //cm.Qualifier = dbsrc.DBConnection.Qualifier; CManagedComponentWrapper dcomp = comp.Instantiate(); //set Component Custom Properties foreach (KeyValuePair <string, object> prop in _src.CustomProperties.CustomPropertyCollection.InnerArrayList) { dcomp.SetComponentProperty(prop.Key, prop.Value); } if (comp.RuntimeConnectionCollection.Count > 0) { comp.RuntimeConnectionCollection[0].ConnectionManagerID = _cm.ID; comp.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(_cm); } // Finalize Reinitialize(dcomp); return(comp); }
/// <summary> /// Rebuilds mapping of input and output columns /// (errors are corrected if possible) /// </summary> /// <param name="componentMetaData">SSIS components metadata</param> public void RebuildMappings(IDTSComponentMetaData100 componentMetaData, IsagEvents events) { IDTSInput100 input = componentMetaData.InputCollection[Constants.INPUT_NAME]; IDTSVirtualInput100 vInput = input.GetVirtualInput(); Dictionary <int, ColumnConfig> mappingsInput = new Dictionary <int, ColumnConfig>(); List <ColumnConfig> mappingsWithoutInputs = new List <ColumnConfig>(); List <ColumnConfig> newMappings = new List <ColumnConfig>(); if (this.ContainsWrongUsageType(vInput.VirtualInputColumnCollection, events)) { ComponentMetaDataTools.SetUsageTypeReadOnly(vInput); } //Writre existing mappings in 2 lists (one with input columns, one without) foreach (ColumnConfig config in this.ColumnConfigList) { if (config.HasInput) { mappingsInput.Add(config.InputColumnId, config); } else { mappingsWithoutInputs.Add(config); } } //Generate new mapping using SSIS input columns foreach (IDTSInputColumn100 inputCol in input.InputColumnCollection) { ColumnConfig config; if (mappingsInput.ContainsKey(inputCol.ID)) { config = mappingsInput[inputCol.ID]; config.InputColumnName = inputCol.Name; config.DataTypeInput = SqlCreator.GetSQLServerDataTypeFromInput(inputCol, config.IsGeometryDataType); } else { config = new ColumnConfig(inputCol.Name, SqlCreator.GetSQLServerDataTypeFromInput(inputCol, isGeometry: false), inputCol); } newMappings.Add(config); } //Add properties to the newly created mapping ColumnConfigList.Clear(); foreach (ColumnConfig config in newMappings) { ColumnConfigList.Add(config); } foreach (ColumnConfig config in mappingsWithoutInputs) { ColumnConfigList.Add(config); } this.Save(componentMetaData); }
public override IDTSComponentMetaData100 Initialize() { //create flat file destination component IDTSComponentMetaData100 comp = base.Initialize(); _cm.Name = $"FlatFile Destination Connection Manager {comp.ID}"; //Create a new FlatFileDestination component CManagedComponentWrapper dcomp = comp.Instantiate(); foreach (KeyValuePair <string, object> prop in _dst.CustomProperties.CustomPropertyCollection.InnerArrayList) { dcomp.SetComponentProperty(prop.Key, prop.Value); } /*Specify the connection manager for Src.The Connections class is a collection of the connection managers that have been added to that package and are available for use at run time*/ if (comp.RuntimeConnectionCollection.Count > 0) { comp.RuntimeConnectionCollection[0].ConnectionManagerID = _cm.ID; comp.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(_cm); } this.Reinitialize(dcomp); return(comp); }
public void TestValidateExtraCustomProperty() { 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(); speechToText.CustomPropertyCollection.New(); DTSValidationStatus actual = speechToTextInstance.Validate(); DTSValidationStatus expected = DTSValidationStatus.VS_ISCORRUPT; Assert.AreEqual(expected, actual); Assert.AreEqual("[Error] SSIS Speech To Text: There is either to many or not enough custom properties.", events.errorMessages[0]); }
public static void CustomizeComponentProperties(IDTSComponentMetaData100 componentMetaData) { //add IsIdentifier property to input columns IDTSInput100 input = componentMetaData.InputCollection[0]; foreach (IDTSInputColumn100 column in input.InputColumnCollection) { AddColumnIsIdentifierProperty(column); } //add output columns IDTSOutput100 output = componentMetaData.OutputCollection[0]; AddOutputColumn(output, "TableName", DataType.DT_WSTR, 255, 0, 0, 0); AddOutputColumn(output, "ColumnNameList", DataType.DT_WSTR, 4000, 0, 0, 0); AddOutputColumn(output, "ColumnValueList", DataType.DT_WSTR, 4000, 0, 0, 0); AddOutputColumn(output, "IdentifierCondition", DataType.DT_WSTR, 4000, 0, 0, 0); AddOutputColumn(output, "ExceptionLevel", DataType.DT_WSTR, 20, 0, 0, 0); AddOutputColumn(output, "StartTime", DataType.DT_DBTIMESTAMP, 0, 0, 0, 0); AddOutputColumn(output, "PackageName", DataType.DT_WSTR, 255, 0, 0, 0); AddOutputColumn(output, "TaskName", DataType.DT_WSTR, 255, 0, 0, 0); AddOutputColumn(output, "ComponentName", DataType.DT_WSTR, 255, 0, 0, 0); AddOutputColumn(output, "ErrorCode", DataType.DT_I4, 0, 0, 0, 0); AddOutputColumn(output, "ErrorDescription", DataType.DT_WSTR, 1000, 0, 0, 0); AddOutputColumn(output, "ErrorColumn", DataType.DT_I4, 0, 0, 0, 0); AddOutputColumn(output, "ErrorColumnName", DataType.DT_WSTR, 255, 0, 0, 0); }
public IDTSComponentMetaData100 AddComp_DerivedCol(string ComponentName, IDTSOutput100 outCols ) { // Create IDTSComponentMetaData100 Comp = dmp.ComponentMetaDataCollection.New(); Comp.ComponentClassID = "Microsoft.DerivedColumn"; // Instantiate CManagedComponentWrapper Inst = Comp.Instantiate(); Inst.ProvideComponentProperties(); Comp.Name = ComponentName; Comp.Description = "Derajw kolumn"; // Parametrize Comp.OutputCollection[0].TruncationRowDisposition = DTSRowDisposition.RD_NotUsed; Comp.OutputCollection[0].ErrorRowDisposition = DTSRowDisposition.RD_NotUsed; // Connect IDTSPath100 pth = dmp.PathCollection.New(); pth.AttachPathAndPropagateNotifications(outCols, Comp.InputCollection[0]); // Return return(Comp); }
private void ProcessDataFlow(MainPipe mainPipe, TaskHost taskHost) { int sortCount = 0; foreach (IDTSComponentMetaData100 comp in mainPipe.ComponentMetaDataCollection) { string key = PackageHelper.GetComponentKey(comp); if (!PackageHelper.ComponentInfos.ContainsKey(key)) { System.Diagnostics.Debug.WriteLine("DataFlowPerformancePractice ProcessDataFlow(113): Key not found '" + key + "'"); continue; } if (PackageHelper.ComponentInfos[key].CreationName == "DTSTransform.Sort.2") { sortCount++; //Trace the input IDTSComponentMetaData100 sourceComp = PackageHelper.TraceInputToSource(mainPipe, comp); if (sourceComp != null) { key = PackageHelper.GetComponentKey(sourceComp); if (PackageHelper.ComponentInfos[key].Name == "OLE DB Source" || PackageHelper.ComponentInfos[key].Name == "ADO NET Source") { Results.Add(new Result(false, string.Format("The {0} Sort transformation is operating on data provided from the {1} source. Rather than using the Sort transformation, which is fully blocking, the sorting should be performed using a WHERE clause in the source's SQL, and the IsSorted and SortKey properties should be set appropriately. Reference: http://msdn.microsoft.com/en-us/library/ms137653(SQL.90).aspx", comp.Name, sourceComp.Name), ResultSeverity.Normal)); } } } } if (sortCount > 2) { Results.Add(new Result(false, String.Format("There are {0} Sort transfomations in the {1} data flow. A large number of Sorts can slow down data flow performance. Consider staging the data to a relational database and sorting it there.", sortCount, taskHost.Name), ResultSeverity.Normal)); } }
public void TestExecuteRecogniseAsyncBasic() { 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(); speechToText.CustomPropertyCollection[Utility.SubscriptionKeyPropName].Value = config.AppSettings.Settings["subscriptionKey"].Value; Uri targetURI = new Uri(@"wss://speech.platform.bing.com/api/service/recognition/continuous"); SpeechToTextImplement testMe = new SpeechToTextImplement(speechToText, "en-us", targetURI, config.AppSettings.Settings["subscriptionKey"].Value); string filename = "Ex_Pro_1.mp3"; testMe.ExecuteRecogniseAsync(filename).Wait(); Assert.IsTrue(testMe.Results.Count > 0); }
public SSISOdbcSource(OdbcSource dbsrc, MainPipe pipe, ConnectionManager cm) : base(pipe, "ODBC Source") { // create the odbc source //set connection properies cm.Name = "ODBC Source Connection Manager"; cm.ConnectionString = dbsrc.ConnectionString; cm.Description = dbsrc.Description; //do not require Qualifier //cm.Qualifier = dbsrc.DBConnection.Qualifier; IDTSComponentMetaData100 comp = this.MetadataCollection; CManagedComponentWrapper dcomp = comp.Instantiate(); //set Component Custom Properties foreach (KeyValuePair <string, object> prop in dbsrc.CustomProperties.CustomPropertyCollection.InnerArrayList) { dcomp.SetComponentProperty(prop.Key, prop.Value); } if (comp.RuntimeConnectionCollection.Count > 0) { comp.RuntimeConnectionCollection[0].ConnectionManagerID = cm.ID; comp.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(cm); } // Finalize this.Reinitialize(dcomp); }
public IDTSComponentMetaData100 AddComp_Multicast(string ComponentName, IDTSOutput100 outCols ) { // Create IDTSComponentMetaData100 Comp = dmp.ComponentMetaDataCollection.New(); Comp.ComponentClassID = "Microsoft.Multicast"; // Instantiate CManagedComponentWrapper Inst = Comp.Instantiate(); Inst.ProvideComponentProperties(); Comp.Name = ComponentName; Comp.Description = "Dodany Sort"; // Connect IDTSPath100 pth = dmp.PathCollection.New(); pth.AttachPathAndPropagateNotifications(outCols, Comp.InputCollection[0]); // Return return(Comp); }
/// <summary> /// Updates input columns custom properties (GUIDs) /// </summary> /// <param name="input">the components input</param> /// <param name="componentMetaData">>the components metadata</param> /// <param name="isagCustomProperties">the components custom properties</param> private static void UpdateInputIdProperties(IDTSInput100 input, IDTSComponentMetaData100 componentMetaData, IsagCustomProperties isagCustomProperties) { foreach (IDTSInputColumn100 col in input.InputColumnCollection) { UpdateInputIdProperty(col, componentMetaData, isagCustomProperties); } }
public override IDTSComponentMetaData100 Connect(IDTSComponentMetaData100 src, int outputID = 0) { ConnectComponents(src, outputID); //add partitionfunction input SetPartitionFunctionInput(_src.Partition.Input); return(MetadataCollection); }
public SSISExcelSource(ExcelSource dbsrc, MainPipe pipe, ConnectionManager cm) : base(pipe, "Excel Source") { // create the oledb source //set connection properies cm.Name = "Excel Source Connection Manager"; cm.ConnectionString = dbsrc.ConnectionString; cm.Description = dbsrc.Description; //set connection properties //mwrt.IDTSConnectionManagerExcel100 ecm = cm.InnerObject as mwrt.IDTSConnectionManagerExcel100; //ecm.ExcelFilePath = dbsrc.FilePath; //ecm.FirstRowHasColumnName = dbsrc.Header; //ecm.ExcelVersionNumber = mwrt.DTSExcelVersion.DTSExcelVer_2007; IDTSComponentMetaData100 comp = this.MetadataCollection; CManagedComponentWrapper dcomp = comp.Instantiate(); foreach (KeyValuePair <string, object> prop in dbsrc.CustomProperties.CustomPropertyCollection.InnerArrayList) { dcomp.SetComponentProperty(prop.Key, prop.Value); } /*Specify the connection manager for Src.The Connections class is a collection of the connection managers that have been added to that package and are available for use at run time*/ if (comp.RuntimeConnectionCollection.Count > 0) { comp.RuntimeConnectionCollection[0].ConnectionManagerID = cm.ID; comp.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(cm); } // Finalize this.Reinitialize(dcomp); }
public SSISAdoNetSource(AdoNetSource dbsrc, MainPipe pipe, ConnectionManager cm) : base(pipe, "ADO NET Source") { // create the adonet source //set connection properies cm.Name = "AdoNet Source Connection Manager"; cm.ConnectionString = dbsrc.ConnectionString; cm.Description = dbsrc.Description; //cm.Qualifier = "System.Data.SqlClient.SqlConnection, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"; cm.Qualifier = dbsrc.DBConnection.Qualifier; IDTSComponentMetaData100 comp = this.MetadataCollection; CManagedComponentWrapper dcomp = comp.Instantiate(); //set Component Custom Properties foreach (KeyValuePair <string, object> prop in dbsrc.CustomProperties.CustomPropertyCollection.InnerArrayList) { dcomp.SetComponentProperty(prop.Key, prop.Value); } if (comp.RuntimeConnectionCollection.Count > 0) { comp.RuntimeConnectionCollection[0].ConnectionManagerID = cm.ID; comp.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(cm); } // Finalize this.Reinitialize(dcomp); }
public void TestAddNumberOfRowsOutputColumns() { 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 actual = textFileSplitter.OutputCollection.New(); ManageColumns.AddNumberOfRowsOutputColumns(actual); Assert.AreEqual(3, actual.OutputColumnCollection.Count, "Number of Columns is wrong"); Assert.AreEqual(MessageStrings.KeyValueColumnName, actual.OutputColumnCollection[0].Name, "Column Name is wrong"); Assert.AreEqual(DataType.DT_STR, actual.OutputColumnCollection[0].DataType, "DataType is wrong"); Assert.AreEqual(255, actual.OutputColumnCollection[0].Length, "Length is wrong"); Assert.AreEqual(MessageStrings.KeyValueColumnDescription, actual.OutputColumnCollection[0].Description, "Description is wrong"); Assert.AreEqual(MessageStrings.NumberOfRowsColumnName, actual.OutputColumnCollection[1].Name, "Column Name is wrong"); Assert.AreEqual(DataType.DT_I8, actual.OutputColumnCollection[1].DataType, "DataType is wrong"); Assert.AreEqual(0, actual.OutputColumnCollection[1].Length, "Length is wrong"); Assert.AreEqual(MessageStrings.NumberOfRowsColumnDescription, actual.OutputColumnCollection[1].Description, "Description is wrong"); Assert.AreEqual(MessageStrings.KeyValueStatusColumnName, actual.OutputColumnCollection[2].Name, "Column Name is wrong"); Assert.AreEqual(DataType.DT_STR, actual.OutputColumnCollection[2].DataType, "DataType is wrong"); Assert.AreEqual(255, actual.OutputColumnCollection[2].Length, "Length is wrong"); Assert.AreEqual(MessageStrings.KeyValueStatusColumnDescription, actual.OutputColumnCollection[2].Description, "Description is wrong"); }
public void TestSetOutputColumnDefaults() { 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(); IDTSOutputColumn100 actual = output.OutputColumnCollection.New(); ManageColumns.SetOutputColumnDefaults(actual, 1252); Assert.AreEqual(DataType.DT_STR, actual.DataType, "DataType is wrong"); Assert.AreEqual(255, actual.Length, "Length is wrong"); Assert.AreEqual(DTSRowDisposition.RD_NotUsed, actual.ErrorRowDisposition, "Row Disposition is wrong"); Assert.AreEqual(DTSRowDisposition.RD_NotUsed, actual.TruncationRowDisposition, "Truncate Disposition is wrong"); }
protected SSISModule(MainPipe pipe, string module_name, int module_id, string module_clsid) { //create SSIS component m_pipe = pipe; IDTSComponentMetaData100 comp = pipe.ComponentMetaDataCollection.New(); m_ID = comp.ID; Application app = new Application(); comp.ComponentClassID = (String.IsNullOrEmpty(module_clsid)) ? app.PipelineComponentInfos[module_name].CreationName : module_clsid; CManagedComponentWrapper dcomp = comp.Instantiate(); dcomp.ProvideComponentProperties(); //set common SSIS module properties if (module_id == 0) { comp.Name = String.Format(CultureInfo.InvariantCulture, "{0}", module_name); } else { comp.Name = String.Format(CultureInfo.InvariantCulture, "{0} - {1}", module_name, module_id); } PrintOutput.PrintToOutput(String.Format(CultureInfo.InvariantCulture, "DE added {0}", comp.Name), DERun.Debug); }
protected virtual void MatchInputColumns(Dictionary <string, int> converted, bool needschema) { IDTSComponentMetaData100 comp = this.MetadataCollection; CManagedComponentWrapper dcomp = comp.Instantiate(); IDTSInput100 input = comp.InputCollection[0]; IDTSVirtualInput100 vInput = input.GetVirtualInput(); IDTSVirtualInputColumnCollection100 vColumns = vInput.VirtualInputColumnCollection; IDTSExternalMetadataColumnCollection100 exColumns = input.ExternalMetadataColumnCollection; if (exColumns != null && exColumns.Count > 0) { bool hasMatch = false; foreach (IDTSExternalMetadataColumn100 exColumn in exColumns) { int inputColId; if (converted.ContainsKey(exColumn.Name.ToLower())) { inputColId = (int)converted[exColumn.Name.ToLower()]; } else { inputColId = FindVirtualInputColumnId(vColumns, exColumn.Name); } if (inputColId == 0) { //the column wasn't found if the Id is 0, so we'll print out a message and skip this row. PrintOutput.PrintToOutput("DE could not map external column " + exColumn.Name + ". Skipping column.", DERun.Debug); } else { // create input column IDTSInputColumn100 vCol = dcomp.SetUsageType(input.ID, vInput, inputColId, DTSUsageType.UT_READONLY); // and then we'll map it to the input row. dcomp.MapInputColumn(input.ID, vCol.ID, exColumn.ID); hasMatch = true; } } if (!hasMatch) { throw new InvalidArgumentException("Unable to map input to destination"); } } //if output schema is required and not provided else if (needschema) { //PrintOutput.PrintToError("No destination columns available"); throw new InvalidArgumentException("No destination columns available"); } //otherwise use virtual inputs else { foreach (IDTSVirtualInputColumn100 vColumn in vColumns) { // create input column for all virtual input columns dcomp.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY); } } }
public HDFSDestinationFormUI(IDTSComponentMetaData100 metaData, IServiceProvider serviceProvider) : this() { this.metaData = metaData; this.serviceProvider = serviceProvider; this.connectionService = (IDtsConnectionService)serviceProvider.GetService(typeof(IDtsConnectionService)); this.designTimeInstance = metaData.Instantiate(); }
public void Initialize(IDTSComponentMetaData100 dtsComponentMetadata, IServiceProvider serviceProvider) { ArgumentVerifier.CheckObjectArgument(dtsComponentMetadata, "componentMetadata"); ArgumentVerifier.CheckObjectArgument(serviceProvider, "serviceProvider"); this.componentMetadata = dtsComponentMetadata; this.serviceProvider = serviceProvider; this.connService = this.serviceProvider.GetService(typeof(Microsoft.SqlServer.Dts.Runtime.Design.IDtsConnectionService)) as Microsoft.SqlServer.Dts.Runtime.Design.IDtsConnectionService; }
public __ComponentMetadataWrapper(IDTSComponentMetaData100 dtsComponentMetaData100, Func<IUnitOfWork> dictionaryUnitOfWorkCallback) : base((object)dtsComponentMetaData100 == null ? null : dtsComponentMetaData100.CustomPropertyCollection) { if ((object)dtsComponentMetaData100 == null) throw new ArgumentNullException("dtsComponentMetaData100"); if ((object)dictionaryUnitOfWorkCallback == null) throw new ArgumentNullException("dictionaryUnitOfWorkCallback"); this.dtsComponentMetaData100 = dtsComponentMetaData100; this.dictionaryUnitOfWorkCallback = dictionaryUnitOfWorkCallback; }
public ReverseStringUIForm(IDTSComponentMetaData100 dtsComponentMetaData, IServiceProvider serviceProvider, Connections connections) { InitializeComponent(); // Store constructor parameters for later _dtsComponentMetaData = dtsComponentMetaData; // Get design-time interface for changes and validation _designTimeComponent = _dtsComponentMetaData.Instantiate(); // Get Input _input = _dtsComponentMetaData.InputCollection[0]; // Set any form controls that host component properties here // None required for ReverseString component // Populate DataGridView with columns SetInputVirtualInputColumns(); _connections = connections; // Get IDtsConnectionService and store. IDtsConnectionService dtsConnectionService = serviceProvider.GetService(typeof(IDtsConnectionService)) as IDtsConnectionService; _dtsConnectionService = dtsConnectionService; // Get Connections collection, and get name of currently selected connection. string connectionName = ""; if (_dtsComponentMetaData.RuntimeConnectionCollection[0] != null) { IDTSRuntimeConnection100 runtimeConnection = _dtsComponentMetaData.RuntimeConnectionCollection[0]; if (runtimeConnection != null && runtimeConnection.ConnectionManagerID.Length > 0 && _connections.Contains(runtimeConnection.ConnectionManagerID)) { connectionName = _connections[runtimeConnection.ConnectionManagerID].Name; } } // Populate connections combo. PopulateConnectionsCombo(this.cmbSqlConnections, Connection_Type, connectionName); }
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 Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSBuffer100 Clone(IDTSComponentMetaData100 pOwner) { return _innerObject.Clone(pOwner); }
internal BipsProxyIDTSComponentMetaData100(IDTSComponentMetaData100 innerObject) { _innerObject = innerObject; }
public void Instantiate(String bstrUserComponentTypeName, IDTSComponentMetaData100 pComponentMetaData, IDTSVariableDispenser100 pVariableDispenser, IDTSBufferManager100 pBufferManager, IDTSEventInfos100 pEventInfos, IDTSObjectReferenceTracker100 pRefTracker, IDTSLogEntryInfos100 pLogEntryInfos, out DTSProductLevel peProductLevel) { _innerObject.Instantiate(bstrUserComponentTypeName, pComponentMetaData, pVariableDispenser, pBufferManager, pEventInfos, pRefTracker, pLogEntryInfos, out peProductLevel); }
/// <summary> /// Initialize is called before Edit() to give us the component metadata for the /// component about to be edited, and an IServiceProvider where we can ask /// for common VS and SSIS services, like creating connection managers... (see below) /// </summary> public void Initialize(IDTSComponentMetaData100 dtsComponentMetadata, IServiceProvider serviceProvider) { // Save off the component metadata and the service provider interfaces for later... component_ = dtsComponentMetadata; serviceProvider_ = serviceProvider; needsNewMetaData_ = true; manualEditMode_ = false; }
public void Initialize(IDTSComponentMetaData100 dtsComponentMetadata, IServiceProvider serviceProvider) { this.DtsComponentMetaData100 = dtsComponentMetadata; this.ServiceProvider = serviceProvider; this.ComponentMetadataWrapper = new __ComponentMetadataWrapper(this.DtsComponentMetaData100, this.GetDictionaryUnitOfWork); }
public void NewDataFlow() { _lastComponent = null; }
public void Initialize(IDTSComponentMetaData100 dtsComponentMetadata, IServiceProvider serviceProvider) { this.ServiceProvider = serviceProvider; }
public static void RegisterOleDBConnection(SsisEmitterContext context, OleDBConnection oleDBConnection, IDTSComponentMetaData100 component) { oleDBConnection.Initialize(context); component.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(oleDBConnection.ConnectionManager); component.RuntimeConnectionCollection[0].ConnectionManagerID = oleDBConnection.ConnectionManager.ID; }
void IDtsComponentUI.Initialize(Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSComponentMetaData100 dtsComponentMetadata, IServiceProvider serviceProvider) { this.componentMetadata = dtsComponentMetadata; this.serviceProvider = serviceProvider; Debug.Assert(this.serviceProvider != null); this.errorCollector = this.serviceProvider.GetService(typeof(IErrorCollectionService)) as IErrorCollectionService; Debug.Assert(this.errorCollector != null); if (this.errorCollector == null) { throw new InvalidOperationException("Not all editing services available"); } }
private static void InitializeComponent(IDTSComponentMetaData100 cmd, ConnectionManager cm) { try { CManagedComponentWrapper cmcw = cmd.Instantiate(); cmd.RuntimeConnectionCollection[0].ConnectionManager = Microsoft.SqlServer.Dts.Runtime.DtsConvert.GetExtendedInterface(cm); cmd.RuntimeConnectionCollection[0].ConnectionManagerID = cm.ID; // aquire the runtime connection to the designtime instance of the component. cmcw.AcquireConnections(null); cmcw.ReinitializeMetaData(); cmd.RemoveInvalidInputColumns(); cmcw.ReleaseConnections(); } catch (Exception e) { Console.WriteLine("There is a problem with the connection manager {0}", cm.Name); Console.WriteLine("More than likely it could not connect to the database and reinitialize the correct columns"); Console.WriteLine("Make sure you have the ability to connect and that the connection managers in the template package are correct"); Console.WriteLine("Error message is below:"); Console.WriteLine(e.Message); System.Environment.Exit(0); } }
public void Initialize(IDTSComponentMetaData100 dtsComponentMetadata, IServiceProvider serviceProvider) { // Store ComponentMetaData for later use _dtsComponentMetaData = dtsComponentMetadata; _serviceProvider = serviceProvider; }
private void AddConvertComponent(Hashtable colConvertTable, Hashtable colConnectTable) { convertComp = pipeline.ComponentMetaDataCollection.New(); convertComp.ComponentClassID = DATACONVERT_GUID; convertComp.Name = "Data Conversion"; convertComp.ValidateExternalMetadata = true; convertDesignTimeComp = convertComp.Instantiate(); convertDesignTimeComp.ProvideComponentProperties(); IDTSInput100 cvtInput = convertComp.InputCollection[0]; IDTSOutput100 cvtOutput = convertComp.OutputCollection[0]; IDTSInputColumnCollection100 cvtInputCols = cvtInput.InputColumnCollection; IDTSOutput100 srcOutput = srcComp.OutputCollection[0]; pipeline.PathCollection.New().AttachPathAndPropagateNotifications(srcOutput, cvtInput); IDTSVirtualInput100 cvtVirInput = cvtInput.GetVirtualInput(); int i = 0; foreach (object key in colConvertTable.Keys) { int srcColID = (int)key; cvtVirInput.SetUsageType(srcColID, DTSUsageType.UT_READONLY); IDTSInputColumn100 cvtInputCol = cvtInputCols.GetInputColumnByLineageID(srcColID); if (cvtInputCol != null) { IDTSOutputColumn100 cvtOutputCol = convertDesignTimeComp.InsertOutputColumnAt(cvtOutput.ID, i++, "Convert_" + cvtInputCol.Name, ""); IDTSExternalMetadataColumn100 destCol = (IDTSExternalMetadataColumn100)colConvertTable[key]; convertDesignTimeComp.SetOutputColumnDataTypeProperties(cvtOutput.ID, cvtOutputCol.ID, destCol.DataType, destCol.Length, destCol.Precision, destCol.Scale, destCol.CodePage); // map output column and input column convertDesignTimeComp.SetOutputColumnProperty(cvtOutput.ID, cvtOutputCol.ID, "SourceInputColumnLineageID", srcColID); // add match table entry. colConnectTable.Add(cvtOutputCol.ID, destCol); } } }
public override void CreateNewOutputRows() { #region Set up verbose logging __metadata = ComponentMetaData; #endregion // See http://blogs.msdn.com/b/brian_jones/archive/2008/11/10/reading-data-from-spreadsheetml.aspx // http://openxmldeveloper.org/discussions/formats/f/14/p/5029/157797.aspx // http://blogs.msdn.com/b/ericwhite/archive/2010/07/21/table-markup-in-open-xml-spreadsheetml.aspx #region Configure Column Mapping this.MapColumns(); if (this._columnMappings.Count != ComponentMetaData.OutputCollection[0].OutputColumnCollection.Count) { string message = this._columnMappings.Count.ToString() + " column relationships have been set up, but the Script Source has " + ComponentMetaData.OutputCollection[0].OutputColumnCollection.Count.ToString() + " output columns defined."; ReportError(message, true); throw new ArgumentException(message); } VerboseLog(this._columnMappings.Count.ToString() + " column mappings defined."); #endregion #region Extract Excel file name from connection manager string workbookFileName = null; SpreadsheetDocument document = null; try { VerboseLog("Extracting Excel file name from connection manager."); string connectionString = ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager.ConnectionString; string[] connectionStringParts = connectionString.Split(';'); foreach (string connectionStringPart in connectionStringParts) { string[] pair = connectionStringPart.Split('='); if (pair[0] == "Data Source") { workbookFileName = pair[1]; VerboseLog("File name of '" + workbookFileName + "' identified in connection manager."); break; } } } #region catch ... catch (Exception ex) { ReportError("Unable to parse connection string: " + ex.Message, true); } #endregion #endregion #region Opening Excel file if (workbookFileName != null) { try { VerboseLog("Attempting to open Excel file."); document = SpreadsheetDocument.Open(workbookFileName, false); } #region catch ... catch (Exception ex) { ReportError("Unable to open '" + workbookFileName + "': " + ex.Message, true); } #endregion } VerboseLog("Excel file opened."); #endregion try { WorkbookPart workbook = document.WorkbookPart; SharedStringTablePart sharedStringTablePart = workbook.SharedStringTablePart; #region Unused code for finding ranges //ComponentMetaData.FireInformation(0, "", "Got WorkbookPart", "", 0, ref fireAgain); //#region Look at Ranges //bool foundRange = false; //RangeDef rangeDef = new RangeDef(); //foreach (DefinedName name in workbook.Workbook.GetFirstChild<DefinedNames>()) //{ // ComponentMetaData.FireInformation(0, "", "Looking at defined name '" + name.Name + "'", "", 0, ref fireAgain); // if (name.Name == this._rangeName) // { // ComponentMetaData.FireInformation(0, "", "Saving def", "", 0, ref fireAgain); // rangeDef.Name = name.Name; // string reference = name.InnerText; // ComponentMetaData.FireInformation(0, "", " reference: " + reference, "", 0, ref fireAgain); // rangeDef.Sheet = reference.Split('!')[0].Trim('\''); // string[] rangeArray = reference.Split('!')[1].Split('$'); // rangeDef.StartCol = rangeArray[1]; // rangeDef.StartRow = rangeArray[2].TrimEnd(':'); // rangeDef.EndCol = rangeArray[3]; // rangeDef.EndRow = rangeArray[4]; // foundRange = true; // break; // } //} //ComponentMetaData.FireInformation(0, "", "Done looking for defined names", "", 0, ref fireAgain); //if (foundRange) //{ // string rangeID = workbook.Workbook.Descendants<Sheet>().Where(r => r.Name.Equals(rangeDef.Sheet)).First().Id; // ComponentMetaData.FireInformation(0, "", "Got rangeID " + rangeID, "", 0, ref fireAgain); // WorksheetPart range = (WorksheetPart)workbook.GetPartById(rangeID); // ComponentMetaData.FireInformation(0, "", "Got Range", "", 0, ref fireAgain); //} //#endregion #endregion #region Iterate over sheets to find table // VerboseLog("Searching sheets for table '" + ExcelTableName + "'."); VerboseLog("Searching for sheet '" + ExcelSheetName + "'."); // Table table = null; Worksheet worksheet = null; foreach (Sheet sheet in workbook.Workbook.Sheets) { VerboseLog("Examining sheet '" + sheet.Name + "'."); WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheet.Id); if (sheet.Name == ExcelSheetName) { worksheet = worksheetPart.Worksheet; VerboseLog("Sheet found."); break; } //foreach (TableDefinitionPart tableDefinitionPart in worksheetPart.TableDefinitionParts) //{ // VerboseLog("Sheet contains table '" + tableDefinitionPart.Table.DisplayName + "'."); // if (tableDefinitionPart.Table.DisplayName == ExcelTableName) // { // worksheet = worksheetPart.Worksheet; // table = tableDefinitionPart.Table; // VerboseLog("Sheet and table found."); // break; // } //} //if (table != null) //{ // break; //} } #endregion //if (table == null) //{ // ReportError("Table '" + ExcelTableName + "' wasn't found in '" + workbookFileName + "'.", true); //} //else //{ // string firstColumnHeader = ""; // #region Find Excel Column Offsets // VerboseLog("Collecting column offsets for mapped columns."); // int columnIndex = 1; // foreach (TableColumn tableColumn in table.TableColumns) // { // if (columnIndex == 1) // { // firstColumnHeader = tableColumn.Name; // } // foreach (ColumnMapping columnRelationship in this._columnMappings) // { // if (tableColumn.Name == columnRelationship.ExcelColumnName) // { // VerboseLog("Found Excel column " + tableColumn.Name + " at offset " + columnIndex.ToString()); // columnRelationship.ExcelColumnOffset = columnIndex; // break; // } // } // columnIndex++; // } // #region Throw an error if not all columns were found // foreach (ColumnMapping columnRelationship in this._columnMappings) // { // if (!columnRelationship.ExcelColumnFound) // { // string message = "Unable to locate column '" + columnRelationship.ExcelColumnName + "' in table '" + ExcelTableName + "'."; // ReportError(message, true); // } // } // #endregion // #endregion if (worksheet == null) { ReportError("Sheet '" + ExcelSheetName + "' wasn't found in '" + workbookFileName + "'.", true); } else { string firstColumnHeader = ""; #region Read spreadsheet data into SSIS output buffer SheetData sheetData = worksheet.GetFirstChild<SheetData>(); IEnumerable<Row> rows = sheetData.Elements<Row>(); #region Find First Row UInt32 firstRow = 0; // VerboseLog("Finding first row of table."); VerboseLog("Finding first row of sheet."); foreach (Row row in rows) { foreach (Cell cell in row.Elements<Cell>()) { if (this.CellReferenceToCoordinates(cell.CellReference)[0] == 1) { if (this.GetCellValue(cell, sharedStringTablePart) == firstColumnHeader) { firstRow = row.RowIndex + 1; } } } } // VerboseLog("First row of table is on row " + firstRow.ToString() + "."); VerboseLog("First row of sheet is on row " + firstRow.ToString() + "."); #endregion // VerboseLog("Preparing to read " + rows.Count<Row>().ToString() + " table rows from Excel."); VerboseLog("Preparing to read " + rows.Count<Row>().ToString() + " sheet rows from Excel."); foreach (Row row in rows) { VerboseLog("Reading row " + row.RowIndex.ToString() + "."); if (row.RowIndex < firstRow) { VerboseLog("Skipping non-table or header row."); } else { VerboseLog("Reading data row " + (row.RowIndex - 1).ToString() + "."); bool rowAdded = false; foreach (Cell cell in row.Elements<Cell>()) { foreach (ColumnMapping columnRelationship in this._columnMappings) { if (this.CellReferenceToCoordinates(cell.CellReference)[0] == columnRelationship.ExcelColumnOffset) { string cellValue = this.GetCellValue(cell, sharedStringTablePart); if ((cellValue == null) || ((cellValue == "") && columnRelationship.TreatBlanksAsNulls)) { // do nothing } else { if (!rowAdded) { Output0Buffer.AddRow(); rowAdded = true; } VerboseLog("Excel column '" + columnRelationship.ExcelColumnName + "' contains '" + cellValue + "'."); columnRelationship.SetSSISBuffer(this.GetCellValue(cell, sharedStringTablePart)); } } } } } } #endregion } } #region catch ... catch (Exception ex) { ReportError("Unable to open Excel file using OpenXML API: " + ex.Message, true); } #endregion VerboseLog("Closing Excel file"); document.Close(); }
public void AddComponent(SsisComponent component) { if (_lastComponent != component.Component) { _lastComponent = component.Component; _componentList.Add(component); } }
public static string GetValidationErrorMessage(IDTSComponentMetaData100 componentMetaData) { // Validate that TableName is specified in a format of <schema>.<table> if (componentMetaData.CustomPropertyCollection["TableName"].Value == null) { return "Need to specify TableName in the Component Properties tab."; } else { string tableName = componentMetaData.CustomPropertyCollection["TableName"].Value.ToString(); if (!Regex.IsMatch(tableName, @"(.+)\.(.+)", RegexOptions.IgnoreCase)) { return "TableName needs to include both schema and table name."; } } // Validate that ErrorCode and ErrorColumn are included in the input selection int errorCodeFound = 0; int errorColumnFound = 0; foreach (IDTSInputColumn100 column in componentMetaData.InputCollection[0].InputColumnCollection) { string columnName = column.Name; if (Regex.IsMatch(columnName, "(.*?)ErrorCode$", RegexOptions.IgnoreCase)) errorCodeFound++; if (Regex.IsMatch(columnName, "(.*?)ErrorColumn$", RegexOptions.IgnoreCase)) errorColumnFound++; ; } if (errorCodeFound == 0) return "Need to include ErrorCode in the Input Columns tab."; else if (errorCodeFound > 1) return "Only one ErrorCode is allowed in the Input Columns tab."; if (errorColumnFound == 0) return "Need to include ErrorColumn in the Input Columns tab."; else if (errorColumnFound > 1) return "Only one ErrorColumn is allowed in the Input Columns tab."; // No validation errror return ""; }
public DataFlowComponentNodeFactory(IDTSComponentMetaData100 input, IDTSComponentMetaDataCollection100 metadata) { _input = input; _metadata = metadata; }
// Add this to the Validate method. This may impact performance so make sure to remove it from the Validation method after the upgrade is done. public static void OneTimeUpgrade(IDTSComponentMetaData100 componentMetaData) { bool isExist = false; isExist = false; foreach (IDTSCustomProperty100 customProperty in componentMetaData.CustomPropertyCollection) { if (customProperty.Name == "TableName") isExist = true; } if (!isExist) { IDTSCustomProperty100 TableName = componentMetaData.CustomPropertyCollection.New(); TableName.Name = "TableName"; TableName.Description = "Specifies the table name associated with the exception"; TableName.Value = (componentMetaData.Name.Split())[0].Replace("T@", "edfi."); } }
/// <summary> /// Initialize is called before Edit() to give us the component metadata for the /// component about to be edited, and an IServiceProvider where we can ask /// for common VS and SSIS services, like creating connection managers... (see below) /// </summary> public void Initialize(IDTSComponentMetaData100 dtsComponentMetadata, IServiceProvider serviceProvider) { // Save off the component metadata and the service provider interfaces for later... component_ = dtsComponentMetadata; serviceProvider_ = serviceProvider; }
public Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSBuffer100 CreateFlatBuffer64(UInt64 lSize, IDTSComponentMetaData100 pOwner) { return _innerObject.CreateFlatBuffer64(lSize, pOwner); }
public Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSBuffer100 CreateBuffer(Int32 hBufferType, IDTSComponentMetaData100 pOwner) { return _innerObject.CreateBuffer(hBufferType, pOwner); }
public void ConstructPackage(bool createNew) { // Create SSIS package package = new Microsoft.SqlServer.Dts.Runtime.Package(); // Add DataFlow task dataFlowTask = package.Executables.Add("STOCK:PipelineTask"); TaskHost taskhost = dataFlowTask as TaskHost; taskhost.Name = "Data Flow Task"; pipeline = taskhost.InnerObject as MainPipe; // Add source connection manager and adapter if (SourceProvider == null) { throw new InvalidOperationException("Empty source provider."); } else { srcConnMgr = SourceProvider.AddConnectionManager(package); srcComp = SourceProvider.AddSourceAdapter(pipeline, srcConnMgr); } // Add destination connection manager and adapter, create new table if asked to. if (DestProvider == null) { throw new InvalidOperationException("Empty destination provider."); } else { if (createNew) { DestProvider.CreateDestination(srcComp.OutputCollection[0].OutputColumnCollection); } destConnMgr = DestProvider.AddConnectionManager(package); destComp = DestProvider.AddDestAdapter(pipeline, destConnMgr, out destDesignTimeComp); } }