Exemplo n.º 1
0
        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);
        }
Exemplo n.º 2
0
        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));
        }
Exemplo n.º 3
0
        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);
        }
Exemplo n.º 6
0
        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));
        }
        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");
        }
Exemplo n.º 11
0
        /// <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();
        }
Exemplo n.º 12
0
        public void Initialize(IDTSComponentMetaData100 dtsComponentMetadata, IServiceProvider serviceProvider)
        {
            this.serviceProvider = serviceProvider;
              this.metaData = dtsComponentMetadata;

              this.connectionService = (IDtsConnectionService)serviceProvider.GetService(typeof(IDtsConnectionService));
        }
Exemplo n.º 13
0
        /// <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;
            }
        }
Exemplo n.º 14
0
        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);
        }
Exemplo n.º 17
0
        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);
        }
Exemplo n.º 18
0
        public void Initialize(IDTSComponentMetaData100 dtsComponentMetadata, IServiceProvider serviceProvider)
        {
            this.serviceProvider = serviceProvider;
            this.metaData        = dtsComponentMetadata;

            this.connectionService = (IDtsConnectionService)serviceProvider.GetService(typeof(IDtsConnectionService));
        }
        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]);
        }
Exemplo n.º 20
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);
        }
Exemplo n.º 21
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);
        }
Exemplo n.º 22
0
        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));
            }
        }
Exemplo n.º 23
0
        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);
        }
Exemplo n.º 26
0
 /// <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);
        }
Exemplo n.º 29
0
        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);
        }
Exemplo n.º 30
0
        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");
        }
Exemplo n.º 31
0
        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");
        }
Exemplo n.º 32
0
        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);
        }
Exemplo n.º 33
0
        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);
                }
            }
        }
Exemplo n.º 34
0
 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;
        }
Exemplo n.º 36
0
		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;
		}
Exemplo n.º 37
0
        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);
        }
Exemplo n.º 38
0
        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;
        }
Exemplo n.º 39
0
 public Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSBuffer100 Clone(IDTSComponentMetaData100 pOwner)
 {
     return _innerObject.Clone(pOwner);
 }
Exemplo n.º 40
0
 internal BipsProxyIDTSComponentMetaData100(IDTSComponentMetaData100 innerObject)
 {
     _innerObject = innerObject;
 }
Exemplo n.º 41
0
 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);
 }
Exemplo n.º 42
0
        /// <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);
 }
Exemplo n.º 44
0
 public void NewDataFlow()
 {
     _lastComponent = null;
 }
Exemplo n.º 45
0
 public void Initialize(IDTSComponentMetaData100 dtsComponentMetadata, IServiceProvider serviceProvider)
 {
     this.ServiceProvider = serviceProvider;
 }
Exemplo n.º 46
0
 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");
            }
        }
Exemplo n.º 48
0
 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);
     }
 }
Exemplo n.º 49
0
 public void Initialize(IDTSComponentMetaData100 dtsComponentMetadata, IServiceProvider serviceProvider)
 {
     // Store ComponentMetaData for later use
     _dtsComponentMetaData = dtsComponentMetadata;
     _serviceProvider = serviceProvider;
 }
Exemplo n.º 50
0
        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);
				}
			}
		}
Exemplo n.º 51
0
    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();
    }
Exemplo n.º 52
0
 public void AddComponent(SsisComponent component)
 {
     if (_lastComponent != component.Component)
     {
         _lastComponent = component.Component;
         _componentList.Add(component);
     }
 }
Exemplo n.º 53
0
        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 "";
        }
Exemplo n.º 54
0
 public DataFlowComponentNodeFactory(IDTSComponentMetaData100 input, IDTSComponentMetaDataCollection100 metadata)
 {
     _input = input;
     _metadata = metadata;
 }
Exemplo n.º 55
0
        // 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.");
            }
        }
Exemplo n.º 56
0
 /// <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;
 }
Exemplo n.º 57
0
 public Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSBuffer100 CreateFlatBuffer64(UInt64 lSize,
     IDTSComponentMetaData100 pOwner)
 {
     return _innerObject.CreateFlatBuffer64(lSize, pOwner);
 }
Exemplo n.º 58
0
 public Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSBuffer100 CreateBuffer(Int32 hBufferType,
     IDTSComponentMetaData100 pOwner)
 {
     return _innerObject.CreateBuffer(hBufferType, pOwner);
 }
Exemplo n.º 59
0
		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);
            }

		}