Ejemplo n.º 1
0
        private static void PackageCreation(string xml, SSISBuilder pkg, string processType)
        {
            XElement root    = XElement.Load(new StringReader(xml));
            XElement process = root.Element("Process");

            pkg.VerboseLogging = Convert.ToBoolean(process.Attribute("VerboseLogging").Value);
            foreach (XElement node in process.Elements())
            {
                //string stepName = steps.Attribute("Name").Value;
                if (node.Name != null && node.Name.ToString().ToUpper() == "CONNECTIONSET")
                {
                    CHEFMetaDataProcessConnectionSet con;
                    con = GetConnectionSet <CHEFMetaDataProcessConnectionSet>(node.ToString());
                    // for SQL Connection
                    if (con != null)
                    {
                        if (con.SQLConnection != null)
                        {
                            foreach (var oleCon in con.SQLConnection)
                            {
                                if (oleCon.key != string.Empty)
                                {
                                    if (oleCon.Encrypt != null && oleCon.Encrypt.ToUpper() == "FALSE")
                                    {
                                        oleCon.Encrypt = "False";
                                    }
                                    else
                                    {
                                        oleCon.Encrypt = "True";
                                    }

                                    if (oleCon.TrustedConnection != null && oleCon.TrustedConnection.ToUpper() == "FALSE")
                                    {
                                        ConnectionEncryption conEncryption = new ConnectionEncryption();
                                        string strPassword      = conEncryption.DecryptString(oleCon.EncryptedPassword);
                                        string connectionString = System.Configuration.ConfigurationManager.AppSettings["Database_Standard_Connection"].Replace("[DataBase_Name]", oleCon.DatabaseName).Replace("[Server_Name]", oleCon.ServerName).Replace("[Encrypt_Connection]", oleCon.Encrypt).Replace("[User_ID]", oleCon.UserID).Replace("[User_Password]", strPassword).Replace("[Encryption]", oleCon.Encrypt);
                                        pkg.AddConnection(new Connection()
                                        {
                                            Key = oleCon.key, ConnectionString = connectionString, ConnectionType = ConnectionTypes.OleDBConnection
                                        });
                                    }
                                    else
                                    {
                                        string connectionString = System.Configuration.ConfigurationManager.AppSettings["Database_Trusted_Connection"].Replace("[DataBase_Name]", oleCon.DatabaseName).Replace("[Server_Name]", oleCon.ServerName).Replace("[Encryption]", oleCon.Encrypt);
                                        pkg.AddConnection(new Connection()
                                        {
                                            Key = oleCon.key, ConnectionString = connectionString, ConnectionType = ConnectionTypes.OleDBConnection
                                        });
                                    }
                                }
                            }
                        }
                        if (con.SharePointListConnection != null)
                        {
                            foreach (var oleCon in con.SharePointListConnection)
                            {
                                if (oleCon.key != string.Empty)
                                {
                                    if (oleCon.UseWindowsAuthentication != null && oleCon.UseWindowsAuthentication.ToUpper() == "FALSE")
                                    {
                                        ConnectionEncryption conEncryption = new ConnectionEncryption();
                                        string strPassword      = conEncryption.DecryptString(oleCon.EncryptedPassword);
                                        string connectionString = "SiteURL=" + oleCon.SiteURL + ";UserID=" + oleCon.UserID + ";Password="******"TRUE")
                                    {
                                        connectionString = "UseDevelopmentStorage=true";
                                    }
                                    else
                                    {
                                        ConnectionEncryption conEncryption = new ConnectionEncryption();
                                        string strAccountKey = conEncryption.DecryptString(oleCon.EncryptedAccountKey);
                                        connectionString = "DefaultEndpointsProtocol=" + oleCon.DefaultEndpointsProtocol.Trim() + ";AccountName=" + oleCon.AccountName + ";AccountKey=" + strAccountKey;
                                    }
                                    pkg.AddConnection(new Connection()
                                    {
                                        Key = oleCon.key, ConnectionString = connectionString, ConnectionType = ConnectionTypes.TableStorageConnection
                                    });
                                }
                                else
                                {
                                    throw new Exception("Invalid TableStorage Connection");
                                }
                            }
                        }
                        // for File Connection
                        if (con.FileConnection != null)
                        {
                            foreach (var fileCon in con.FileConnection)
                            {
                                //not able to make two different file connection
                                if (fileCon.key != string.Empty)
                                {
                                    pkg.AddConnection(new Connection()
                                    {
                                        Key = fileCon.key, ConnectionString = fileCon.FileName, ConnectionType = ConnectionTypes.FileConnection
                                    });
                                }
                            }
                        }
                        // for FlatFile  Connection
                        if (con.FlatFileConnection != null)
                        {
                            foreach (var flatFileCon in con.FlatFileConnection)
                            {
                                if (flatFileCon.key != string.Empty)
                                {
                                    pkg.AddConnection(new Connection()
                                    {
                                        Key = flatFileCon.key, ConnectionString = flatFileCon.FileName, ConnectionType = ConnectionTypes.FlatFileConnection
                                    });
                                }
                            }
                        }
                        // for FlatFile  Connection
                        if (con.SMTPConnection != null)
                        {
                            foreach (var smtpCon in con.SMTPConnection)
                            {
                                string smpt = string.Empty;
                                if (smtpCon.key != string.Empty)
                                {
                                    if (smtpCon.UseWindowsAuthentication == string.Empty || smtpCon.UseWindowsAuthentication == null)
                                    {
                                        smtpCon.UseWindowsAuthentication = "False";
                                    }
                                    if (smtpCon.EnableSsl == string.Empty || smtpCon.EnableSsl == null)
                                    {
                                        smtpCon.EnableSsl = "False";
                                    }
                                    smpt = "SmtpServer=" + smtpCon.SmtpServer + ";UseWindowsAuthentication=" + smtpCon.UseWindowsAuthentication + ";EnableSsl=" + smtpCon.EnableSsl + ";";
                                    pkg.AddConnection(new Connection()
                                    {
                                        Key = smtpCon.key, ConnectionString = smpt, ConnectionType = ConnectionTypes.SMTPConnection
                                    });
                                }
                            }
                        }
                    }
                }
                else if (node.Name != null && node.Name.ToString().ToUpper() == "VARIABLES")
                {
                    foreach (var variableNode in node.Elements())
                    {
                        CHEFMetaDataProcessVariablesVariable variable;
                        variable = GetVariableSet <CHEFMetaDataProcessVariablesVariable>(variableNode.ToString());
                        if (variable != null)
                        {
                            pkg.SetVariableValue(variable.Name, variable.Value, variable.DataType);
                        }
                    }
                }
                else if (node.Name != null && node.Name.ToString().ToUpper() == "SETVARIABLES")
                {
                    SetVariables setvariable;

                    setvariable = GetSetVariableSet <SetVariables>(node.ToString());
                    if (setvariable != null)
                    {
                        pkg.SetVariableRuntime(setvariable.SetVariable, "Need To Complete", "");
                    }
                }
                else if (node.Name != null && node.Name.ToString().ToUpper() == "SENDMAILTASK")
                {
                    SendMailTask sendMailTask;
                    sendMailTask = GetSendEmailTaskSet <SendMailTask>(node.ToString());
                    if (sendMailTask != null)
                    {
                        pkg.AddSendMailTask(sendMailTask.Name, sendMailTask.SMTPServer, sendMailTask.From, sendMailTask.To, sendMailTask.CC, sendMailTask.BCC, sendMailTask.Subject, sendMailTask.MessageSource, sendMailTask.Attachments, sendMailTask.Priority, sendMailTask.MessageSourceType);
                    }
                }
                else if (node.Name != null && node.Name.ToString().ToUpper() == "STEP")
                {
                    //CHEFMetaDataProcessStep step;
                    //step = GetStepsSet<CHEFMetaDataProcessStep>(node.ToString());
                    string stepType = ProcessTypeNames.Staging.ToString();
                    Int32  stepID   = 0;
                    if (node.Attribute("TypeName") != null)
                    {
                        stepType = node.Attribute("TypeName").Value;
                    }

                    if (node.Attribute("ID") != null)
                    {
                        stepID = Convert.ToInt32(node.Attribute("ID").Value);
                    }
                    string stepName = node.Attribute("Name").Value;
                    foreach (var stepNode in node.Elements())
                    {
                        // This loop is for DataFlowTask
                        if (stepType == processType.ToString() && stepType != string.Empty && stepID >= startStepID)
                        {
                            if (stepNode.Name != null && stepNode.Name.ToString().ToUpper() == "VARIABLES")
                            {
                                foreach (var variableNode in stepNode.Elements())
                                {
                                    CHEFMetaDataProcessVariablesVariable variable;
                                    variable = GetVariableSet <CHEFMetaDataProcessVariablesVariable>(variableNode.ToString());
                                    if (variable != null)
                                    {
                                        pkg.SetVariableValue(variable.Name, variable.Value, variable.DataType);
                                    }
                                }
                            }
                            else if (stepNode.Name != null && stepNode.Name.ToString().ToUpper() == "SETVARIABLES")
                            {
                                SetVariables setvariable;

                                setvariable = GetSetVariableSet <SetVariables>(stepNode.ToString());
                                if (setvariable != null)
                                {
                                    pkg.SetVariableRuntime(setvariable.SetVariable, stepName, "");
                                }
                            }
                            else if (stepNode.Name != null && stepNode.Name.ToString().ToUpper() == "SENDMAILTASK")
                            {
                                SendMailTask sendMailTask;
                                sendMailTask = GetSendEmailTaskSet <SendMailTask>(stepNode.ToString());
                                if (sendMailTask != null)
                                {
                                    pkg.AddSendMailTask(sendMailTask.Name, sendMailTask.SMTPServer, sendMailTask.From, sendMailTask.To, sendMailTask.CC, sendMailTask.BCC, sendMailTask.Subject, sendMailTask.MessageSource, sendMailTask.Attachments, sendMailTask.Priority, sendMailTask.MessageSourceType);
                                }
                            }
                            else if (stepNode.Name != null && stepNode.Name.ToString().ToUpper() == "SQLTASKSET")
                            {
                                CHEFMetaDataProcessStepSQLTaskSet sQLTaskSet;
                                sQLTaskSet = GetSQLTaskSet <CHEFMetaDataProcessStepSQLTaskSet>(stepNode.ToString());
                                if (sQLTaskSet != null)
                                {
                                    pkg.AddSQLTaskSet(sQLTaskSet, stepName);
                                }
                            }
                            // This loop is for PackageExecution task
                            else if (stepNode.Name != null && stepNode.Name.ToString().ToUpper() == "PACKAGEEXECUTION")
                            {
                                CHEFMetaDataProcessStepPackageExecution packageExecution;
                                packageExecution = GetPackageExecutionSet <CHEFMetaDataProcessStepPackageExecution>(stepNode.ToString());
                                if (packageExecution != null)
                                {
                                    pkg.AddExecutablePackage(packageExecution.PackageName, packageExecution.Connection);
                                }
                            }
                            else if (stepNode.Name != null && stepNode.Name.ToString().ToUpper() == "DATAFLOWSET")
                            {
                                CHEFMetaDataProcessStepDataFlowSet dataFlowSet;
                                dataFlowSet = GetDataFlowSet <CHEFMetaDataProcessStepDataFlowSet>(stepNode.ToString());
                                if (dataFlowSet != null)
                                {
                                    pkg.AddDataFlowSet(dataFlowSet, stepName);
                                }
                            }
                            else if (stepNode.Name != null && stepNode.Name.ToString().ToUpper() == "TABLESTORAGESET")
                            {
                                CHEFMetaDataProcessStepTableStorageSet tableStorageSet;
                                tableStorageSet = GetTableStorageSet <CHEFMetaDataProcessStepTableStorageSet>(stepNode.ToString());
                                if (tableStorageSet != null)
                                {
                                    //pkg.AddDataFlowSet(tableStorageSet, stepName);
                                }
                            }
                        }
                    }
                }
            }
        }
