private void CreateEnvironmentButton_Click(object sender, EventArgs e)
{
try
{
Server server = new Server(_server);
IntegrationServices isServer = new IntegrationServices(server);
Catalog catalog = isServer.Catalogs["SSISDB"];
CatalogFolder folder = catalog.Folders["ProSSIS"];
EnvironmentInfo env = new EnvironmentInfo(folder, "Environment1", "Description of Environment1");
env.Create();
env.Variables.Add("var1", TypeCode.Int32, 1, false, "Var1 Description");
env.Variables.Add("sensitiveVar2", TypeCode.String, "secure value", true, "");
env.Alter();
//ProjectInfo p = folder.Projects["ProSSIS"];
//p.References.Add("Environment1", folder.Name);
//p.Alter();
MessageBox.Show("Environment Created");
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
// GET api/values/5
public string Get(int id)
{
string folderName = "ConArchETL";
string environmentName = "";
string cloudDatabaseHost = ".";
//string cloudDatabaseUser = "******";
//string cloudDatabasePassword = "******";
string settings = string.Copy(ConfigurationManager.ConnectionStrings["ManagementContext"].ConnectionString);
string ADO_Evasys_ConnectionString = ".";
// Create a connection to the server
List <string> connectionConfigs = settings.Split(';').Where(c => !string.IsNullOrEmpty(c)).ToList();
Dictionary <string, string> connectionValues = connectionConfigs.Select(item => item.Split('=')).ToDictionary(s => s[0], s => s[1]);
connectionValues.TryGetValue("Data Source", out cloudDatabaseHost);
string sqlConnectionString = "Data Source=" + cloudDatabaseHost +
";Initial Catalog=master;Integrated Security=SSPI;";
SqlConnection sqlConnection = new SqlConnection(sqlConnectionString);
// Create the Integration Services object
IntegrationServices integrationServices = new IntegrationServices(sqlConnection);
// Get the Integration Services catalog
Catalog catalog = integrationServices.Catalogs["SSISDB"];
// Get the folder
CatalogFolder folder = catalog.Folders[folderName];
// Get Environment
environmentName = String.Format("Environment_{0}", folder.Environments.ToList().Count *2);
EnvironmentInfo environmentInfo = folder.Environments[environmentName];
if (environmentInfo != null)
{
environmentInfo.Drop();
}
environmentInfo = new EnvironmentInfo(folder, environmentName, environmentName);
environmentInfo.Create();
if (null == environmentInfo.Variables["CM.ADO_Evasys.ConnectionString"])
{
environmentInfo.Variables.Add("CM.ADO_Evasys.ConnectionString", TypeCode.String, ADO_Evasys_ConnectionString, false, "ConnectionString");
}
else
{
environmentInfo.Variables["CM.ADO_Evasys.ConnectionString"].Value = ADO_Evasys_ConnectionString;
}
environmentInfo.Alter();
return(cloudDatabaseHost);
}
private void DropFolderButton_Click(object sender, EventArgs e)
{
try
{
Server server = new Server(_server);
IntegrationServices isServer = new IntegrationServices(server);
Catalog catalog = isServer.Catalogs["SSISDB"];
CatalogFolder folder = new CatalogFolder(catalog, "Test", "Test description");// catalog.Folders["ProSSIS"];
folder.Create();
EnvironmentInfo newEnv = new EnvironmentInfo(folder, "Environment1", "Description of Environment1");
newEnv.Create();
newEnv.Variables.Add("var1", TypeCode.Int32, 1, false, "Var1 Description");
newEnv.Variables.Add("sensitiveVar2", TypeCode.String, "secure value", true, "");
newEnv.Alter();
// this will fail because there is an environment under the folder
try
{
folder.Drop();
}
catch
{
}
foreach (EnvironmentInfo env in folder.Environments.ToArray())
{
env.Drop();
}
foreach (ProjectInfo p in folder.Projects.ToArray())
{
p.Drop();
}
// this will succeed now that everything has been removed.
folder.Drop();
MessageBox.Show("Folder removed");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void ConfigureEnvironment(ProjectInfo project, CatalogFolder folder, string workingDir)
{
string environment = project.Name;
var newEnv = folder.Environments[environment];
if (newEnv != null)
{
newEnv.Drop();
}
newEnv = new EnvironmentInfo(folder, environment, "");
newEnv.Create();
if (project.References[environment, folder.Name] != null)
{
project.References.Remove(environment, folder.Name);
}
project.References.Add(environment, folder.Name);
AddParametersToEnvironment(workingDir, project.Name, newEnv, project);
}
} // private void SelectDeployXML()
private void btnDeployPackage_Click(object sender, EventArgs e)
{
try
{
btnDeployPackage.Enabled = false;
envCnt = 0;
txtErrMsg.ForeColor = Color.Black;
txtErrMsg.Refresh();
sbMsg = new StringBuilder();
Catalog catalog = null;
CatalogFolder catalogFolder = null;
string varName = string.Empty;
bool varSensitive = false;
TypeCode typeCode = TypeCode.String;
foreach (string env in sEnvironmentTypes)
{
envCnt++;
sEnvironmentType = env;
index = deployXML.EnvironmentServerNames.FindIndex(x => x.Name == env);
sEnvironmentServerName = deployXML.EnvironmentServerNames[index].Server;
deployXML.SetEnvironmentVariablesList(sEnvironmentType);
txtEnvType.Text = sEnvironmentType;
txtEnvType.Refresh();
switch (sEnvironmentType)
{
case "Test":
sEnvironmentName = "Test";
break;
case "Dev":
sEnvironmentName = "Dev";
break;
case "QA":
sEnvironmentName = "QA";
break;
case "UATPri":
sEnvironmentName = "UAT";
break;
case "UATSec":
sEnvironmentName = "UAT";
break;
case "PRDPri":
sEnvironmentName = "PRD";
break;
case "PRDSec":
sEnvironmentName = "PRD";
break;
default:
throw new Exception("Unknown Deployment Type Specified In The Config File: " + sEnvironmentType);
}
// Get the connection strings for the deployment servers, replacing text #EnvironmentServerName#, for the physical server name.
sqlConnPkgDeply = ConfigurationManager.ConnectionStrings["SQLConnPkgDeply"].ConnectionString.Replace("#EnvironmentServerName#", sEnvironmentServerName);
sqlConnJobScript = ConfigurationManager.ConnectionStrings["SQLConnJobScript"].ConnectionString.Replace("#EnvironmentServerName#", sEnvironmentServerName);
txtConnStr.Text = sqlConnPkgDeply;
txtConnStr.Refresh();
if (sSQLAgentJobScript.Length > 0)
{
txtSQLAgentJobConnStr.Text = sqlConnJobScript;
txtSQLAgentJobConnStr.Refresh();
}
// Adjust the Folder Name if deploying to an SSISDB Catalog where you have multiple environments running.
// Meaning you're deploying to a server where you want to maintain separate Dev/QA/UAT environments.
// Append the Environment Type to the Folder Name to isolate package code for the environments.
if (bMultiEnvPerCatalog == true)
{
sSSISFolderName = deployXML.SSISFolderName + sEnvironmentName;
sSSISFolderDescription = deployXML.SSISFolderDescription + " (" + sEnvironmentName + ")";
txtSSISFolderName.Text = sSSISFolderName;
txtSSISFolderName.Refresh();
txtSSISFolderDescription.Text = sSSISFolderDescription;
txtSSISFolderDescription.Refresh();
}
sbMsg.Append("Deployment for Environment Type: '" + sEnvironmentType + "', Server Name: " + sEnvironmentServerName + ".\r\n");
txtErrMsg.Text = sbMsg.ToString();
txtErrMsg.Refresh();
sbMsg.Append("Multiple Environments Per SSISDB Catalog: " + (bMultiEnvPerCatalog == true ? "True" : "False") + ".\r\n");
txtErrMsg.Text = sbMsg.ToString();
txtErrMsg.Refresh();
// Create the SSIS object:
SqlConnection oConnection = new SqlConnection(sqlConnPkgDeply);
IntegrationServices integrationServices = new IntegrationServices(oConnection);
// Verify there is an SSIS Catalog on the deployment server:
if (integrationServices.Catalogs.Count == 0)
{
throw new Exception("There are no SSIS Catalogs associated with connection string: " + sqlConnPkgDeply + " The default Integration Services Catalog is assumed to be " + sSSISCatalog + ".");
}
else
{
catalog = integrationServices.Catalogs[sSSISCatalog];
}
// Check to see if the Project folder exists:
catalogFolder = catalog.Folders[sSSISFolderName];
if (catalogFolder == null)
{
// Create a catalog folder and assign description.
catalogFolder = new CatalogFolder(catalog, sSSISFolderName, sSSISFolderDescription);
catalogFolder.Create();
sbMsg.Append("Folder:" + sSSISFolderName + " has been created in the SSIS Catalog.\r\n");
txtErrMsg.Text = sbMsg.ToString();
txtErrMsg.Refresh();
}
if (deployXML.UseSSISProjectFilename == true && sSSISProjectFilename.Length > 0)
{
// Deploy the project packages:
sbMsg.Append("Deploying " + sDeployFilePath + sSSISProjectFilename + " project ISPAC file.\r\n");
txtErrMsg.Text = sbMsg.ToString();
txtErrMsg.Refresh();
// Think you can only deploy the entire project, not just individual dtsx files.
byte[] projectFile = File.ReadAllBytes(sDeployFilePath + sSSISProjectFilename);
catalogFolder.DeployProject(sSSISProjectName, projectFile);
sbMsg.Append("SSIS Project (" + sSSISProjectFilename + ") has been successfully deployed!\r\n");
txtErrMsg.Text = sbMsg.ToString();
txtErrMsg.Refresh();
}
// Create an Environment for the SSIS project:
if (deployXML.EnvironmentVariables.Count > 0)
{
if (catalogFolder.Environments[sEnvironmentName] != null)
{
catalogFolder.Environments[sEnvironmentName].Drop();
}
EnvironmentInfo environment = new EnvironmentInfo(catalogFolder, sEnvironmentName, sSSISFolderName + " Environment Variables (" + sEnvironmentName + ")");
environment.Create();
sbMsg.Append("SSIS '" + sEnvironmentType + "' Environment has been successfully created!\r\n");
txtErrMsg.Text = sbMsg.ToString();
txtErrMsg.Refresh();
// Add variables to the environment:
foreach (var projVar in deployXML.EnvironmentVariables)
{
varName = projVar.Name;
varSensitive = projVar.Sensitive.ToLower() == "false" || projVar.Sensitive.ToLower() == "no" ? false : true;
switch (projVar.Type.ToUpper())
{
case "BOOLEAN":
typeCode = TypeCode.Boolean;
break;
case "BYTE":
typeCode = TypeCode.Byte;
break;
case "CHAR":
typeCode = TypeCode.Char;
break;
case "DATETIME":
typeCode = TypeCode.DateTime;
break;
case "DBNULL":
typeCode = TypeCode.DBNull;
break;
case "DECIMAL":
typeCode = TypeCode.Decimal;
break;
case "DOUBLE":
typeCode = TypeCode.Double;
break;
case "EMPTY":
typeCode = TypeCode.Empty;
break;
case "INT16":
typeCode = TypeCode.Int16;
break;
case "INT32":
typeCode = TypeCode.Int32;
break;
case "INT64":
typeCode = TypeCode.Int64;
break;
case "OBJECT":
typeCode = TypeCode.Object;
break;
case "SBYTE":
typeCode = TypeCode.SByte;
break;
case "SINGLE":
typeCode = TypeCode.Single;
break;
case "STRING":
typeCode = TypeCode.String;
break;
case "UINT16":
typeCode = TypeCode.UInt16;
break;
case "UINT32":
typeCode = TypeCode.UInt32;
break;
case "UINT64":
typeCode = TypeCode.UInt64;
break;
default:
throw new Exception("Unknown Type Code Specified In The Environment Variable Config File Section: " + projVar.Type);
}
environment.Variables.Add(varName, typeCode, projVar.Value, varSensitive, projVar.Description);
sbMsg.Append("Added Environment Variable: " + varName + ", Type = " + projVar.Type + ", Value = " + projVar.Value + ", Description = " + projVar.Description + ", Sensitive = " + (varSensitive == false ? "false" : "true") + "\r\n");
}
environment.Alter();
txtErrMsg.Text = sbMsg.ToString();
txtErrMsg.Refresh();
//Add environment reference to the SSIS project:
ProjectCollection SSISProjects = catalogFolder.Projects;
ProjectInfo SSISProject = SSISProjects[sSSISProjectName];
if (SSISProject.References.Contains(sEnvironmentName, sSSISFolderName) == true)
{
SSISProject.References.Remove(sEnvironmentName, sSSISFolderName);
}
SSISProject.References.Add(sEnvironmentName, sSSISFolderName);
SSISProject.Alter();
sbMsg.Append("Environment reference '" + sEnvironmentType + "' has been added to the SSIS Project " + sSSISFolderName + "\r\n");
txtErrMsg.Text = sbMsg.ToString();
txtErrMsg.Refresh();
//Create Credential and Proxy.
if ((deployXML.CreateCredential == true && deployXML.CredentialName != string.Empty) || (deployXML.CreateProxy == true && deployXML.SSISProxyName != string.Empty))
{
if ((deployXML.SameIdentitySecretForAllEnv == true && envCnt == 1) || deployXML.SameIdentitySecretForAllEnv == false)
{
using (var credProxy = new CredProxy(sEnvironmentType, deployXML.CredentialName, deployXML.SSISProxyName, deployXML.CreateCredential))
{
credProxy.ShowDialog();
sIdentity = credProxy.Identity;
sSecret = credProxy.Secret;
}
}
// Run script to create the credential.
if (deployXML.CreateCredential == true)
{
sErrorMsg = string.Empty;
SqlParameter[] objParameter = new SqlParameter[4];
objParameter[0] = new SqlParameter("@sCredName", SqlDbType.NVarChar, 128)
{
Value = deployXML.CredentialName
};
objParameter[1] = new SqlParameter("@sIdentity", SqlDbType.NVarChar, 128)
{
Value = sIdentity
};
objParameter[2] = new SqlParameter("@sSecret", SqlDbType.NVarChar, 128)
{
Value = sSecret
};
objParameter[3] = new SqlParameter("@sErrorMsg", SqlDbType.NVarChar, 1000)
{
Value = sErrorMsg,
Direction = ParameterDirection.Output
};
ExecuteNonQueryAsText(SQLScripts.ADHOCCreateCredential, objParameter, sqlConnPkgDeply); // Uses Initial Catalog of master.
sErrorMsg = objParameter[3].Value.ToString().Trim(); // This gets the value output for sErrorMsg.
sbMsg.Append("Script used to create the SQL job CREDENTIAL was run.\r\n");
if (sErrorMsg.Length > 0)
{
sbMsg.Append("Error running script used to create the job run CREDENTIAL:\r\n" + sErrorMsg + "\r\n");
}
txtErrMsg.Text = sbMsg.ToString();
txtErrMsg.Refresh();
}
// Run script to create the proxy.
if (deployXML.CreateProxy == true)
{
sErrorMsg = string.Empty;
SqlParameter[] objParameter = new SqlParameter[4];
objParameter[0] = new SqlParameter("@sCredName", SqlDbType.NVarChar, 128)
{
Value = deployXML.CredentialName
};
objParameter[1] = new SqlParameter("@sProxyName", SqlDbType.NVarChar, 128)
{
Value = deployXML.SSISProxyName
};
objParameter[2] = new SqlParameter("@sIdentity", SqlDbType.NVarChar, 128)
{
Value = sIdentity
};
objParameter[3] = new SqlParameter("@sErrorMsg", SqlDbType.NVarChar, 1000)
{
Value = sErrorMsg,
Direction = ParameterDirection.Output
};
ExecuteNonQueryAsText(SQLScripts.ADHOCCreateProxy, objParameter, sqlConnJobScript); // Uses Initial Catalog of msdb.
sErrorMsg = objParameter[3].Value.ToString().Trim(); // This gets the value output for sErrorMsg.
sbMsg.Append("Script used to create the SQL job PROXY was run.\r\n");
if (sErrorMsg.Length > 0)
{
sbMsg.Append("Error running script used to create the job run PROXY:\r\n" + sErrorMsg + "\r\n");
}
txtErrMsg.Text = sbMsg.ToString();
txtErrMsg.Refresh();
}
}
// Run script to map project parameters to environment variables.
if (bMapProjParamsToEnvVar == true)
{
SqlParameter[] objParameter = new SqlParameter[3];
objParameter[0] = new SqlParameter("@sEnvironmentType", SqlDbType.NVarChar, 50)
{
Value = sEnvironmentName
};
objParameter[1] = new SqlParameter("@sSSISFolderName", SqlDbType.NVarChar, 128)
{
Value = sSSISFolderName
};
objParameter[2] = new SqlParameter("@sSSISProjectName", SqlDbType.NVarChar, 128)
{
Value = sSSISProjectName
};
ExecuteNonQueryAsText(SQLScripts.ADHOCMapProjParamsToEnvVar, objParameter, sqlConnPkgDeply);
sbMsg.Append("Script used to map Project Parameters to Environment Variables was run.\r\n");
txtErrMsg.Text = sbMsg.ToString();
txtErrMsg.Refresh();
}
} // if (htEnvironmentVariables.Count > 0)
foreach (string jobScriptFile in sSQLAgentJobScriptList)
{
sbSQLAgentJobScriptText = new StringBuilder();
sbSQLAgentJobScriptText.Append(System.IO.File.ReadAllText(sDeployFilePath + jobScriptFile));
SqlParameter[] objParameter = new SqlParameter[6];
objParameter[0] = new SqlParameter("@sEnvironmentType", SqlDbType.NVarChar, 50)
{
Value = sEnvironmentName
};
objParameter[1] = new SqlParameter("@bMultiEnvPerCatalog", SqlDbType.Bit)
{
Value = bMultiEnvPerCatalog
};
objParameter[2] = new SqlParameter("@sSSISFolderName", SqlDbType.NVarChar, 128)
{
Value = sSSISFolderName
};
objParameter[3] = new SqlParameter("@sSSISProjectName", SqlDbType.NVarChar, 128)
{
Value = sSSISProjectName
};
objParameter[4] = new SqlParameter("@sSSISProxyName", SqlDbType.NVarChar, 128)
{
Value = sSSISProxyName
};
objParameter[5] = new SqlParameter("@sSSISCatServerName", SqlDbType.NVarChar, 128)
{
Value = sEnvironmentServerName
};
ExecuteNonQueryAsText(sbSQLAgentJobScriptText.ToString(), objParameter, sqlConnJobScript);
sbMsg.Append("SQL Agent Job Script File " + sDeployFilePath + jobScriptFile + " was run.\r\n");
txtErrMsg.Text = sbMsg.ToString();
txtErrMsg.Refresh();
} // foreach (string jobScriptFile in sSQLAgentJobScriptList)
sbMsg.Append("\r\n");
txtErrMsg.Text = sbMsg.ToString();
txtErrMsg.Refresh();
} // foreach (string env in sEnvironmentTypes)
btnExit.BackColor = Color.Green;
btnExit.Focus();
}
catch (Exception ex)
{
txtErrMsg.Text = sbMsg.ToString() + "\r\n" + ex.Message + (ex.InnerException != null ? "\r\n\r\nInner Exception: " + ex.InnerException.Message : "") + "\r\n";
txtErrMsg.ForeColor = Color.Red;
txtErrMsg.Refresh();
}
finally
{
// Create or append run results to the log file.
CopyToLogFile(txtErrMsg.Text);
}
} // private void btnDeployPackage_Click(object sender, EventArgs e)
