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 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]);
        }
        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.º 5
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.º 6
0
        public Package(AST.Task.AstPackageNode astNode)
            : base(astNode)
        {
            _DTSApplication        = new DTS.Application();
            DtsPackage             = new DTS.Package();
            DtsPackage.Name        = StringManipulation.NameCleaner(Name);
            PackageType            = String.IsNullOrEmpty(astNode.PackageType) ? "ETL" : astNode.PackageType;
            PackageFolder          = astNode.PackageFolder;
            PackagePath            = astNode.PackagePath;
            PackageProtectionLevel = astNode.ProtectionLevel;
            PackagePassword        = astNode.PackagePassword;

            // vsabella: We thought about adding this in the Lowering phase.
            // The reason this was not placed in Lowering is that this variable must be available
            // before any other lowering can take place.  Additionally i needed a single place where the
            // variable name could remain constant and other lowering phase engines could refer to it.
            // PreEmit
            PackageRootVariable =
                new Variable(PackagePathRootVariableName)
            {
                InheritFromPackageParentConfigurationString = "User::" + PackagePathRootVariableName,
                ValueString = PathManager.TargetPath,
                TypeCode    = TypeCode.String
            };

            Children.Add(PackageRootVariable);
        }
Exemplo n.º 7
0
        public Package(AST.Task.AstPackageNode astNode)
            : base(astNode)
        {
            _DTSApplication = new DTS.Application();
            DtsPackage = new DTS.Package();
            DtsPackage.Name = StringManipulation.NameCleaner(Name);
            PackageType = String.IsNullOrEmpty(astNode.PackageType) ? "ETL" : astNode.PackageType;
            PackageFolder = astNode.PackageFolder;
            PackagePath = astNode.PackagePath;
            PackageProtectionLevel = astNode.ProtectionLevel;
            PackagePassword = astNode.PackagePassword;

            // vsabella: We thought about adding this in the Lowering phase.
            // The reason this was not placed in Lowering is that this variable must be available
            // before any other lowering can take place.  Additionally i needed a single place where the
            // variable name could remain constant and other lowering phase engines could refer to it.
            // PreEmit
            PackageRootVariable =
                new Variable(PackagePathRootVariableName)
                {
                    InheritFromPackageParentConfigurationString = "User::" + PackagePathRootVariableName,
                    ValueString = PathManager.TargetPath,
                    TypeCode = TypeCode.String
                };

            Children.Add(PackageRootVariable);
        }
Exemplo n.º 8
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.º 9
0
        /// <summary>
        /// Constructs a graph given a name
        /// </summary>
        /// <param name="name">Name of package</param>
        /// <returns>Graph</returns>
        public static Graph CreateGraph(string name)
        {
            Logger.Common($"Creating graph {name}");
            Package package = _application.LoadPackage(NameToPath(name), null);

            return(new Graph(_application, package, name, _options));
        }
Exemplo n.º 10
0
        //========== END TAMPIL DATA DIMENSI DAN FAKTA ==========

        //========== ETL UPDATE DATA MART ==========
        private void btnUpdateETL_Click(object sender, EventArgs e)
        {
            try
            {
                splashScreenManager1.ShowWaitForm();
                Cursor.Current = Cursors.WaitCursor;
                string        lokasi = Environment.CurrentDirectory.ToString() + "\\Resources\\Package.dtsx";
                DTSExecResult pkgResults_Sql;
                Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
                Microsoft.SqlServer.Dts.Runtime.Package     pkg = new Microsoft.SqlServer.Dts.Runtime.Package();
                pkg            = app.LoadPackage(lokasi, null);
                pkgResults_Sql = pkg.Execute();
                splashScreenManager1.CloseWaitForm();
                MessageBox.Show("Data berhasil di-Update.", "Pesan", MessageBoxButtons.OK, MessageBoxIcon.Information);
                TampilData();
            }
            catch (System.Exception f)
            {
                MessageBox.Show(f.Message);
            }
            finally
            {
                Cursor.Current = Cursors.Default;
            }
        }
        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");
        }
Exemplo n.º 12
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);
        }
Exemplo n.º 13
0
        public SsisPackage(Package logicalPackage, SSISEmitterContext context) : base(logicalPackage, context)
        {
            _DTSApplication  = new DTS.Application();
            _DTSPackage      = new DTS.Package();
            _DTSPackage.Name = Name.Replace(".", "_").Replace("[", " ").Replace("]", " ");
            _projectManager  = new SsisProject(this);

            CurrentPackage = this;
        }
Exemplo n.º 14
0
        private void RunPackage(Extraction extraction, ExtractionJob job, ExtractionTrigger extractionTrigger)
        {
            var packagePath = extraction.PackagePath + extraction.PackageName + ".dtsx";

            dts.Package package = _app.LoadPackage(packagePath, null);

            Log.WriteErrorLog(string.Format("Starting package execution for {0}-{1}.", _serviceName, extraction.Title));

            //Update tigger to Running
            extractionTrigger.Status = PackageStatus.Running;
            extractionTrigger.Remark = "Package currently running...";
            _dataManager.UpdateExtractionTrigger(_connectionString, extractionTrigger);
            //DD-mon-YYYY
            extraction.ScriptText = extraction.ScriptText.Replace("@StartDate", job.StartDate.GetOracleDate());
            extraction.ScriptText = extraction.ScriptText.Replace("@EndDate", job.EndDate.GetOracleDate());
            extraction.ScriptText = extraction.ScriptText.Replace("@FYYear", string.Format("FY{0}", job.EndDate.Year.ToString()));
            extraction.ScriptText = extraction.ScriptText.Replace("@Year", string.Format("{0}", job.EndDate.Year.ToString()));
            extraction.ScriptText = extraction.ScriptText.Replace("@Period", string.Format("M{0}", job.EndDate.Month.ToString().PadLeft(2, '0')));

            package.Variables["StorProc"].Value = extraction.ScriptText;//"Script", true, "", extraction.ScriptText);

            var result = package.Execute();

            if (result == dts.DTSExecResult.Success)
            {
                int ssisDuration = 0;
                //int.TryParse(Math.Round((package.ExecutionDuration / 1000.00), 2).ToString(), out ssisDuration);

                ssisDuration = (package.ExecutionDuration / 1000);

                Log.WriteErrorLog(string.Format("Package execution for {0}-{1} successfull.", _serviceName, extraction.Title));

                //Update tigger to Done

                extractionTrigger.Status = PackageStatus.Done;
                //extractionTrigger.Remark = string.Format("{0}{1}lines extracted ({2})", string.Format("Package {0}-{1} successfully executed: ", _serviceName, extraction.Title), package.Variables["RecCount"].Value.ToString(), DateTimeExtensions.GetRealTime(ssisDuration));
                extractionTrigger.Remark = string.Format("{0}{1}lines extracted ({2})", string.Format("Package {0}-{1} successfully executed: ", _serviceName, extraction.Title), package.Variables["RecCount"].Value.ToString(), HHMMSS(ssisDuration));
                _dataManager.UpdateExtractionTrigger(_connectionString, extractionTrigger);
                //_Exp = _ProcessName & ": " & _ssisCount & " lines extracted (" & HHMMSS(_ssisDuration) & ")"
            }
            else
            {
                string errorMessage = string.Empty;
                foreach (var error in package.Errors)
                {
                    errorMessage += error.Description;
                }

                Log.WriteErrorLog(string.Format("Package execution for {0}-{1} failed.{2}", _serviceName, extraction.Title, errorMessage));

                //Update tigger to Fail
                extractionTrigger.Status = PackageStatus.Fail;
                extractionTrigger.Remark = string.Format("Package {0}-{1} failed.", _serviceName, extraction.Title) + "\n" + errorMessage;
                _dataManager.UpdateExtractionTrigger(_connectionString, extractionTrigger);
            }
        }
Exemplo n.º 15
0
        public void Reload()
        {
            this.Save();

            string        qualifiedProjectPath = QualifiedProjectPath;
            DirectoryInfo packageDirectoryInfo = System.IO.Directory.CreateDirectory(qualifiedProjectPath);

            qualifiedProjectPath = packageDirectoryInfo.FullName
                                   + _packageName
                                   + Resources.ExtensionDTSXProjectFile;

            this._package = _DTSApplication.LoadPackage(qualifiedProjectPath, null);
        }
Exemplo n.º 16
0
        public VulcanPackage(string packageName, string packageType, VulcanConfig vulcanConfig, TemplateManager templateManager, XPathNavigator packageNavigator)
        {
            this._packageName    = packageName;
            this._projectSubpath = GetSubpathFromPackageType(packageType);

            this._vulcanConfig    = vulcanConfig;
            this._templateManager = templateManager;
            this._projectManager  = new ProjectManager(packageName);

            _DTSApplication = new DTS.Application();
            _package        = new DTS.Package();

            this._package.Name     = this._packageName;
            this._packageNavigator = packageNavigator;

            this.UnSave();
        }
Exemplo n.º 17
0
        public VulcanPackage(string packageName, string packageType, VulcanConfig vulcanConfig, TemplateManager templateManager, XPathNavigator packageNavigator)
        {
            this._packageName = packageName;
            this._projectSubpath = GetSubpathFromPackageType(packageType);

            this._vulcanConfig = vulcanConfig;
            this._templateManager = templateManager;
            this._projectManager = new ProjectManager(packageName);

            _DTSApplication = new DTS.Application();
            _package = new DTS.Package();

            this._package.Name = this._packageName;
            this._packageNavigator = packageNavigator;

            this.UnSave();
        }
