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);
}
}
}
}
}
}
}
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();
}
}