Ejemplo n.º 2
0
        private static void WraperCreateSSISPkg(string[] args)
        {
            const string  pkgCreationLog           = "_PackageCreation";
            const string  sqlLogConnectionKey      = "SQLPreAndPostLogKey007";
            const string  flatFileConnectionLogKey = "FileVerboseLoggingProviderConnection";
            string        PreSQL                 = "Declare @StepName varchar(255)=left('<StepName>',255) EXEC CHEF.InsertLog @StepName," + (int)JobStatus.Started;
            string        PostSQL                = "Declare @StepName varchar(255)=left('<StepName>',255) Exec CHEF.InsertLog @StepName,?";
            string        QueueNextSQL           = "EXEC CHEF.QueueNext";
            string        strPackageLocation     = string.Empty;
            string        strProjectLocation     = string.Empty;
            CatalogFolder catalogFolder          = null;
            bool          executeExistingPackage = false;

            SqlConnection cn = new SqlConnection(DatabaseConnection.CHEF);
            SqlCommand    cm = cn.CreateCommand();

            try
            {
                string isMetaDataFileOrDatabase = ConfigurationManager.AppSettings["MetaDataFileOrDatabase"];
                //Reading Global Config XML form Database
                cn.Open();
                cm.Parameters.Clear();
                cm.CommandType = CommandType.StoredProcedure;
                cm.Parameters.AddWithValue("@MetadataTypeID", MetaDataTypes.GlobalConfig);
                cm.CommandText = "CHEF.GetMetaData";
                SqlDataReader drGlobalConfig = cm.ExecuteReader();
                if (drGlobalConfig.HasRows)
                {
                    drGlobalConfig.Read();
                    strGlobalConfigXML = drGlobalConfig["MetaData"].ToString();
                }
                drGlobalConfig.Close();
                //Reading MetaDataProccess form Database
                cm.Parameters.Clear();
                cm.CommandType = CommandType.StoredProcedure;
                cm.Parameters.AddWithValue("@MetadataTypeID", MetaDataTypes.Process);
                cm.CommandText = "CHEF.GetMetaData";
                SqlDataReader drProcess = cm.ExecuteReader();
                if (drProcess.HasRows)
                {
                    drProcess.Read();
                    strMetaDataXML = drProcess["MetaData"].ToString();
                    processName    = drProcess["ProcessName"].ToString();
                    processID      = drProcess["ProcessID"].ToString();
                    startStepID    = Convert.ToInt32(drProcess["StartStepID"].ToString());
                    processType    = (ProcessTypeNames)Enum.Parse(typeof(ProcessTypeNames), drProcess["ProcessTypeName"].ToString());
                }
                drProcess.Close();

                XElement root    = XElement.Load(new StringReader(strMetaDataXML));
                XElement process = root.Element("Process");
                if (process.Attribute("ExecuteExistingPackage") == null)
                {
                    executeExistingPackage = false;
                }
                else
                {
                    executeExistingPackage = Convert.ToBoolean(process.Attribute("ExecuteExistingPackage").Value);
                }
                if (executeExistingPackage)
                {
                    Microsoft.SqlServer.Management.IntegrationServices.PackageInfo packageInfo = null;
                    catalogFolder = CheckCatalogFolder("CHEFFolder");
                    packageInfo   = GetPackageFromCatalog(catalogFolder, processID + "_" + processName);
                    if (packageInfo != null)
                    {
                        return;
                    }
                }
                //CHEFMetaData chefMetaData;
                CHEFGlobalConfig chefGlobalConfig;
                // Package Creating Logging
                cm.Parameters.Clear();
                cm.CommandType = CommandType.StoredProcedure;
                cm.Parameters.AddWithValue("@ProcessStep", processName + pkgCreationLog);
                cm.CommandText = "CHEF.InsertLog";
                cm.ExecuteNonQuery();
                SSISBuilder pkg;
                chefGlobalConfig = GetCHEFGlobalConfig <CHEFGlobalConfig>(strGlobalConfigXML);

                /* PUTTING THIS PORTION WITHIN COMMENTS; PACKAGE STORAGE LOCATION NO LONGER REQUIRED SINCE IT IS DEPLOYED TO CATALOG */

                //if (chefGlobalConfig.GlobalConfiguration.OutputPackageLocation == null)
                //{
                //    throw new Exception("Invalid Output Package Location");
                //}
                //else
                //{
                //    if (args.Length <= 0)
                //        strPackageLocation = chefGlobalConfig.GlobalConfiguration.OutputPackageLocation;
                //    else
                //        strPackageLocation = args[0].ToString();
                //    //Removing the last successfully created package
                //    if (File.Exists(strPackageLocation)) File.Delete(strPackageLocation);
                //}

                //chefMetaData = GetCHEFMetaData<CHEFMetaData>(strMetaDataXML);
                //Linq
                pkg = new SSISBuilder(processName, processName);
                //pkg.VerboseLogging = Convert.ToBoolean(chefMetaData.Process.VerboseLogging);
                // Bad Rows error rediction
                chefGlobalConfig.GlobalConfiguration.BadRowFolderLocation = chefGlobalConfig.GlobalConfiguration.BadRowFolderLocation + @"\" + DateTime.Now.ToString().Replace(':', '-');
                if (!System.IO.Directory.Exists(chefGlobalConfig.GlobalConfiguration.BadRowFolderLocation))
                {
                    System.IO.Directory.CreateDirectory(chefGlobalConfig.GlobalConfiguration.BadRowFolderLocation);
                }
                pkg.BadRowsFolderLocation = chefGlobalConfig.GlobalConfiguration.BadRowFolderLocation;
                // SQL Connection for Log
                pkg.AddConnection(new Connection()
                {
                    Key = sqlLogConnectionKey, ConnectionString = cn.ConnectionString + ";Provider=SQLNCLI11.1;", ConnectionType = ConnectionTypes.OleDBConnection
                });
                //File Verbose Logging Provider Connection
                pkg.AddConnection(new Connection()
                {
                    Key = flatFileConnectionLogKey, ConnectionString = chefGlobalConfig.GlobalConfiguration.LogLocation, ConnectionType = ConnectionTypes.FileConnection
                });
                //Package Start Execuation Logging
                Executable sqlTaskPre = pkg.AddExecutableSQLTask(PreSQL.Replace("<StepName>", processName + "_PackageExecution"), sqlLogConnectionKey, "Pre-" + processName + "_PackageExecution", "PackageFailSuccess");
                pkg.AddPrecedenceConstraints(sqlTaskPre);
                //Package Level Variables
                pkg.SetVariableValue("PackageFailSuccess", 2, "Int32");
                PackageCreation(strMetaDataXML, pkg, processType.ToString());

                // Process Finished Package Execution Logging
                Executable sqlTaskQueueNext = pkg.AddExecutableSQLTask(QueueNextSQL, sqlLogConnectionKey, "QueueNextETL");
                pkg.AddPrecedenceConstraints(sqlTaskQueueNext);

                // Process Finished Package Execution Logging
                Executable sqlTaskPost = pkg.AddExecutableSQLTask(PostSQL.Replace("<StepName>", processName + "_PackageExecution"), sqlLogConnectionKey, "Post-" + processName + "_PackageExecution", "PackageFailSuccess");
                pkg.AddPrecedenceConstraints(sqlTaskPost);

                /* PUTTING THIS PORTION WITHIN COMMENTS; PACKAGE ARCHIVE LOCATION NO LONGER REQUIRED */
                bool DumpPackageFileOnDisk = false;
                if (ConfigurationManager.AppSettings["DumpPackageFileOnDisk"] == null)
                {
                    DumpPackageFileOnDisk = false;
                }
                else if (ConfigurationManager.AppSettings["DumpPackageFileOnDisk"].ToString() == "True")
                {
                    DumpPackageFileOnDisk = true;
                }
                if (System.IO.Directory.Exists(chefGlobalConfig.GlobalConfiguration.OutputPackageLocation))
                {
                    System.IO.Directory.Delete(chefGlobalConfig.GlobalConfiguration.OutputPackageLocation);
                }
                if (DumpPackageFileOnDisk)
                {
                    strPackageLocation = chefGlobalConfig.GlobalConfiguration.OutputPackageLocation;
                    pkg.SaveAsPackage(strPackageLocation);
                }
                strProjectLocation = Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location) + @"\Temp";
                catalogFolder      = CheckCatalogFolder("CHEFFolder");
                CreateProjectAndDeploy(catalogFolder, strProjectLocation, pkg.GetCurrentPackage());
                // Logging successful creation of Package
                cm.Parameters.Clear();
                cm.CommandText = "CHEF.InsertLog";
                cm.CommandType = CommandType.StoredProcedure;
                cm.Parameters.AddWithValue("@ProcessStep", processName + pkgCreationLog);
                cm.Parameters.AddWithValue("@StatusID", 2);
                cm.ExecuteNonQuery();
                cm.Parameters.Clear();
            }
            catch (Exception exx)
            {
                string errorMessage = exx.Message;
                if (errorMessage.Length > 500)
                {
                    errorMessage = errorMessage.Substring(0, 499);
                }
                cm.CommandText = "CHEF.InsertLog";
                cm.Parameters.Clear();
                cm.CommandType = CommandType.StoredProcedure;
                cm.Parameters.AddWithValue("@ProcessStep", processName + pkgCreationLog);
                cm.Parameters.AddWithValue("@Description", errorMessage);
                cm.Parameters.AddWithValue("@StatusID", JobStatus.Failed);
                cm.ExecuteNonQuery();
                //throw (exx);
            }
            finally
            {
                cn.Close();
                cm.Dispose();
            }
        }