Exemplo n.º 18
0
        public void TestIDTSOutputCreator()
        {
            Microsoft.SqlServer.Dts.Runtime.Package package = new Microsoft.SqlServer.Dts.Runtime.Package();
            Executable exec = package.Executables.Add("STOCK:PipelineTask");

            Microsoft.SqlServer.Dts.Runtime.TaskHost thMainPipe = exec as Microsoft.SqlServer.Dts.Runtime.TaskHost;
            MainPipe dataFlowTask = thMainPipe.InnerObject as MainPipe;

            IDTSComponentMetaData100 textFileSplitter = dataFlowTask.ComponentMetaDataCollection.New();
            ComponentEventHandler    events           = new ComponentEventHandler();

            dataFlowTask.Events               = DtsConvert.GetExtendedInterface(events as IDTSComponentEvents);
            textFileSplitter.Name             = "Row Splitter Test";
            textFileSplitter.ComponentClassID = typeof(Martin.SQLServer.Dts.TextFileSplitter).AssemblyQualifiedName;
            CManagedComponentWrapper instance = textFileSplitter.Instantiate();

            instance.ProvideComponentProperties();

            IDTSOutput100 output = textFileSplitter.OutputCollection.New();

            output.Name = "New # Output";
            output.ErrorRowDisposition = DTSRowDisposition.RD_NotUsed;

            ManageProperties.AddOutputProperties(output.CustomPropertyCollection);

            ManageColumns.AddNumberOfRowsOutputColumns(output);

            ManageProperties propertyManager = new ManageProperties();

            propertyManager.PostErrorEvent += new PostErrorDelegate(this.PostError);
            errorMessages = new List <string>();

            SSISOutput actual = new SSISOutput(output, null);

            Assert.IsNotNull(actual, "SSISOutput is null");
            Assert.IsNotNull(actual.CustomPropertyCollection, "Custom Property Collection is null");
            Assert.IsNotNull(actual.OutputColumnCollection, "Column Collection is null");
            Assert.AreEqual(3, actual.CustomPropertyCollection.Count, "Custom Property Collection Count is Wrong");
            Assert.AreEqual(3, actual.OutputColumnCollection.Count, "Output Column Collection Count is Wrong");
            Assert.IsNotNull(ManageProperties.GetPropertyValue(actual.CustomPropertyCollection, ManageProperties.typeOfOutput));
            Assert.IsNotNull(ManageProperties.GetPropertyValue(actual.CustomPropertyCollection, ManageProperties.rowTypeValue));
            Assert.IsNotNull(ManageProperties.GetPropertyValue(actual.CustomPropertyCollection, ManageProperties.masterRecordID));
            Assert.AreEqual("_NewOutput", actual.Name, "Name is incorrect");
            Assert.AreEqual(DTSRowDisposition.RD_NotUsed, actual.ErrorRowDisposition, "Error Row Disposition is incorrect");
        }
Exemplo n.º 19
0
        public void ExecutePackage()
        {
            string strPackagePath = @"C:\jinshan\SSISWork\SSIS_World\ISExtensions\ISExtensions\winForm.dtsx";

            SSIS_Runtime.Application oApp = new SSIS_Runtime.Application();

            SSIS_Runtime.Package oPackage = new SSIS_Runtime.Package();

            oPackage = oApp.LoadPackage(strPackagePath, null);

            oPackage.Variables["Myvar"].Value = textBox1.Text.Trim();

            //oPackage.Variables["MyParameter"].Value = textBox2.Text.Trim();

            oPackage.Execute();

            MessageBox.Show("Package Executed!");
        }
Exemplo n.º 20
0
        public static void CreateSQLCEComponent(Microsoft.SqlServer.Dts.Runtime.Package package, MainPipe dataFlowTask, string sqlCEDatabaseName, string sqlCEPassword, String tableName, out ConnectionManager sqlCECM, out IDTSComponentMetaData100 sqlCETarget, out CManagedComponentWrapper sqlCEInstance)
        {
            // Add SQL CE Connection
            sqlCECM = package.Connections.Add("SQLMOBILE");
            sqlCECM.ConnectionString = connectionString(sqlCEDatabaseName, sqlCEPassword);
            sqlCECM.Name             = "SQLCE Destination " + tableName;

            sqlCETarget = dataFlowTask.ComponentMetaDataCollection.New();
            sqlCETarget.ComponentClassID = typeof(Microsoft.SqlServer.Dts.Pipeline.SqlCEDestinationAdapter).AssemblyQualifiedName;
            sqlCEInstance = sqlCETarget.Instantiate();
            sqlCEInstance.ProvideComponentProperties();
            sqlCETarget.Name = "SQLCE Target " + tableName;
            sqlCETarget.RuntimeConnectionCollection[0].ConnectionManager   = DtsConvert.GetExtendedInterface(sqlCECM);
            sqlCETarget.RuntimeConnectionCollection[0].ConnectionManagerID = sqlCECM.ID;

            sqlCETarget.CustomPropertyCollection["Table Name"].Value = tableName;
            sqlCEInstance.AcquireConnections(null);
            sqlCEInstance.ReinitializeMetaData();
            sqlCEInstance.ReleaseConnections();
        }
Exemplo n.º 21
0
        public static DtsExecutionResult RunPackage(string path, Dictionary <string, Object> parameters)
        {
            DtsExecutionResult result = null;

            Microsoft.SqlServer.Dts.Runtime.Application app     = new Microsoft.SqlServer.Dts.Runtime.Application();
            Microsoft.SqlServer.Dts.Runtime.Package     package = null;
            package = app.LoadPackage(path, null, false);
            var x = package.Variables;

            foreach (var item in x)
            {
                string p = item.Name;
                //Debug.Print(p.ToString());

                if (parameters.ContainsKey(p))
                {
                    item.Value = parameters[item.Name];
                }
            }
            Microsoft.SqlServer.Dts.Runtime.DTSExecResult results = package.Execute();
            if (results == DTSExecResult.Failure)
            {
                foreach (var item in package.Errors)
                {
                    string    s  = string.Format("{0}{1}{2}{1}{3}{1}{4}", item.Description, Environment.NewLine, item.ErrorCode, item.Source, item.SubComponent);
                    Exception ex = new Exception(s);
                    logger.Log(ex);
                }
            }
            result = new DtsExecutionResult(results);
            foreach (var item in package.Variables)
            {
                if (!item.SystemVariable)
                {
                    result.Add(item.Name, item.Value);
                }
            }
            return(result);
        }
Exemplo n.º 22
0
        public void TestChangeErrorOutputSetColumnProperty()
        {
            Microsoft.SqlServer.Dts.Runtime.Package package = new Microsoft.SqlServer.Dts.Runtime.Package();
            Executable exec = package.Executables.Add("STOCK:PipelineTask");

            Microsoft.SqlServer.Dts.Runtime.TaskHost thMainPipe = exec as Microsoft.SqlServer.Dts.Runtime.TaskHost;
            MainPipe dataFlowTask        = thMainPipe.InnerObject as MainPipe;
            ComponentEventHandler events = new ComponentEventHandler();

            dataFlowTask.Events = DtsConvert.GetExtendedInterface(events as IDTSComponentEvents);

            IDTSComponentMetaData100 textFileSplitter = dataFlowTask.ComponentMetaDataCollection.New();

            textFileSplitter.Name             = "Row Splitter Test";
            textFileSplitter.ComponentClassID = typeof(Martin.SQLServer.Dts.TextFileSplitter).AssemblyQualifiedName;
            CManagedComponentWrapper instance = textFileSplitter.Instantiate();

            instance.ProvideComponentProperties();

            IDTSOutput100 errorOutput = textFileSplitter.OutputCollection[1];

            ManageProperties.AddMissingOutputColumnProperties(errorOutput.OutputColumnCollection[0].CustomPropertyCollection);

            Boolean exceptionThrown = false;

            try
            {
                instance.SetOutputColumnProperty(errorOutput.ID, errorOutput.OutputColumnCollection[0].ID, ManageProperties.dotNetFormatString, String.Empty);
            }
            catch (COMException ex)
            {
                Assert.AreEqual(MessageStrings.CantChangeOutputProperties("Error"), ex.Message, "Exception Message Wrong");
                exceptionThrown = true;
            }

            Assert.IsTrue(exceptionThrown, "Exception Not Thrown");
        }
        private void RunPackage(Processes processes, ProcessJob job, ProcessTrigger processTrigger)
        {
            var packagePath = processes.PackagePath + processes.PackageName + ".dtsx";

            dts.Package package = _app.LoadPackage(packagePath, null);

            Log.WriteErrorLog(string.Format("Starting package execution for {0}---{1}.", _serviceName, processes.Title));

            //Update tigger to Running
            processTrigger.Status = PackageStatus.Running;
            processTrigger.Remark = "Package currently running...";
            _dataManager.UpdateProcessTrigger(_connectionString, processTrigger);

            var result = package.Execute();

            if (result == dts.DTSExecResult.Success)
            {
                int ssisDuration = 0;
                ssisDuration = (package.ExecutionDuration / 1000);
                // int.TryParse(Math.Round((package.ExecutionDuration / 1000.00), 2).ToString(), out ssisDuration);

                var statusMessage = string.Format("Package execution for {0}-{1} successfull.", _serviceName, processes.Title);

                try
                {
                    if (_currentProcess == Convert.ToInt32(processId) && Count() != 0)
                    {
                        //processTrigger.Remark = string.Format("Package {0}-{1} successfully executed,however cannot proceed with the next process ", _serviceName, processes.Title) + "Duration: (" + HHMMSS(ssisDuration) + ")";
                        processTrigger.Remark = string.Format("Package {0}-{1} successfully executed,however cannot proceed with the next process ", _serviceName, processes.Title) + "Duration- HH:MM:SS: (" + HHMMSS(ssisDuration) + ")";

                        processTrigger.Status = PackageStatus.Done;
                    }
                    else
                    {
                        processTrigger.Remark = string.Format("Package {0}-{1} successfully executed: ", _serviceName, processes.Title) + "Duration- HH:MM:SS: (" + HHMMSS(ssisDuration) + ")";
                        processTrigger.Status = PackageStatus.Done;
                    }
                    //processTrigger.Remark = string.Format("Package {0}-{1} successfully executed: ", _serviceName, processes.Title) + "Duration: (" + HHMMSS(ssisDuration) + ")";
                    //processTrigger.Status = PackageStatus.Done;

                    var message = package.Variables["Message"].Value.ToString();

                    if (!string.IsNullOrEmpty(message))
                    {
                        char firstLevelSeparator  = '/';
                        char secondLevelSeparator = '|';

                        var firstLevels  = message.Split(firstLevelSeparator);
                        var secondLevels = firstLevels[2].Split(secondLevelSeparator);

                        if (firstLevels[1] == "Failed")
                        {
                            statusMessage         = string.Format("Package execution for {0}-{1} for operation {2}  Fail.", _serviceName, processes.Title, firstLevels[0]) + "Duration- HH:MM:SS: (" + HHMMSS(ssisDuration) + ")";
                            processTrigger.Remark = string.Format("Package {0}-{1} for operation {2} execution failed: ", _serviceName, processes.Title, firstLevels[0]) + "Duration- HH:MM:SS: (" + HHMMSS(ssisDuration) + ")/n";

                            foreach (var s in secondLevels)
                            {
                                processTrigger.Remark += s + "/n";
                            }

                            processTrigger.Status = PackageStatus.Fail;
                            Log.WriteErrorLog(processTrigger.Remark);
                        }
                        else
                        {
                            //if (_redcount != 0)
                            //{

                            //    statusMessage = string.Format("Package execution for {0}-{1} for operation {2} successful.", _serviceName, processes.Title, firstLevels[0]) + "Duration: (" + HHMMSS(ssisDuration) + ")";
                            //    processTrigger.Remark = string.Format("Package {0}-{1} for operation {2} execution successful: ", _serviceName, processes.Title, firstLevels[0]) + "Duration: (" + HHMMSS(ssisDuration) + ")";

                            //}
                            //else
                            //{

                            //    statusMessage = string.Format("Package execution for {0}-{1} for operation {2} successful,however cannot proceed with the nest process", _serviceName, processes.Title, firstLevels[0]) + "Duration: (" + HHMMSS(ssisDuration) + ")";
                            //    processTrigger.Remark = string.Format("Package {0}-{1} for operation {2} execution successful:however cannot proceed with the nest process ", _serviceName, processes.Title, firstLevels[0]) + "Duration: (" + HHMMSS(ssisDuration) + ")";
                            //}

                            statusMessage         = string.Format("Package execution for {0}-{1} for operation {2} successful.", _serviceName, processes.Title, firstLevels[0]) + "Duration- HH:MM:SS: (" + HHMMSS(ssisDuration) + ")";
                            processTrigger.Remark = string.Format("Package {0}-{1} for operation {2} execution successful: ", _serviceName, processes.Title, firstLevels[0]) + "Duration- HH:MM:SS: (" + HHMMSS(ssisDuration) + ")";

                            foreach (var s in secondLevels)
                            {
                                processTrigger.Remark += s;
                            }

                            processTrigger.Status = PackageStatus.Done;
                            Log.WriteErrorLog(processTrigger.Remark);
                        }
                    }
                    else
                    {
                        Log.WriteErrorLog("Message variable is empty.");
                    }
                }
                catch (Exception ex)
                {
                    Log.WriteErrorLog(ex.Message);
                }

                Log.WriteErrorLog(statusMessage);

                //Update tigger to Done
                _dataManager.UpdateProcessTrigger(_connectionString, processTrigger);
                //_Exp = _ProcessName & ": " & _ssisCount & " lines extracted (" & HHMMSS(_ssisDuration) & ")"
            }
            else
            {
                string errorMessage = string.Empty;
                foreach (var error in package.Errors)
                {
                    errorMessage += error.Description;
                }

                Log.WriteErrorLog(string.Format("Package execution for {0}-{1} failed.", _serviceName, processes.Title));

                //Update tigger to Fail
                processTrigger.Status = PackageStatus.Fail;
                processTrigger.Remark = string.Format("Package {0}-{1} failed.", _serviceName, processes.Title) + "\n" + errorMessage;
                _dataManager.UpdateProcessTrigger(_connectionString, processTrigger);
            }
        }
