private void TransferPackageButton_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();
Project localProject = CreateNewProject("newproject.ispac", "New Project");
folder.DeployProject("New Project", System.IO.File.ReadAllBytes("newprojects.ispac"));
folder.Alter();
ProjectInfo newProject = folder.Projects["New Project"];
newProject.Move("ProSSIS");
newProject.Alter();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public void CreateProjectAndDeployTest2()
{
string serverName = ConfigurationManager.ConnectionStrings["CHEF"].ConnectionString.Split(';')[1];
int index = serverName.IndexOf('=');
serverName = serverName.Substring(index + 1);
Server server = new Server(serverName);
IntegrationServices integrationServices = new IntegrationServices(server);
Catalog catalog = integrationServices.Catalogs["SSISDB"];
CatalogFolder catalogFolder = catalog.Folders["NotTheCHEFFolder"];
if (catalogFolder == null)
{
catalogFolder = new CatalogFolder(catalog, "NotTheCHEFFolder", "Not the CHEF folder.");
catalogFolder.Create();
}
string strProjectLocation = @"C:\Program Files\Microsoft\CHEF\Temp";
Package package = null;
Application app = new Application();
package = app.LoadPackage(@"C:\Users\t-satsen\Documents\Visual Studio 2010\Projects\Package_DatabaseToDatabase\BasicFeaturesPackage\bin\Debug\TableToTableDataTransferPkg.dtsx", null);
Program_Accessor.processID = "100";
Program_Accessor.processName = "TestTask";
if (catalogFolder.Projects[Program_Accessor.processID + "_" + Program_Accessor.processName] != null)
{
catalogFolder.Projects[Program_Accessor.processID + "_" + Program_Accessor.processName].Drop();
}
Program_Accessor.CreateProjectAndDeploy(catalogFolder, strProjectLocation, package);
Assert.IsNull(catalogFolder.Projects[Program_Accessor.processID + "_" + Program_Accessor.processName]);
}
//MyCode: Check if catalog exists and if folder exists; create if not.
private static CatalogFolder CheckCatalogFolder(string folderName)
{
string serverName = ConfigurationManager.ConnectionStrings["CHEF"].ConnectionString.Split(';')[1];
int index = serverName.IndexOf('=');
serverName = serverName.Substring(index + 1);
Server server = new Server(serverName);
IntegrationServices integrationServices = new IntegrationServices(server);
Catalog catalog = integrationServices.Catalogs["SSISDB"];
CatalogFolder catalogFolder = null;
if (catalog == null)
{
//Throw exception if catalog doesn't exist
throw new Exception("Catalog not found. Please create the SSISDB catalog and try again.");
}
else
{
catalogFolder = catalog.Folders[folderName];
if (catalogFolder == null)
{
//Create catalog folder if it doesn't exist
catalogFolder = new CatalogFolder(catalog, folderName, "This is the folder which contains all projects generated through CHEF");
catalogFolder.Create();
}
}
return(catalogFolder);
}
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 CatalogFolder CreateSsisFolder(bool createFolder)
{
CatalogFolder catalogFolder = _catalog.Folders[SsisFolder];
if (catalogFolder == null)
{
if (!CreateFolder)
{
throw new Exception($"The Folder '{SsisFolder}' was not found. Set CreateFolder to true to create it");
}
else
{
catalogFolder = new CatalogFolder(_catalog, SsisFolder, "");
catalogFolder.Create();
}
}
return(catalogFolder);
}
private void Create_Click(object sender, EventArgs e)
{
Server server = new Server(_server);
try
{
IntegrationServices isServer = new IntegrationServices(server);
Catalog catalog = isServer.Catalogs["SSISDB"];
CatalogFolder folder = new CatalogFolder(catalog, "ProSSIS", "New Folder");
folder.Create();
MessageBox.Show("Folder Created");
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
static void Main(string[] args)
{
// Connect to the default instance on localhost
var server = new Server("localhost");
var store = new IntegrationServices(server);
// Check that we have a catalog
if (store.Catalogs.Count == 0)
{
Console.WriteLine("SSIS catalog not found on localhost.");
}
// Get the SSISDB catalog - note that there should only
// be one, but the API may support multiple catalogs
// in the future
var catalog = store.Catalogs["SSISDB"];
// Create a new folder
var folder = new CatalogFolder(catalog,
"MyFolder",
"Folder that holds projects");
folder.Create();
// Make sure the project file exists
if (!File.Exists(ProjectFileLocation))
{
Console.WriteLine("Project file not found at: {0}",
ProjectFileLocation);
}
// Load the project using the SSIS API
var project = Project.OpenProject(ProjectFileLocation);
// Deploy the project to the folder we just created
folder.DeployProject(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)
private void tabControl1_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
TabItem tabitem = (TabItem)tabControl1.SelectedItem;
if (tabitem.Header.ToString() == "Package")
{
//packagePath = ConfigurationManager.AppSettings["PackagesPath"].ToString();
// //try
// //{
// // //Set the current directory.
// // if (!System.IO.Directory.Exists(packagePath))
// // System.IO.Directory.CreateDirectory(packagePath);
// // Directory.SetCurrentDirectory(packagePath);
// //}
// //catch (DirectoryNotFoundException ex)
// //{
// // throw new Exception(string.Format("The specified Packages Path {0} does not exist. Please check the Admin Unitility config file. {1}", packagePath, ex));
// //}
// //int i = System.IO.Directory.GetFiles(packagePath).Count();
// comboBoxPackages.Items.Clear();
// if (comboBoxPackages.Items.Count != i)
// {
// foreach (string s in System.IO.Directory.GetFiles(packagePath))
// {
// if (s.ToUpper().IndexOf(".DTSX") > 0)
// {
// string selectedPackage = System.IO.Path.GetFileName(s);
// string processId = string.Empty;
// string processName = string.Empty;
// int startIndex = 0;
// int length = 0;
// selectedPackage = selectedPackage.Replace(".dtsx", "");
// startIndex = selectedPackage.LastIndexOf("_");
// if (startIndex > 0)
// {
// length = (selectedPackage.Length - 1) - (startIndex);
// processId = selectedPackage.Substring(startIndex + 1, length);
// processName = selectedPackage.Substring(0, startIndex);
// comboBoxPackages.Items.Add(new KeyValuePair(processId, processName));
// }
// }
// }
// }
string serverName = ConfigurationManager.ConnectionStrings["CHEF"].ConnectionString.Split(';')[1];
int index = serverName.IndexOf('=');
serverName = serverName.Substring(index + 1);
Server server = new Server(serverName);
IntegrationServices integrationServices = new IntegrationServices(server);
Catalog catalog = integrationServices.Catalogs["SSISDB"];
CatalogFolder catalogFolder = null;
if (catalog == null)
{
//Throw exception if catalog doesn't exist
throw new Exception("Catalog not found. Please create the SSISDB catalog and try again.");
}
else
{
catalogFolder = catalog.Folders["CHEFFolder"];
if (catalogFolder == null)
{
//Create catalog folder if it doesn't exist
catalogFolder = new CatalogFolder(catalog, "CHEFFolder", "This is the folder which contains all projects generated through CHEF");
catalogFolder.Create();
}
}
foreach (ProjectInfo projectInfo in catalogFolder.Projects)
{
comboBoxPackages.Items.Add(new KeyValuePair(projectInfo.Name.Substring(0, projectInfo.Name.IndexOf('_')), projectInfo.Name.Substring(projectInfo.Name.IndexOf('_') + 1)));
}
}
else
{
comboBoxPackages.Items.Clear();
}
}