Exemplo n.º 24
0
        public void Reload()
        {
            this.Save();

            string qualifiedProjectPath = QualifiedProjectPath;
            DirectoryInfo packageDirectoryInfo = System.IO.Directory.CreateDirectory(qualifiedProjectPath);

            qualifiedProjectPath = packageDirectoryInfo.FullName
                                    + _packageName
                                    + Resources.ExtensionDTSXProjectFile;

            this._package = _DTSApplication.LoadPackage(qualifiedProjectPath, null);
        }
Exemplo n.º 25
0
        public ActionResult DownloadBrewReport(String _BatchKey, bool CreateLogFile = false)
        {
            // Parameterhandling
            const string FILENAME_TYPE       = "xlsx";
            const string EXCEL_TEMPLATE_NAME = "ExcelReport_BatchBrew_Template" + "." + FILENAME_TYPE;
            const string SSIS_PACKAGE        = "ExcelReport_BatchBrew.dtsx";
            const string FOLDER_BASE_SOURCE  = "Areas\\PlantiT.Web.HeinekenMassafra.MES\\Excel";
            const string FOLDER_BIN          = "bin";
            const string FOLDER_TEMPLATE     = "template";
            const string FOLDER_BASE_OUTPUT  = "Areas\\PlantiT.Web.HeinekenMassafra.MES\\Excel\\output";
            const string LOG_FILE_NAME       = "DownloadBrewBatch_Log.txt";

            const int WAITTIME_IN_MS    = 1000;
            const int MAXWAITTIME_IN_MS = 30000;

            String sLocation = Assembly.GetExecutingAssembly().Location; // Get current directory

            Logger logger = null;

            long   nBatchKey      = 0;
            string sBatchName     = String.Empty;
            string sBatchNumber   = String.Empty;
            string sSAPMaterial   = String.Empty;
            string sSAP_Batch     = String.Empty;
            string sBrewLine      = String.Empty;
            string sShortMaterial = String.Empty;



            string sSourcePath          = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, FOLDER_BASE_SOURCE);
            string sDestinationPath     = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, FOLDER_BASE_OUTPUT);
            string sDestinationFilePath = String.Empty;
            string sFileName            = String.Empty;

            DateTime dtCheckTime = DateTime.Now;

            List <CBatch> listBatch = null;

            // Log generieren
            if (CreateLogFile)
            {
                logger = new Logger(Path.Combine(sDestinationPath, LOG_FILE_NAME), true);
            }


            // Batch Key ermitteln
            try
            {
                nBatchKey = Convert.ToInt64(_BatchKey);
            }
            catch
            {
                return(new HttpNotFoundResult(String.Format("Invalid BatchKey: {0}", _BatchKey)));
            }

            // Batchdaten ermitteln
            String odataURI = String.Format("odata/ods/ZWebSAPInterfaceStatuses?$select=_BatchKey,BatchName,SAPMaterial,SAP_Batch,BatchProcessStartTime&$filter=_BatchKey eq {0}", _BatchKey);

            PGatewayConnection gatewayConnection = DependencyResolver.Current.GetService(typeof(PGatewayConnection)) as PGatewayConnection;

            try
            {
                listBatch = gatewayConnection.ReadItem <CODataResponse <CBatch> >(odataURI).Value;
            }
            catch
            {
                return(new HttpNotFoundResult(String.Format("OData query invalid for BatchKey: {0}", _BatchKey)));
            }

            // Daten prüfen
            if (listBatch == null || listBatch.Where(x => x._BatchKey == nBatchKey).Count() == 0)
            {
                return(new HttpNotFoundResult(String.Format("Data not found for BatchKey: {0}", _BatchKey)));
            }

            // Namen von Auftrag ermitteln
            sBatchName     = listBatch.Where(x => x._BatchKey == nBatchKey).FirstOrDefault().BatchName;
            sBatchNumber   = sBatchName.Substring(0, 12).Substring(sBatchName.Substring(0, 12).Length - 4, 4);
            sSAPMaterial   = listBatch.Where(x => x._BatchKey == nBatchKey).FirstOrDefault().SAPMaterial;
            sSAP_Batch     = listBatch.Where(x => x._BatchKey == nBatchKey).FirstOrDefault().SAP_Batch;
            sBrewLine      = sSAP_Batch.Substring(5, 1);
            sShortMaterial = sSAPMaterial.Substring(sSAPMaterial.Length - 5);

            string FILENAME_PREFIX = (listBatch.Where(x => x._BatchKey == nBatchKey).FirstOrDefault().BatchProcessStartTime).ToString("yy");


            // BatchName prüfen
            if (sBatchName.Equals(String.Empty))
            {
                return(new HttpNotFoundResult(String.Format("No Batchname found for BatchKey: {0}", _BatchKey)));
            }



            // Nach Datei suchen
            sFileName = FILENAME_PREFIX + "_" + sBatchNumber + "_" + sBrewLine + "_1_" + sShortMaterial;


            Microsoft.SqlServer.Dts.Runtime.Application app     = new Microsoft.SqlServer.Dts.Runtime.Application();
            Microsoft.SqlServer.Dts.Runtime.Package     package = null;
            MyEventListener eventListener = new MyEventListener();

            //Load the SSIS Package which will be executed
            app.PackagePassword = "******";

            String sPackageFilePath = Path.Combine(sSourcePath, FOLDER_BIN, SSIS_PACKAGE);

            // Execute
            try
            {
                // Load package
                package = app.LoadPackage(sPackageFilePath, eventListener);


                // Set values
                Variables vars = package.Variables;

                vars["User::InputBatchKey"].Value         = nBatchKey;
                vars["User::InputDestinationFile"].Value  = sFileName + "." + FILENAME_TYPE;
                vars["User::InputDestinationPath"].Value  = sDestinationPath;
                vars["User::InputTemplateFilePath"].Value = Path.Combine(sSourcePath, FOLDER_TEMPLATE, EXCEL_TEMPLATE_NAME);
                vars["User::InputCreateLogFile"].Value    = CreateLogFile;
                vars["User::InputLogFilePath"].Value      = (logger != null) ? logger.FilePath : String.Empty;

                // Log erzeugen
                if (logger != null)
                {
                    logger.Log("InputBatchKey", vars["User::InputBatchKey"].Value.ToString());
                    logger.Log("InputDestinationFile", vars["User::InputDestinationFile"].Value.ToString());
                    logger.Log("InputDestinationPath", vars["User::InputDestinationPath"].Value.ToString());
                    logger.Log("InputTemplateFilePath", vars["User::InputTemplateFilePath"].Value.ToString());
                }


                vars["InputToOverwrite"].Value = true;

                DTSExecResult results = package.Execute(null, null, eventListener, null, null);
            }
            catch (Exception ex)
            {
                if (logger != null)
                {
                    logger.Log("Error", String.Format("Error: {0} // Innermessage: {1}", ex.Message, (ex.InnerException != null) ? ex.InnerException.Message : String.Empty));
                }

                return(new HttpNotFoundResult(String.Format("Error: {0} // Innermessage: {1}", ex.Message, (ex.InnerException != null) ? ex.InnerException.Message : String.Empty)));
            }

            // zyklisch prüfen ob Datei vorhanden ist, maximal  MAXWAITTIME_IN_MS
            for (int i = 0; i < MAXWAITTIME_IN_MS; i += WAITTIME_IN_MS)
            {
                // erstmal eine Sekunde zeit geben, zyklisch prüfen
                System.Threading.Thread.Sleep(WAITTIME_IN_MS);

                // alle Dateien des Pfades holen die dem Suchmuster entsprechen
                DirectoryInfo dirInfo     = new DirectoryInfo(Path.Combine(sDestinationPath));
                var           filesInPath = dirInfo.EnumerateFiles(String.Format("{0}*.{1}", sFileName, FILENAME_TYPE));

                var file = filesInPath.Where(x => x.Name.Contains(sFileName)).OrderByDescending(x => x.LastWriteTime).FirstOrDefault();

                // nur welche die eben erstellt wurde
                if (file == null)
                {
                    continue;
                }

                // Falls eben erstellt...
                if (file.LastWriteTime >= dtCheckTime)
                {
                    // gefunden, Abbruch
                    sFileName = file.Name;
                    break;
                }
            }

            // Pfad mit Datei erzeugen
            sDestinationFilePath = Path.Combine(sDestinationPath, sFileName);

            // Datei vorhanden?
            if (!System.IO.File.Exists(sDestinationFilePath))
            {
                return(new HttpNotFoundResult(String.Format("Excel file could not created for BatchKey: {0}", _BatchKey)));
            }

            // Create data return stream
            byte[] fileData    = System.IO.File.ReadAllBytes(sDestinationFilePath);
            string contentType = System.Web.MimeMapping.GetMimeMapping(sDestinationFilePath);

            // Datei entfernen
            System.IO.File.Delete(sDestinationFilePath);

            var cd = new System.Net.Mime.ContentDisposition()
            {
                // for example foo.bak
                FileName = sFileName,

                // always prompt the user for downloading, set to true if you want
                // the browser to try to show the file inline
                Inline = false,
            };

            Response.AppendHeader("Content-Disposition", cd.ToString());
            return(File(fileData, contentType));
        }
        private void LoadPackage()
        {
            string contents = String.Empty;

            Microsoft.SqlServer.Dts.Runtime.Package package = new Microsoft.SqlServer.Dts.Runtime.Package();

            using (StreamReader r = new StreamReader("DemoPackageWithoutDesignTimeProperties.dtsx"))
            {
                contents = r.ReadToEnd();
            }

            package.LoadFromXML(contents, null);

            ControlFlowGraphModelElement controlFlowGraphModelElement = new ControlFlowGraphModelElement();

            controlFlowGraphModelElement.Initialize(package as IDTSSequence);

            ModelElement m = controlFlowGraphModelElement.GetModelElement("{FFE5461A-385C-4A35-984A-91B6A8E43119}");

            GraphModelElement graphModelElement = new GraphModelElement();

            graphModelElement.Container = controlFlowGraphModelElement.Container;

            GraphLayout graphLayout = GraphLayout.GetLayout(graphModelElement);

            GraphControl graphControl = new GraphControl();

            graphControl.Width       = 1000;
            graphControl.Height      = 1000;
            graphControl.DataContext = m;

            GraphPanelEx p1 = new GraphPanelEx();

            p1.Width      = 1000;
            p1.Height     = 1000;
            p1.GraphModel = graphModelElement;
            p1.UpdateEx(true);

            GraphControlEx graphControlEx = new GraphControlEx();

            graphControlEx.DataContext = controlFlowGraphModelElement.Container;

            graphControlEx.AssignLayout(controlFlowGraphModelElement.Container, new Point(100, 100));
            graphControlEx.UpdateLayout();

            GraphModelElementEx graphModelElementEx = new GraphModelElementEx();

            graphModelElementEx.GraphControl = graphControl;
            graphModelElementEx.Container    = controlFlowGraphModelElement.Container;
            graphModelElementEx.RefreshGraph(controlFlowGraphModelElement.Container);

            GraphPanelEx graphPanelEx = new GraphPanelEx();

            graphPanelEx.Width        = 1000;
            graphPanelEx.Height       = 1000;
            graphPanelEx.DataContext  = graphModelElement;
            graphPanelEx.GraphModel   = graphModelElement;
            graphPanelEx.LayoutEngine = new Microsoft.SqlServer.Graph.LayoutEngine.Sugiyama.MsaglLayoutGraph();

            graphPanelEx.UpdateEx();

            //MethodInfo dynMethod = graphPanelEx.GetType().GetMethod("CalculateLayout", BindingFlags.NonPublic | BindingFlags.Instance);
            //dynMethod.Invoke(this, new object[] { false });

            graphLayout.ApplyLayout(graphModelElementEx);

            ContainerModelElementEx x = controlFlowGraphModelElement.Container as ContainerModelElementEx;
            var children = ContainerModelElementEx.GetAllChildren(controlFlowGraphModelElement.Container);
            var bounds   = ContainerModelElementEx.GetBounds(x.Children);

            graphModelElementEx.RefreshGraph(graphModelElementEx.Container);

            LayoutGraph layoutGraph = new LayoutGraph();

            graphLayout.AppendLayout(controlFlowGraphModelElement.Container.Children);
            graphLayout.ApplyLayout(controlFlowGraphModelElement);

            // Should contain the serialized GraphLayout XML Data with correct coordinates...
            var graphLayoutXml = SerializerHelper.Save(graphModelElement);
        }
Exemplo n.º 27
0
        static void Main(string[] args)
        {
            string testDTSX = "LargeExample.dtsx";

            string path = Path.Combine(SSISFolder, testDTSX);
            // Traverse through every single EDS and save a snapshot of it
            //foreach (EDSSettings EDS in SettingsStore.EDSSettings.Where(e => e.Name != "Template"))
            //{
            //    MetaDataSnapshot metaDataSnapshot = new MetaDataSnapshot(EDS.ConnectionString);

            //    Console.WriteLine(metaDataSnapshot);

            //    MetaDataStore.Provider.SaveSnapshot(EDS, metaDataSnapshot);
            //}


            // Key = EDS connection string
            Dictionary <string, DatabaseMetaChange> latestChanges = MetaDataStore.GetLatestChanges(SettingsStore.EDSSettings);

            _databaseMetaChange = latestChanges.Values.First();   //TODO: change this later.

            // Print out changes
            Console.WriteLine(_databaseMetaChange);

            // Create a new application to host packages, and load a package within it
            Application application = new Application();
            Package     package     = application.LoadPackage(path, null);

            Graph g = new Graph(application, package, testDTSX, new Options.Options())
            {
                Options = new Options.Options()
            };

            // Print attributes, which is used in graph
            List <Dictionary <string, Attribute> > list = g.AttributeTable.Values.SelectMany(d => d.Values).ToList();
            List <Attribute> attributes = list.SelectMany(d => d.Values).ToList();
            string           s          = string.Join("\n", attributes.Select(a => $"  -{a.Id}/{a.AttributeRef?.ID}: {a.Name}"));

            Console.WriteLine(s);

            // Iterate through all meta changes
            foreach (TableMetaChange tableMetaChange in _databaseMetaChange.Tables.Values)
            {
                foreach (ColumnMetaChange columnMetaChange in tableMetaChange.Columns.Values.Where(c => c.ColumnChanges > 0))
                {
                    foreach (ColumnChanges change in columnMetaChange.ListChanges())
                    {
                        g.Alter(columnMetaChange, change);
                    }
                }
            }

            // Validate package before saving it
            package.Validate(package.Connections, package.Variables, new PackageValidateErrorEvent(), null);

            // Save package
            application.SaveToXml(Path.Combine(TestOutputFolder, "packages", testDTSX), package, null);

            Console.WriteLine("Done");
            Console.ReadKey();
        }
Exemplo n.º 28
0
        public override object Execute(object project, Package package, DtsContainer container)
        {
#if SQL2012 || SQL2014 || SQL2017
            object returnValue;

            CommandParentType commandParentType = GetCommandParentType();

            try
            {
                OnCommandStarted(new CommandStartedEventArgs(DateTime.Now, CommandName, null, null, commandParentType));

                Parameter parameter;

                if (ParameterType == ParameterType.Project)
                {
                    Project currentProject = project as Project;

                    if (currentProject == null)
                    {
                        throw new Exception("The package's project was not loaded.");
                    }

                    parameter = currentProject.Parameters[ParameterName];

                    if (parameter == null)
                    {
                        throw new Exception(string.Format("The project parameter {0} could not be found.", ParameterName ?? "<NULL>"));
                    }
                }
                else
                {
                    if (package == null)
                    {
                        throw new ArgumentNullException("package", "The package was not passed correctly or loaded.");
                    }

                    parameter = package.Parameters[ParameterName];

                    if (parameter == null)
                    {
                        throw new Exception(string.Format("The package parameter {0} could not be found.", ParameterName ?? "<NULL>"));
                    }
                }

                if (Operation == VariableCommand.VariableOperation.Get)
                {
                    returnValue = parameter.Value;
                }
                else
                {
                    returnValue = parameter.Value = Convert.ChangeType(Value, parameter.DataType);
                }

                OnCommandCompleted(new CommandCompletedEventArgs(DateTime.Now, CommandName, null, null, string.Format("The {0} command has completed.", CommandName), commandParentType));
            }
            catch (Exception ex)
            {
                OnCommandFailed(new CommandFailedEventArgs(DateTime.Now, CommandName, null, null, ex.Message, commandParentType));

                throw;
            }

            return(returnValue);
#else
            return(null);
#endif
        }
Exemplo n.º 29
0
        public static void BuildSSISPackage(out Microsoft.SqlServer.Dts.Runtime.Package package, out IDTSComponentMetaData100 multipleHash, out CManagedComponentWrapper multipleHashInstance, out String lineageString, out MainPipe dataFlowTask, out Microsoft.SqlServer.Dts.Runtime.Application app)
        {
            package = new Microsoft.SqlServer.Dts.Runtime.Package();
            Executable exec = package.Executables.Add("STOCK:PipelineTask");

            Microsoft.SqlServer.Dts.Runtime.TaskHost thMainPipe = exec as Microsoft.SqlServer.Dts.Runtime.TaskHost;
            dataFlowTask = thMainPipe.InnerObject as MainPipe;
            ComponentEventHandler events = new ComponentEventHandler();

            dataFlowTask.Events = DtsConvert.GetExtendedInterface(events as IDTSComponentEvents);

            // Create a flat file source
            ConnectionManager flatFileConnectionManager = package.Connections.Add("FLATFILE");

            flatFileConnectionManager.Properties["Format"].SetValue(flatFileConnectionManager, "Delimited");
            flatFileConnectionManager.Properties["Name"].SetValue(flatFileConnectionManager, "Flat File Connection");
            flatFileConnectionManager.Properties["ConnectionString"].SetValue(flatFileConnectionManager, @".\TextDataToBeHashed.txt");
            flatFileConnectionManager.Properties["ColumnNamesInFirstDataRow"].SetValue(flatFileConnectionManager, false);
            flatFileConnectionManager.Properties["HeaderRowDelimiter"].SetValue(flatFileConnectionManager, "\r\n");
            flatFileConnectionManager.Properties["TextQualifier"].SetValue(flatFileConnectionManager, "\"");
            flatFileConnectionManager.Properties["DataRowsToSkip"].SetValue(flatFileConnectionManager, 0);
            flatFileConnectionManager.Properties["Unicode"].SetValue(flatFileConnectionManager, false);
            flatFileConnectionManager.Properties["CodePage"].SetValue(flatFileConnectionManager, 1252);

            // Create the columns in the flat file
            IDTSConnectionManagerFlatFile100       flatFileConnection = flatFileConnectionManager.InnerObject as IDTSConnectionManagerFlatFile100;
            IDTSConnectionManagerFlatFileColumn100 StringDataColumn   = flatFileConnection.Columns.Add();

            StringDataColumn.ColumnDelimiter     = ",";
            StringDataColumn.ColumnType          = "Delimited";
            StringDataColumn.DataType            = DataType.DT_STR;
            StringDataColumn.DataPrecision       = 0;
            StringDataColumn.DataScale           = 0;
            StringDataColumn.MaximumWidth        = 255;
            ((IDTSName100)StringDataColumn).Name = "StringData";
            IDTSConnectionManagerFlatFileColumn100 MoreStringColumn = flatFileConnection.Columns.Add();

            MoreStringColumn.ColumnDelimiter     = ",";
            MoreStringColumn.ColumnType          = "Delimited";
            MoreStringColumn.DataType            = DataType.DT_STR;
            MoreStringColumn.DataPrecision       = 0;
            MoreStringColumn.DataScale           = 0;
            MoreStringColumn.MaximumWidth        = 255;
            ((IDTSName100)MoreStringColumn).Name = "MoreString";
            IDTSConnectionManagerFlatFileColumn100 DateColumn = flatFileConnection.Columns.Add();

            DateColumn.ColumnDelimiter     = ",";
            DateColumn.ColumnType          = "Delimited";
            DateColumn.DataType            = DataType.DT_DATE;
            DateColumn.DataPrecision       = 0;
            DateColumn.DataScale           = 0;
            DateColumn.MaximumWidth        = 0;
            ((IDTSName100)DateColumn).Name = "DateColumn";
            IDTSConnectionManagerFlatFileColumn100 IntegerColumn = flatFileConnection.Columns.Add();

            IntegerColumn.ColumnDelimiter     = ",";
            IntegerColumn.ColumnType          = "Delimited";
            IntegerColumn.DataType            = DataType.DT_I4;
            IntegerColumn.DataPrecision       = 0;
            IntegerColumn.DataScale           = 0;
            IntegerColumn.MaximumWidth        = 0;
            ((IDTSName100)IntegerColumn).Name = "IntegerColumn";
            IDTSConnectionManagerFlatFileColumn100 NumericColumn = flatFileConnection.Columns.Add();

            NumericColumn.ColumnDelimiter     = "\r\n";
            NumericColumn.ColumnType          = "Delimited";
            NumericColumn.DataType            = DataType.DT_NUMERIC;
            NumericColumn.DataPrecision       = 15;
            NumericColumn.DataScale           = 2;
            NumericColumn.MaximumWidth        = 0;
            ((IDTSName100)NumericColumn).Name = "NumericColumn";

            app = new Microsoft.SqlServer.Dts.Runtime.Application();

            IDTSComponentMetaData100 flatFileSource = dataFlowTask.ComponentMetaDataCollection.New();

            flatFileSource.ComponentClassID = app.PipelineComponentInfos["Flat File Source"].CreationName;
            // Get the design time instance of the Flat File Source Component
            var flatFileSourceInstance = flatFileSource.Instantiate();

            flatFileSourceInstance.ProvideComponentProperties();

            flatFileSource.RuntimeConnectionCollection[0].ConnectionManager   = DtsConvert.GetExtendedInterface(flatFileConnectionManager);
            flatFileSource.RuntimeConnectionCollection[0].ConnectionManagerID = flatFileConnectionManager.ID;

            // Reinitialize the metadata.
            flatFileSourceInstance.AcquireConnections(null);
            flatFileSourceInstance.ReinitializeMetaData();
            flatFileSourceInstance.ReleaseConnections();
            flatFileSource.CustomPropertyCollection["RetainNulls"].Value = true;



            //[MD5BinaryOutput] varbinary(16), [MD5HexOutput] varchar(34), [MD5BaseOutput] varchar(24))";
            multipleHash = dataFlowTask.ComponentMetaDataCollection.New();
            multipleHash.ComponentClassID = typeof(Martin.SQLServer.Dts.MultipleHash).AssemblyQualifiedName;
            multipleHashInstance          = multipleHash.Instantiate();

            multipleHashInstance.ProvideComponentProperties();
            multipleHash.Name = "Multiple Hash Test";
            multipleHashInstance.ReinitializeMetaData();

            // Create the path from source to destination.
            StaticTestUtilities.CreatePath(dataFlowTask, flatFileSource.OutputCollection[0], multipleHash, multipleHashInstance);

            // Select the input columns.
            IDTSInput100        multipleHashInput  = multipleHash.InputCollection[0];
            IDTSVirtualInput100 multipleHashvInput = multipleHashInput.GetVirtualInput();

            foreach (IDTSVirtualInputColumn100 vColumn in multipleHashvInput.VirtualInputColumnCollection)
            {
                multipleHashInstance.SetUsageType(multipleHashInput.ID, multipleHashvInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
            }

            // Add the output columns
            // Generate the Lineage String
            lineageString = String.Empty;
            foreach (IDTSInputColumn100 inputColumn in multipleHashInput.InputColumnCollection)
            {
                if (lineageString == String.Empty)
                {
                    lineageString = String.Format("#{0}", inputColumn.LineageID);
                }
                else
                {
                    lineageString = String.Format("{0},#{1}", lineageString, inputColumn.LineageID);
                }
            }
        }
Exemplo n.º 30
0
        static void Main(string[] args)
        {
            // Dont try and make sense of any of this code....I still don't get it....this was a 2 day hack....seriously, rewrite it if you want to make it into a useful tool
            // WARNING: I catch general exceptions.  TA-DA!!
            int result = -2;
            OleDbConnection conn = null;
            OleDbCommand entire_excel_file = null;
            OleDbDataReader config_data_reader = null;
            // truncate bobby tables
            System.IO.StreamWriter bt = new StreamWriter("bobby_tables.sql");
            bt.Close();
            // populate the variables.
            num_cols = Enum.GetValues(typeof(ConfigColumnNames)).Length;
            //args = new string[] { "", @"C:\Users\koln01\Desktop\ESPN\AD_Scope\pip_config.xls" };
            app = new Microsoft.SqlServer.Dts.Runtime.Application();

            // parameter check, pretty lame one at that.
            if (args.Length != 1)
            {
                Console.WriteLine("This program takes a single argument.  The path to an Excel (xlsx) file with config parameters");
                System.Environment.Exit(0);
            }

            //query the excel file to get the entire result set so we can traverse it.
            string config_cs = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + args[0] + ";Extended Properties=\"Excel 12.0;HDR=YES\";";

            try
            {
                conn = new OleDbConnection(config_cs);
                conn.Open();
            }
            catch (Exception)
            {
                Console.WriteLine("There was an error opening the connection to the excel file you gave, make sure you gave the correct path to the correct config");
                Console.WriteLine("This is the path I have: {0}", args[0]);
                Console.WriteLine("IF that is the correct path, Make sure there isn't a lock on the file (ie. it is open by another program)");
                System.Environment.Exit(0);
            }

            try
            {
                entire_excel_file = new OleDbCommand("select * from [PIP$] where TableName is not NULL", conn);
                config_data_reader = entire_excel_file.ExecuteReader();
            }
            catch (Exception)
            {
                Console.WriteLine("I tried to query all of the info in PIP and I couldn't.");
                Console.WriteLine("Do you have a tab named 'PIP' in the config file?  PS: no trailing spaces or hidden chars?");
                Console.WriteLine("Do you have a header called 'TableName'?  PS: no trailing spaces or hidden chars?");
                System.Environment.Exit(0);
            }

            //begin the traversal of the excel file.  Keeping the current and previous so we know when we encounter a new table.
            string[] current_row = new string[num_cols];
            string[] previous_row = new string[num_cols];

            // hashtable that hashes to a hashtable with values of string...to map the column name to its respective datatypes.
            Dictionary<string, Dictionary<string, string>> hash = new Dictionary<string, Dictionary<string, string>>();
            Dictionary<string, Dictionary<string, string>> metas = new Dictionary<string, Dictionary<string, string>>();

            // Print all table that are about to be dropped and ask "are you sure?"
            int count = 0;
            string previous = "";
            string current = "";
            while (config_data_reader.Read())
            {
                current = config_data_reader[ConfigColumnNames.TableName.ToString()].ToString();
                if (config_data_reader["DropTable"].ToString().Trim().ToUpper() == "TRUE" && current != previous)
                {
                    Console.WriteLine("\t" + config_data_reader["TableName"].ToString());
                    count++;
                }
                previous = config_data_reader[ConfigColumnNames.TableName.ToString()].ToString();

            }
            if (count > 0)
            {
                Console.WriteLine("Um, you are about to drop all of these tables....are you sure you know what you are doing? [y/n/idk]");
                string response = Console.ReadLine().Trim().ToLower();

                while (response != "y" && response != "n" && response != "idk")
                {
                    Console.WriteLine("Invalid Response.  I am expecting one of these: y, n, idk");
                    response = Console.ReadLine().Trim().ToLower();
                }

                switch (response)
                {
                    case "y":
                        break;
                    case "n":
                        Console.WriteLine("Whew, that was close.");
                        System.Environment.Exit(0);
                        break;
                    case "idk":
                        Console.WriteLine("Well, then you probably shouldn't be doing this should you.");
                        System.Environment.Exit(0);
                        break;
                    default:
                        Console.WriteLine("Ummmm.....Sum Ting Wong.  You should never see dis.");
                        System.Environment.Exit(0);
                        break;
                }
                config_data_reader.Close();
            }

            // Start of the GUTS of PIP
            try
            {
                entire_excel_file = new OleDbCommand("select * from [PIP$] where TableName is not NULL", conn);
                config_data_reader = entire_excel_file.ExecuteReader();
            }
            catch (Exception)
            {
                Console.WriteLine("I tried to query all of the info in PIP and I couldn't.");
                Console.WriteLine("Do you have a tab named 'PIP' in the config file?  PS: no trailing spaces or hidden chars?");
                Console.WriteLine("Do you have a header called 'TableName'?  PS: no trailing spaces or hidden chars?");
                System.Environment.Exit(0);
            }
            while (config_data_reader.Read())
            {
                // this seems a bit retarded...because it is.  I don't know now to cast this to some form of an array that I can keep a previous and current on.
                // someone who knows the wonderful world of .NET could probably do this some justice.

                foreach (ConfigColumnNames col in Enum.GetValues(typeof(ConfigColumnNames)))
                {
                    string col_value = "";
                    try
                    {
                        col_value = config_data_reader[col.ToString()].ToString();
                    }
                    catch (Exception)
                    {
                        Console.WriteLine("More than likely, this column '{0}' doesn't exist in PIP config...and PIP expects it to.", col.ToString());
                        Console.WriteLine("Otherwise, if you are 100% sure the column exists EXACTLY as you see it spelled in this error, check for leading/trailing spaces");
                        Console.WriteLine("Before and after the column name or any phantom character that don't get displayed...If you have tried all of this and it STILL fails");
                        Console.WriteLine("This might be worthy of a bug report.");
                        System.Environment.Exit(0);

                    }
                    current_row[(int)col] = col_value;

                }
                if (current_row[(int)ConfigColumnNames.TableName] != previous_row[(int)ConfigColumnNames.TableName])
                {
                    #region "High Level Algo"
                    //TODO: this is where we build the create table script & populate the columns in the ssis package with the correct data types.
                    //This is also where we build the batchfile, using current_row as the list of parameters to obtain.
                    //Console.WriteLine("{0} is not equal to {1}", current_row[1], previous_row[1]);

                    // open the template package of currentrow[18]
                    // update flatfile connection manager with flatfile=currentrow[0]
                    // x = select * from [packagebot$] where TableName = currentrow[1]
                    // iterate through the result set.
                    // update flatfile columns
                    // update SSIS object model columns
                    // build create table script.  execute create table script.
                    // save and execute the package.
                    // load template package so we have something to work off of.
                    #endregion
                    if (previous_row[(int)ConfigColumnNames.TemplateSSISPackage] != null)
                    {
                        hash.Add(previous_row[(int)ConfigColumnNames.FieldName], new Dictionary<string, string>() {
                       {"SSIS", previous_row[(int)ConfigColumnNames.SSISFieldFormat]},
                       {"SQL", previous_row[(int)ConfigColumnNames.SqlFieldFormat]},
                       {"Default", previous_row[(int)ConfigColumnNames.ClientFieldFormat]},
                       {"rowdelim", ConvertHexToString(previous_row[(int)ConfigColumnNames.RowDelimiter])},
                       {"coldelim", ConvertHexToString(previous_row[(int)ConfigColumnNames.ColumnDelimiter])},
                       {"precision", previous_row[(int)ConfigColumnNames.Precision]},
                       {"scale", previous_row[(int)ConfigColumnNames.Scale]},
                       {"textqualified", previous_row[(int)ConfigColumnNames.Text_Qualifier]},
                       {"TruncationRowDisposition", previous_row[(int)ConfigColumnNames.TruncationRowDisposition]},
                       {"Description", previous_row[(int)ConfigColumnNames.Field_Comments]}});

                        //packagename = previous_row[27]; // this isn't the real index of package name....change it when you change the config file

                        // this needs to be an option in the config file
                        string connection_string = previous_row[(int)ConfigColumnNames.ConnectionString]; // this isn't the real value, need to change it.
                            //"Data Source=10.1.0.110;User ID=app_etl;Password=clt2010!;Initial Catalog=" + previous_row[22] + ";Provider=NZOLEDB;Persist Security Info=True;";
                        bool execute = false;

                        // check config file to see if we should drop this table
                        bool drop = false;
                        if (previous_row[(int)ConfigColumnNames.DropTable].ToUpper().Trim() == "TRUE")
                        {
                            drop = true;
                        }

                        // Drop Recreate Reload Table
                        if (previous_row[(int)ConfigColumnNames.Drop_Recreate_Reload].ToUpper().Trim() == "TRUE")
                        {
                            string tablename = previous_row[(int)ConfigColumnNames.TableName];
                            string new_name = tablename + "_TEMP";
                            string cmd = "ALTER TABLE " + tablename + " RENAME TO " + new_name + ";";
                            string cmd2 = "INSERT INTO " + tablename + " (select * from " + new_name + ");";
                            StreamWriter sw = new StreamWriter(new_name + ".sql");
                            sw.WriteLine(cmd);
                            sw.WriteLine("-- CREATE TABLE is called here...but I am reusing this script {0}", tablename + ".sql");
                            sw.WriteLine(cmd2);
                            sw.Close();

                            // Execute the Command: this will be in a file with tablename + _TEMP.sql in the current directory
                            OleDbConnection connection = new OleDbConnection(connection_string);
                            connection.Open();
                            OleDbCommand command = new OleDbCommand(cmd, connection);

                            // Rename Table
                            Console.Write("Creating Temp Table {0}_TEMP...", tablename);
                            try
                            {
                                result = command.ExecuteNonQuery();
                                if (result != -1)
                                {//FIXME: Really you should query the number of records in the table, check to see it that matches result
                                    Console.WriteLine("\nThe world will come to and end.  The DB should return -1 for this command and it returned {0}", result);
                                    Console.WriteLine("This was the statement '{0}'", command.CommandText);
                                    System.Environment.Exit(0);
                                }
                            }
                            catch (Exception e)
                            {
                                Console.Write("Failed.\n");
                                Console.WriteLine("Could not alter table {0}", tablename);
                                Console.WriteLine("Tried to execute this statement '{0}' --- Exception is below:", command.CommandText);
                                Console.WriteLine(e.Message);
                                System.Environment.Exit(0);
                            }
                            Console.Write("Done.\n");

                            // Create New Table
                            string tf = previous_row[(int)ConfigColumnNames.TableFooter].ToString();
                            CreateSqlTableScript(hash, metas, tablename,connection_string, tf, conn, true, false);
                            Console.Write("Inserting records into Table {0}...", tablename);
                            // Insert Old Records into new table
                            command = new OleDbCommand(cmd2, connection);
                            try
                            {
                                result = command.ExecuteNonQuery();
                                if (result < 0)
                                {//FIXME: Really you should query the number of records in the table, check to see it that matches result
                                    Console.WriteLine("\nBUG!!  DB Returned {0}", result);
                                    Console.WriteLine("This was the statement '{0}'", command.CommandText);
                                    System.Environment.Exit(0);
                                }

                            }
                            catch (Exception e)
                            {
                                Console.Write("\nFailed.\n");
                                Console.WriteLine("Could not insert records.");
                                Console.WriteLine("Tried to execute this statement '{0}' --- Exception is below:", command.CommandText);
                                Console.WriteLine(e.Message);
                                System.Environment.Exit(0);
                            }
                            Console.Write("Done.\nInserted {0} Records\n", result);

                            // Drop Temp Table
                            Console.Write("Dropping Temp Table {0}...", tablename);
                            try
                            {
                                command = new OleDbCommand("DROP TABLE " + tablename + "_TEMP;", connection);
                               result =  command.ExecuteNonQuery();
                               if (result != -1)
                               {//FIXME: Really you should query the number of records in the table, check to see it that matches result
                                   Console.WriteLine("\nBUG!!  DB Returned {0} Should have been -1", result);
                                   Console.WriteLine("This was the statement '{0}'", "DROP TABLE " + tablename + "_TEMP;");
                                   System.Environment.Exit(0);
                               }

                            }
                            catch (Exception e)
                            {
                                Console.Write("Failed.\n");
                                Console.WriteLine("Dropping {0}_TEMP Failed", tablename);
                                Console.WriteLine("Tried to execute this statement '{0}' --- Exception is below:", "DROP TABLE " + tablename + "_TEMP;");
                                Console.WriteLine(e.Message);
                                System.Environment.Exit(0);
                            }
                            Console.Write("Done.\n");

                            // Clean up
                            connection.Close();
                            sw.Close();
                        }

                        // check config file to see if we should execute this table
                        if (previous_row[(int)ConfigColumnNames.CreateTable].ToUpper().Trim() == "TRUE")
                        {
                            execute = true;
                            string tf = previous_row[(int)ConfigColumnNames.TableFooter];
                            CreateSqlTableScript(hash, metas, previous_row[(int)ConfigColumnNames.TableName], connection_string, tf, conn, execute, drop);
                        }
                        // modify the metadata structure
                        if (previous_row[(int)ConfigColumnNames.UpdateMetadata].ToUpper().Trim() == "TRUE")
                        {
                            string msource_connection_string = previous_row[(int)ConfigColumnNames.MetaDataConnectionString]; // this insn't the right index...needs to change
                            InsertMSourceEntity(msource_connection_string, previous_row);
                        }

                        // Build the package
                        if (previous_row[(int)ConfigColumnNames.BuildPackage].ToUpper().Trim() == "TRUE")
                        {
                            //reset the flat file connection manager with settings in the excel file
                            Console.Write("Building Package {0}.....", previous_row[(int)ConfigColumnNames.NewSSISPackage]);
                            p = app.LoadPackage(previous_row[(int)ConfigColumnNames.TemplateSSISPackage], null);
                            //GetDataFlowTask(p.Executables, p.Executables.GetEnumerator());
                            Microsoft.SqlServer.Dts.Runtime.Sequence seq = null;
                            Microsoft.SqlServer.Dts.Runtime.TaskHost th = null;

                            // looking for sequence container....this shit is very specific to package structure....I have a recursive function
                            // That can handle this...but not in this code.
                            try
                            {
                                seq = p.Executables["Sequence Container"] as Microsoft.SqlServer.Dts.Runtime.Sequence;
                            }
                            catch (Exception)
                            {
                                Console.WriteLine("Failed.\nCould not find the Sequence Container in the Package");
                                Console.WriteLine("the Sequence container MUST BE NAMED: 'Sequence Container'");
                                Console.WriteLine("Otherwise, you will always see this error message");
                                System.Environment.Exit(0);
                            }

                            // Looking for data flow task
                            try
                            {
                                th = seq.Executables["Data Flow Task"] as Microsoft.SqlServer.Dts.Runtime.TaskHost;
                            }
                            catch (Exception)
                            {
                                Console.WriteLine("Failed.\nCould not find the Data Flow Task in the Package");
                                Console.WriteLine("the Sequence container MUST BE NAMED: 'Data Flow Task'");
                                Console.WriteLine("Otherwise, you will always see this error message");
                                System.Environment.Exit(0);
                            }

                            // Error Checking
                            if (seq == null || th == null)
                            {
                                Console.WriteLine("Failed.\nSum Ting Wong.  You should never see this error message");
                                System.Environment.Exit(0);
                            }

                            data_flow_task = th.InnerObject as MainPipe;

                            try
                            {
                                cm_destination = p.Connections["destination"];
                            }
                            catch (Exception)
                            {
                                Console.WriteLine("Failed\nERROR: your destination connection manager MUST BE NAMED: 'destination'");
                                System.Environment.Exit(0);
                            }
                            // uncomment for packages with source component, should use a try catch
                            //cm_source = p.Connections["source"];
                            //p.PackagePassword = "******";
                            //p.Variables.Remove("filePathName");
                            //p.Variables.Add("filePathName", false,"User", previous_row[21]);
                            cm_destination.ConnectionString = connection_string;
                            string coldelim = ConvertHexToString(previous_row[(int)ConfigColumnNames.ColumnDelimiter]);
                            string rowdelim = ConvertHexToString(previous_row[(int)ConfigColumnNames.RowDelimiter]);
                            string ssis_var = "@[User::filePathName]";

                            // uncomment the flat file stuff when working with staging that deals with flatfiles
                            ResetFlatFileConnectionManager(ssis_var, rowdelim, coldelim, ssis_var, previous_row[(int)ConfigColumnNames.Text_Qualifier], p.Connections);

                            // reset the column properties in the FlatFile Source Component
                            IDTSConnectionManagerFlatFile100 cmff = cm_flat_file.InnerObject as IDTSConnectionManagerFlatFile100;
                            ResetFlatFileColumnProperties(cmff, hash);

                            // modify columns; types; reinitialize source and destination components; remap columns
                            string table = previous_row[(int)ConfigColumnNames.TableName].Trim().ToUpper();
                            ModifyComponents(hash, metas, table, table);
                            Console.Write("{0}\n", p.Validate(p.Connections, p.Variables, null, null));
                            try
                            {
                                app.SaveToXml(previous_row[(int)ConfigColumnNames.NewSSISPackage], p, null);
                            }
                            catch (Exception)
                            {
                                Console.WriteLine("Failed.\nCould not save the package. Is your path name in the config correct? I have '{0}'", previous_row[(int)ConfigColumnNames.NewSSISPackage]);
                                Console.WriteLine("Is this package '{0}' open somewhere else?", previous_row[(int)ConfigColumnNames.NewSSISPackage]);
                                Console.WriteLine("Do you have the correct permissions?");
                                Console.WriteLine("Is there something else going on that would cause a lock?");
                                System.Environment.Exit(0);

                            }
                        }
                        if (previous_row[(int)ConfigColumnNames.ExecutePackage].ToUpper().Trim() == "TRUE")
                        {
                            if (p == null)
                                p = app.LoadPackage(previous_row[(int)ConfigColumnNames.NewSSISPackage], null);
                            Console.Write("Executing Package {0}...", previous_row[(int)ConfigColumnNames.NewSSISPackage]);
                            Console.WriteLine(p.Execute());
                        }

                        hash = new Dictionary<string, Dictionary<string, string>>();
                        metas = new Dictionary<string, Dictionary<string, string>>();
                    } // END IF

                } // END IF
                else
                {
                    hash.Add(previous_row[(int)ConfigColumnNames.FieldName], new Dictionary<string, string>() {
                       {"SSIS", previous_row[(int)ConfigColumnNames.SSISFieldFormat]},
                       {"SQL", previous_row[(int)ConfigColumnNames.SqlFieldFormat]},
                       {"Default", previous_row[(int)ConfigColumnNames.ClientFieldFormat]},
                       {"rowdelim", ConvertHexToString(previous_row[(int)ConfigColumnNames.RowDelimiter])},
                       {"coldelim", ConvertHexToString(previous_row[(int)ConfigColumnNames.ColumnDelimiter])},
                       {"precision", previous_row[(int)ConfigColumnNames.Precision]},
                       {"scale", previous_row[(int)ConfigColumnNames.Scale]},
                       {"textqualified", previous_row[(int)ConfigColumnNames.Text_Qualifier]},
                       {"TruncationRowDisposition", previous_row[(int)ConfigColumnNames.TruncationRowDisposition]},
                       {"Description", previous_row[(int)ConfigColumnNames.Field_Comments]}});
                }

                // copy the values in current row to previous row.
                for (int i = 0; i < num_cols; i++)
                {
                    previous_row[i] = current_row[i];
                }
            } // END While

            Console.WriteLine("Done.");
            Console.ReadLine();
        }
Exemplo n.º 31
0
        public ActionResult DownloadBatchOverviewReport(String _TemplateKey, String _1stBatchKey, String _2ndBatchKey, String _BatchTypeKey, String _MaterialKey, String StartTime, String EndTime, bool CreateLogFile = false)
        {
            const string FILENAME_PREFIX = "BatchOverview_";
            //const string FILENAME_PREFIX = "BatchOverview";
            const string FILENAME_TYPE       = "xlsx";
            const string EXCEL_TEMPLATE_NAME = "ExcelReport_BatchOverview_Template.xlsx";
            const string SSIS_PACKAGE        = "ExcelReport_BatchOverview.dtsx";
            const string FOLDER_BASE_SOURCE  = "Areas\\PlantiT.Web.HeinekenMassafra.MES\\Excel";
            const string FOLDER_BIN          = "bin";
            const string FOLDER_TEMPLATE     = "template";
            const string FOLDER_BASE_OUTPUT  = "Areas\\PlantiT.Web.HeinekenMassafra.MES\\Excel\\output";
            //const string FOLDER_BASE_OUTPUT = "Areas\\PlantiT.Web.HeinekenMassafra.MES\\Excel\\output\\";
            const string LOG_FILE_NAME = "DownloadBatchOverview_Log.txt";


            const int WAITTIME_IN_MS    = 1000;
            const int MAXWAITTIME_IN_MS = 30000;

            String sLocation = Assembly.GetExecutingAssembly().Location; // Get current directory

            Logger logger = null;

            long     nTemplateKey  = 0;
            long     n1stBatchKey  = 0;
            long     n2ndBatchKey  = 0;
            long     nBatchTypeKey = 0;
            long     nMaterialKey  = 0;
            DateTime dtStartTime   = DateTime.MaxValue;
            DateTime dtEndTime     = DateTime.MinValue;

            string sSourcePath          = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, FOLDER_BASE_SOURCE);
            string sDestinationPath     = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, FOLDER_BASE_OUTPUT);
            string sDestinationFilePath = String.Empty;
            string sFileName            = String.Empty;

            DateTime dtCheckTime = DateTime.Now;

            // Log generieren
            if (CreateLogFile)
            {
                logger = new Logger(Path.Combine(sDestinationPath, LOG_FILE_NAME), true);
            }


            // Parameter ermitteln
            // TemplateKey
            try
            {
                nTemplateKey = Convert.ToInt64(_TemplateKey);
            }
            catch { }

            // 1stBatchKey
            try
            {
                n1stBatchKey = Convert.ToInt64(_1stBatchKey);
            }
            catch { }

            // 2ndBatchKey
            try
            {
                n2ndBatchKey = Convert.ToInt64(_2ndBatchKey);
            }
            catch { }

            // BatchTypeKey
            try
            {
                nBatchTypeKey = Convert.ToInt64(_BatchTypeKey);
            }
            catch { }

            // MaterialKey
            try
            {
                nMaterialKey = Convert.ToInt64(_MaterialKey);
            }
            catch { }

            // StartTime
            try
            {
                dtStartTime = Convert.ToDateTime(StartTime);
            }
            catch { }

            // EndTime
            try
            {
                dtEndTime = Convert.ToDateTime(EndTime);
            }
            catch { }



            // Nach Datei suchen
            sFileName = FILENAME_PREFIX + DateTime.Now.ToString().Replace(":", "").Replace('/', '.').Replace('-', '.');
            //sFileName = FILENAME_PREFIX;

            Microsoft.SqlServer.Dts.Runtime.Application app     = new Microsoft.SqlServer.Dts.Runtime.Application();
            Microsoft.SqlServer.Dts.Runtime.Package     package = null;
            MyEventListener eventListener = new MyEventListener();

            //Load the SSIS Package which will be executed
            app.PackagePassword = "******";

            String sPackageFilePath = Path.Combine(sSourcePath, FOLDER_BIN, SSIS_PACKAGE);

            // Execute
            try
            {
                // Load package
                package = app.LoadPackage(sPackageFilePath, eventListener);


                // Set values
                Variables vars = package.Variables;

                vars["User::InputTemplateKey"].Value  = nTemplateKey;
                vars["User::Input1stBatchKey"].Value  = n1stBatchKey;
                vars["User::Input2ndBatchKey"].Value  = n2ndBatchKey;
                vars["User::InputBatchTypeKey"].Value = nBatchTypeKey;
                vars["User::InputMaterialKey"].Value  = nMaterialKey;
                vars["User::InputStartTime"].Value    = dtStartTime;
                vars["User::InputEndTime"].Value      = dtEndTime;



                vars["User::InputDestinationFile"].Value  = sFileName + "." + FILENAME_TYPE;
                vars["User::InputDestinationPath"].Value  = sDestinationPath;
                vars["User::InputTemplateFilePath"].Value = Path.Combine(sSourcePath, FOLDER_TEMPLATE, EXCEL_TEMPLATE_NAME);
                vars["User::InputCreateLogFile"].Value    = CreateLogFile;
                vars["User::InputLogFilePath"].Value      = (logger != null) ? logger.FilePath : String.Empty;

                // Log erzeugen
                if (logger != null)
                {
                    logger.Log("InputTemplateKey", vars["User::InputTemplateKey"].Value.ToString());
                    logger.Log("Input1stBatchKey", vars["User::Input1stBatchKey"].Value.ToString());
                    logger.Log("Input2ndBatchKey", vars["User::Input2ndBatchKey"].Value.ToString());
                    logger.Log("InputBatchTypeKey", vars["User::InputBatchTypeKey"].Value.ToString());
                    logger.Log("InputMaterialKey", vars["User::InputMaterialKey"].Value.ToString());
                    logger.Log("InputStartTime", vars["User::InputStartTime"].Value.ToString());
                    logger.Log("InputEndTime", vars["User::InputEndTime"].Value.ToString());

                    logger.Log("InputDestinationFile", vars["User::InputDestinationFile"].Value.ToString());
                    logger.Log("InputDestinationPath", vars["User::InputDestinationPath"].Value.ToString());
                    logger.Log("InputTemplateFilePath", vars["User::InputTemplateFilePath"].Value.ToString());
                }


                vars["InputToOverwrite"].Value = true;

                DTSExecResult results = package.Execute(null, null, eventListener, null, null);
            }
            catch (Exception ex)
            {
                if (logger != null)
                {
                    logger.Log("Error", String.Format("Error: {0} // Innermessage: {1}", ex.Message, (ex.InnerException != null) ? ex.InnerException.Message : String.Empty));
                }

                return(new HttpNotFoundResult(String.Format("Error: {0} // Innermessage: {1}", ex.Message, (ex.InnerException != null) ? ex.InnerException.Message : String.Empty)));
            }

            // zyklisch prüfen ob Datei vorhanden ist, maximal  MAXWAITTIME_IN_MS
            for (int i = 0; i < MAXWAITTIME_IN_MS; i += WAITTIME_IN_MS)
            {
                if (package.Errors.Count > 0)
                {
                    foreach (var item in package.Errors)
                    {
                        if (logger != null)
                        {
                            logger.Log("Error", item.Description);
                        }
                    }
                    break;
                }

                // erstmal eine Sekunde zeit geben, zyklisch prüfen
                System.Threading.Thread.Sleep(WAITTIME_IN_MS);

                // alle Dateien des Pfades holen die dem Suchmuster entsprechen
                DirectoryInfo dirInfo     = new DirectoryInfo(Path.Combine(sDestinationPath));
                var           filesInPath = dirInfo.EnumerateFiles(String.Format("{0}*.{1}", sFileName, FILENAME_TYPE));

                var file = filesInPath.Where(x => x.Name.Contains(sFileName)).OrderByDescending(x => x.LastWriteTime).FirstOrDefault();

                // nur welche die eben erstellt wurde
                if (file == null)
                {
                    continue;
                }

                // Falls eben erstellt...
                if (file.LastWriteTime >= dtCheckTime)
                {
                    // gefunden, Abbruch
                    sFileName = file.Name;
                    break;
                }
            }

            // Pfad mit Datei erzeugen
            sDestinationFilePath = Path.Combine(sDestinationPath, sFileName);

            // Datei vorhanden?
            if (!System.IO.File.Exists(sDestinationFilePath))
            {
                return(new HttpNotFoundResult("Excel file could not created!"));
            }

            // Create data return stream
            byte[] fileData    = System.IO.File.ReadAllBytes(sDestinationFilePath);
            string contentType = System.Web.MimeMapping.GetMimeMapping(sDestinationFilePath);

            // Datei entfernen
            System.IO.File.Delete(sDestinationFilePath);

            var cd = new System.Net.Mime.ContentDisposition()
            {
                // for example foo.bak
                FileName = sFileName,

                // always prompt the user for downloading, set to true if you want
                // the browser to try to show the file inline
                Inline = false,
            };

            Response.AppendHeader("Content-Disposition", cd.ToString());
            return(File(fileData, contentType));
        }
Exemplo n.º 32
0
 public static void BuildSSISPackage(out Microsoft.SqlServer.Dts.Runtime.Package package, out IDTSComponentMetaData100 multipleHash, out CManagedComponentWrapper multipleHashInstance, out String lineageString, out MainPipe dataFlowTask)
 {
     Microsoft.SqlServer.Dts.Runtime.Application app;
     BuildSSISPackage(out package, out multipleHash, out multipleHashInstance, out lineageString, out dataFlowTask, out app);
 }
        public void TestProvideComponentProperties()
        {
            Microsoft.SqlServer.Dts.Runtime.Package package = new Microsoft.SqlServer.Dts.Runtime.Package();
            Executable exec = package.Executables.Add("STOCK:PipelineTask");

            Microsoft.SqlServer.Dts.Runtime.TaskHost thMainPipe = exec as Microsoft.SqlServer.Dts.Runtime.TaskHost;
            MainPipe dataFlowTask        = thMainPipe.InnerObject as MainPipe;
            ComponentEventHandler events = new ComponentEventHandler();

            dataFlowTask.Events = DtsConvert.GetExtendedInterface(events as IDTSComponentEvents);

            IDTSComponentMetaData100 speechToText = dataFlowTask.ComponentMetaDataCollection.New();

            speechToText.ComponentClassID = typeof(Martin.SQLServer.Dts.SSISSpeechToText).AssemblyQualifiedName;
            CManagedComponentWrapper speechToTextInstance = speechToText.Instantiate();

            speechToTextInstance.ProvideComponentProperties();

            int intExpected = 7;  // Add 1 to the number that the component added, as there is a custom MS one as well.
            int intActual   = speechToText.CustomPropertyCollection.Count;

            Assert.AreEqual(intExpected, intActual, "Custom Property Collection Count is wrong");

            IDTSCustomProperty100 cpActual = speechToText.CustomPropertyCollection[Utility.SubscriptionKeyPropName];

            Assert.AreEqual(Utility.SubscriptionKeyPropName, cpActual.Name, "Property name is wrong");
            Assert.AreEqual(DTSCustomPropertyExpressionType.CPET_NOTIFY, cpActual.ExpressionType, "Expression type on SubscriptionKeyPropName is wrong");

            cpActual = speechToText.CustomPropertyCollection[Utility.OperationModePropName];
            Assert.AreEqual(Utility.OperationModePropName, cpActual.Name, "Property name is wrong");
            Assert.AreEqual(DTSCustomPropertyExpressionType.CPET_NONE, cpActual.ExpressionType, "Expression type on OperationModePropName is wrong");
            Assert.AreEqual(typeof(SSISSpeechToText.OperationModeEnum).AssemblyQualifiedName, cpActual.TypeConverter, "Type Converter on OperationModePropName is wrong");

            cpActual = speechToText.CustomPropertyCollection[Utility.LanguagePropName];
            Assert.AreEqual(cpActual.Name, Utility.LanguagePropName, "Property name is wrong");
            Assert.AreEqual(cpActual.ExpressionType, DTSCustomPropertyExpressionType.CPET_NONE, "Expression type on LanguagePropName is wrong");
            Assert.AreEqual(cpActual.TypeConverter, typeof(SSISSpeechToText.SpeechLanguageEnum).AssemblyQualifiedName, "Type Converter on LanguagePropName is wrong");

            cpActual = speechToText.CustomPropertyCollection[Utility.ChannelSeparationPropName];
            Assert.AreEqual(cpActual.Name, Utility.ChannelSeparationPropName, "Property name is wrong");
            Assert.AreEqual(cpActual.ExpressionType, DTSCustomPropertyExpressionType.CPET_NONE, "Expression type on ChannelSeparationPropName is wrong");
            Assert.AreEqual(cpActual.TypeConverter, typeof(SSISSpeechToText.ChannelSeparationEnum).AssemblyQualifiedName, "Type Converter on ChannelSeparationPropName is wrong");

            cpActual = speechToText.CustomPropertyCollection[Utility.ShortPhraseUrlPropName];
            Assert.AreEqual(Utility.ShortPhraseUrlPropName, cpActual.Name, "Property name is wrong");
            Assert.AreEqual(DTSCustomPropertyExpressionType.CPET_NOTIFY, cpActual.ExpressionType, "Expression type on ShortPhraseUrlPropName is wrong");
            Assert.AreEqual(string.Empty, cpActual.TypeConverter, "Type Converter on AuthenticationUriPropName is wrong");

            cpActual = speechToText.CustomPropertyCollection[Utility.LongPhraseUrlPropName];
            Assert.AreEqual(Utility.LongPhraseUrlPropName, cpActual.Name, "Property name is wrong");
            Assert.AreEqual(DTSCustomPropertyExpressionType.CPET_NOTIFY, cpActual.ExpressionType, "Expression type on LongPhraseUrlPropName is wrong");
            Assert.AreEqual(string.Empty, cpActual.TypeConverter, "Type Converter on AuthenticationUriPropName is wrong");

            intExpected = 1;
            intActual   = speechToText.InputCollection.Count;
            Assert.AreEqual(intExpected, intActual, "Input Collection Count is wrong");
            Assert.AreEqual(speechToText.InputCollection[0].Name, "Input", "Input Name is Wrong");
            Assert.AreEqual(speechToText.InputCollection[0].InputColumnCollection.Count, 0, "There are input columns");

            intExpected = 1;
            intActual   = speechToText.OutputCollection.Count;
            Assert.AreEqual(intExpected, intActual, "Output Collection Count is wrong");
            Assert.AreEqual(speechToText.OutputCollection[0].Name, "SpeechOutput", "Output Name is Wrong");
            Assert.AreEqual(speechToText.OutputCollection[0].OutputColumnCollection.Count, 3, "The number of output columns is wrong");

            Assert.AreEqual(speechToText.OutputCollection[0].OutputColumnCollection[Utility.OutputChannelColumnName].Name, Utility.OutputChannelColumnName, "Output column name is wrong");
            Assert.AreEqual(speechToText.OutputCollection[0].OutputColumnCollection[Utility.OutputChannelColumnName].DataType, DataType.DT_STR, "Output column OutputSpeechColumnName datatype is wrong");
            Assert.AreEqual((SSISSpeechToText.OutputTypeEnum)speechToText.OutputCollection[0].OutputColumnCollection[Utility.OutputChannelColumnName].CustomPropertyCollection[Utility.OutputColumnOutputTypePropName].Value, SSISSpeechToText.OutputTypeEnum.Channel, "Output column OutputChannelColumnName purpose is wrong");

            Assert.AreEqual(speechToText.OutputCollection[0].OutputColumnCollection[Utility.OutputSpeechColumnName].Name, Utility.OutputSpeechColumnName, "Output column name is wrong");
            Assert.AreEqual(speechToText.OutputCollection[0].OutputColumnCollection[Utility.OutputSpeechColumnName].DataType, DataType.DT_TEXT, "Output column OutputSpeechColumnName datatype is wrong");
            Assert.AreEqual((SSISSpeechToText.OutputTypeEnum)speechToText.OutputCollection[0].OutputColumnCollection[Utility.OutputSpeechColumnName].CustomPropertyCollection[Utility.OutputColumnOutputTypePropName].Value, SSISSpeechToText.OutputTypeEnum.Speech, "Output column OutputSpeechColumnName purpose is wrong");

            Assert.AreEqual(speechToText.OutputCollection[0].OutputColumnCollection[Utility.OutputTimecodeColumnName].Name, Utility.OutputTimecodeColumnName, "Output column name is wrong");
            Assert.AreEqual(speechToText.OutputCollection[0].OutputColumnCollection[Utility.OutputTimecodeColumnName].DataType, DataType.DT_DBTIME2, "Output column OutputTimecodeColumnName datatype is wrong");
            Assert.AreEqual((SSISSpeechToText.OutputTypeEnum)speechToText.OutputCollection[0].OutputColumnCollection[Utility.OutputTimecodeColumnName].CustomPropertyCollection[Utility.OutputColumnOutputTypePropName].Value, SSISSpeechToText.OutputTypeEnum.Timecode, "Output column OutputTimecodeColumnName purpose is wrong");
        }
Exemplo n.º 34
0
        public SsisPackage(Package logicalPackage, SSISEmitterContext context) : base (logicalPackage, context)
        {
            _DTSApplication = new DTS.Application();
            _DTSPackage = new DTS.Package();
            _DTSPackage.Name = Name.Replace(".", "_").Replace("[", " ").Replace("]", " ");
            _projectManager = new SsisProject(this);

            CurrentPackage = this;
        }
Exemplo n.º 35
0
 public override object Execute(Package package, DtsContainer container)
 {
     return(Execute((object)null, package, container));
 }