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]);
}
static void Main(string[] args)
{
// Variables
string targetServerName = @"192.168.2.111\SQL17ENTERPRISE,1435";
string folderName = "SSISPackageItems";
string projectName = "SSISPackageRunInSQLJob";
string packageName = "Package.dtsx";
// Create a connection to the server
string sqlConnectionString = "Data Source=" + targetServerName + ";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 the project
ProjectInfo project = folder.Projects[projectName];
// Get the package
PackageInfo package = project.Packages[packageName];
// Run the package
package.Execute(false, null);
}
static void Main(string[] args)
{
string targetServerName = "localhost";
string folderName = "Permanencia";
string projectName = "Permanencia";
string packageName = "Package1.dtsx";
// Conexion
string sqlConnectionString = "Data Source=" + targetServerName +
";Initial Catalog=master;Integrated Security=SSPI;";
SqlConnection sqlConnection = new SqlConnection(sqlConnectionString);
// Integration Services obj
IntegrationServices integrationServices = new IntegrationServices(sqlConnection);
// Integration Services catalog
Catalog catalog = integrationServices.Catalogs["SSISDB"];
// Carpeta
CatalogFolder folder = catalog.Folders[folderName];
// Proyecto
ProjectInfo project = folder.Projects[projectName];
// Package
PackageInfo package = project.Packages[packageName];
// Run run package
package.Execute(false, null);
}
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());
}
}
static void Main(string[] args)
{
string targetFolderName = "dailyClosedJobs";
string projectName = "dailyClosedJobs.dtsx";
string projectFilePath = @"" + ConfigurationManager.AppSettings["FolderPath"];
try
{
// Create a connection to the server// Create the Integration Services object
string conStr = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
SqlConnection sqlConnection = new SqlConnection(conStr);
using (var con = sqlConnection)
{
IntegrationServices integrationServices = new IntegrationServices(con);
// Get the Integration Services catalog
Catalog catalog = integrationServices.Catalogs["SSISDB"];
// Create the target folder
CatalogFolder folder = new CatalogFolder(catalog,
targetFolderName, "Folder description");
Console.WriteLine("Deploying " + projectName + " project.");
byte[] projectFile = File.ReadAllBytes(projectFilePath);
folder.DeployProject(projectName, projectFile);
Console.WriteLine("Done.");
}
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
}
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);
}
}
//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);
}
protected PackageGroupCollection BuildPackageGroupCollection(string pathToProject)
{
string[] path = pathToProject.Split('/');
string catalog = path[0];
string folder = path[1];
string project = path[2];
Server server = new Server(ssisServer);
IntegrationServices service = new IntegrationServices(server);
Catalog catalogObject = service.Catalogs[catalog];
CatalogFolder folderObject = catalogObject.Folders[folder];
ProjectInfo projectObject = folderObject.Projects[project];
PackageGroupCollection collection = new PackageGroupCollection();
foreach (Microsoft.SqlServer.Management.IntegrationServices.PackageInfo p in projectObject.Packages)
{
PackageGroup g = new PackageGroup(p, server.Name, catalog, folder, project);
collection.Add(g);
}
return(collection);
}
// 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);
}
internal DapDirectoryModelNode(DappleModel oModel, CatalogFolder oFolder)
: base(oModel)
{
m_oFolder = oFolder;
foreach (CatalogFolder oSubFolder in m_oFolder.Folders)
{
AddChildSilently(new DapDirectoryModelNode(m_oModel, oSubFolder));
}
}
public void CheckCatalogFolderTest1()
{
string folderName = "CHEFFolder"; // TODO: Initialize to an appropriate value
Server server = new Server("localhost");
IntegrationServices integrationServices = new IntegrationServices(server);
Catalog catalog = integrationServices.Catalogs["SSISDB"];
CatalogFolder expected = catalog.Folders[folderName]; // TODO: Initialize to an appropriate value
CatalogFolder actual;
actual = Program_Accessor.CheckCatalogFolder(folderName);
Assert.AreEqual(expected.FolderId, actual.FolderId);
}
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 ExecutePackageButton_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"];
ProjectInfo p = folder.Projects["My ProSSIS Project"];
Microsoft.SqlServer.Management.IntegrationServices.PackageInfo pkg = p.Packages["package.dtsx"];
EnvironmentReference reference = p.References["Environment1", folder.Name];
reference.Refresh();
long operationId = pkg.Execute(false, reference);
catalog.Operations.Refresh();
StringBuilder messages = new StringBuilder();
foreach (Operation op in catalog.Operations)
{
if (op.Id == operationId)
{
op.Refresh();
foreach (OperationMessage msg in op.Messages)
{
messages.AppendLine(msg.Message);
}
}
}
LogFileTextbox.Text = "Package executed: " + messages.ToString();
//MessageBox.Show("Package executed");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void ShowOperationMessagesButton_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"];
ProjectInfo p = folder.Projects["My ProSSIS Project"];
catalog.Operations.Refresh();
StringBuilder messages = new StringBuilder();
foreach (ExecutionOperation exec in catalog.Executions)
{
if (exec.Completed)
{
messages.AppendLine(exec.PackageName + " completed " + exec.EndTime.ToString());
var ops = from a in catalog.Operations where a.Id == exec.Id select a;
foreach (Operation op in ops)
{
op.Refresh();
foreach (OperationMessage msg in op.Messages)
{
messages.AppendLine("\t" + msg.Message);
}
}
}
}
LogFileTextbox.Text = messages.ToString();
//MessageBox.Show("Package executed");
}
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 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 ProjectInfo DeployProject(CatalogFolder catalogFolder, string ispacFile)
{
byte[] bytes = File.ReadAllBytes(ispacFile);
string projectName = Path.GetFileNameWithoutExtension(ispacFile);
var results = catalogFolder.DeployProject(projectName, bytes);
DateTime messageDate = results.CreatedTime.GetValueOrDefault(DateTime.Now).DateTime;
foreach (var message in results.GetLatestMessages(messageDate))
{
Log.LogMessage(message.Message);
}
if (results.Status != Operation.ServerOperationStatus.Success)
{
throw new Exception($"Deployment failed for project: '{projectName}");
}
return(catalogFolder.Projects[projectName]);
}
public override DTSExecResult Execute(Connections connections,
VariableDispenser variableDispenser,
IDTSComponentEvents componentEvents,
IDTSLogging log,
object transaction)
{
//return base.Execute(connections, variableDispenser, componentEvents, log, transaction);
Server catalogServer = new Server(ServerName);
IntegrationServices integrationServices = new IntegrationServices(catalogServer);
Catalog catalog = integrationServices.Catalogs[PackageCatalog];
CatalogFolder catalogFolder = catalog.Folders[PackageFolder];
ProjectInfo catalogProject = catalogFolder.Projects[PackageProject];
Microsoft.SqlServer.Management.IntegrationServices.PackageInfo catalogPackage = catalogProject.Packages[PackageName + ".dtsx"];
catalogPackage.Execute(false, null);
return(DTSExecResult.Success);
}
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());
}
}
public void ExecPackage()
{
Server server = new Server(_server);
IntegrationServices service = new IntegrationServices(server);
Catalog catalogObject = service.Catalogs[_catalog];
CatalogFolder folderObject = catalogObject.Folders[_folder];
ProjectInfo projectObject = folderObject.Projects[_project];
PackageInfo p = projectObject.Packages[_packageinfo.Name];
p.Execute(false, null);
//Package p = dtsapp.LoadFromSqlServer(string.Concat(_packageinfo.Folder +
// "\\" + _packageinfo.Name), _server, null, null, null);
//p.Execute();
}
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 DeployProject_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"];
string projectFileName = "myproject.ispac";
folder.DeployProject("My ProSSIS Project", System.IO.File.ReadAllBytes(projectFileName));
folder.Alter();
ProjectInfo p = folder.Projects["My ProSSIS Project"];
p.References.Add("Environment1", folder.Name);
p.Alter();
MessageBox.Show("Project deployed");
//using (Project project = Project.OpenProject(projectFileName))
//{
// MessageBox.Show("Project deployed");
//}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
protected override ModelNode[] Load()
{
String strCacheDir = Dapple.MainForm.Settings.CachePath;
Directory.CreateDirectory(strCacheDir);
m_oServer = new Server(m_oUri.ToBaseUri(), strCacheDir, DapServerRootModelNode.DAPSecureToken, true);
if (m_oServer.Status != Server.ServerStatus.OnLine)
{
throw new DapException("Server is " + m_oServer.Status.ToString());
}
m_oServer.GetDatasetCount(m_oModel.SearchBounds_DAP, m_oModel.SearchKeyword);
DapBrowserMapModelNode oBrowserMap = new DapBrowserMapModelNode(m_oModel, m_oServer);
m_blBrowserMapAvailable = oBrowserMap.PassesFilter;
m_strTitle = m_oServer.Name;
m_blEntireCatalogMode = m_oServer.MajorVersion < 6 || (m_oServer.MajorVersion == 6 && m_oServer.MinorVersion < 3);
List <ModelNode> result = new List <ModelNode>();
if (DisplayBrowserMap)
{
result.Add(oBrowserMap);
}
String strEdition;
CatalogFolder folder = null;
// --- Make three attempts to get the catalog hierarchy root ---
for (int attempt = 0; attempt < 3; attempt++)
{
folder = s_oCCM.GetCatalogHierarchyRoot(m_oServer, m_oModel.SearchBounds_DAP, m_oModel.SearchBoundsSet, m_oModel.SearchKeywordSet, m_oModel.SearchKeyword, out m_blEntireCatalogMode, out strEdition);
if (folder != null)
{
break;
}
Thread.Sleep(5000);
}
if (folder == null)
{
throw new Exception("Catalog hierarchy root was inaccessible. Try refreshing the server.");
}
foreach (CatalogFolder oSubFolder in folder.Folders)
{
result.Add(new DapDirectoryModelNode(m_oModel, oSubFolder));
}
while (!DapServerModelNode.s_oCCM.bGetDatasetList(m_oServer, folder.Hierarchy, folder.Timestamp, m_oModel.SearchBounds_DAP, m_oModel.SearchBoundsSet, m_oModel.SearchKeywordSet, m_oModel.SearchKeyword))
{
}
FolderDatasetList oDatasets = DapServerModelNode.s_oCCM.GetDatasets(m_oServer, folder, m_oModel.SearchBounds_DAP, m_oModel.SearchBoundsSet, m_oModel.SearchKeywordSet, m_oModel.SearchKeyword);
if (oDatasets == null)
{
throw new Exception("Dataset list was inaccessible. Try refreshing the server.");
}
foreach (DataSet oDataset in oDatasets.Datasets)
{
result.Add(new DapDatasetModelNode(m_oModel, oDataset));
}
return(result.ToArray());
}
public Package LoadPackage()
{
// TODO: Consider making a singleton instance of this
var ssisApp = new Application();
Package package = null;
try
{
if (StoredPassword != null)
{
#if SQL2005
ssisApp.PackagePassword = Helper.ConvertToUnsecureString(StoredPassword);
#else
ssisApp.PackagePassword = StoredPassword.ConvertToUnsecureString();
#endif
}
switch (StorageType)
{
case PackageStorageType.FileSystem:
#if SQL2017 || SQL2014 || SQL2012
if (string.IsNullOrWhiteSpace(ProjectPath))
{
package = ssisApp.LoadPackage(ExpandedPackagePath, null);
}
else
{
string filePassword = StoredPassword == null ? null : StoredPassword.ConvertToUnsecureString();
// Read the project file into memory and release the file before opening the project.
var fileMemoryStream = new MemoryStream(File.ReadAllBytes(ExpandedProjectPath));
_project = string.IsNullOrEmpty(filePassword) ? Project.OpenProject(fileMemoryStream) : Project.OpenProject(fileMemoryStream, filePassword);
_project.ProtectionLevel = DTSProtectionLevel.EncryptSensitiveWithUserKey;
package = Helper.LoadPackageFromProject(_project, _project.Name, PackagePath);
}
#else
package = ssisApp.LoadPackage(ExpandedPackagePath, null);
#endif
break;
case PackageStorageType.MSDB:
package = ssisApp.LoadFromSqlServer(PackagePath, Server, null, null, null);
break;
case PackageStorageType.PackageStore:
package = ssisApp.LoadFromDtsServer(PackagePath, Server, null);
break;
case PackageStorageType.SsisCatalog:
#if SQL2017 || SQL2014 || SQL2012
string catalogPassword = StoredPassword == null ? null : StoredPassword.ConvertToUnsecureString();
var sqlConnectionStringBuilder = new SqlConnectionStringBuilder {
DataSource = Server, InitialCatalog = "SSISDB", IntegratedSecurity = true
};
var integrationServices = new IntegrationServices(new SqlConnection(sqlConnectionStringBuilder.ToString()));
Catalog ssisCatalog = integrationServices.Catalogs.FirstOrDefault();
if (ssisCatalog == null)
{
throw new Exception("A SSIS Catalog could not be found.");
}
string ssisFolderName;
string ssisProjectName;
Helper.ParseSsisProjectPath(ProjectPath, out ssisFolderName, out ssisProjectName);
CatalogFolder catalogFolder = ssisCatalog.Folders.FirstOrDefault(x => string.Compare(x.Name, ssisFolderName, StringComparison.OrdinalIgnoreCase) == 0);
if (catalogFolder == null)
{
throw new Exception(string.Format("The catalog folder {0} could not be found.", ssisFolderName));
}
ProjectInfo projectInfo = catalogFolder.Projects.FirstOrDefault(x => string.Compare(x.Name, ssisProjectName, StringComparison.OrdinalIgnoreCase) == 0);
if (projectInfo == null)
{
throw new Exception(string.Format("The project {0} could not be found.", ssisProjectName));
}
byte[] projectBytes = projectInfo.GetProjectBytes();
if (projectBytes == null || projectBytes.Length < 1)
{
throw new Exception(string.Format("The project {0} could not be loaded.", ssisProjectName));
}
var catalogMemoryStream = new MemoryStream(projectBytes);
_project = catalogPassword == null?Project.OpenProject(catalogMemoryStream) : Project.OpenProject(catalogMemoryStream, catalogPassword);
_project.ProtectionLevel = DTSProtectionLevel.EncryptSensitiveWithUserKey;
package = Helper.LoadPackageFromProject(_project, _project.Name, PackagePath);
break;
#else
throw new NotSupportedException();
#endif
}
}
catch (DtsRuntimeException dtsEx)
{
#if SQL2005
const string SsisPackageStoreVersion = "2005";
#elif SQL2008
const string SsisPackageStoreVersion = "2008";
#elif SQL2012
const string SsisPackageStoreVersion = "2012";
#elif SQL2014
const string SsisPackageStoreVersion = "2014";
#elif SQL2017
const string SsisPackageStoreVersion = "2017";
#endif
if (StorageType == PackageStorageType.PackageStore && dtsEx.ErrorCode == HResults.DTS_E_PACKAGENOTFOUND)
{
throw new DtsPackageStoreException(string.Format("The package \"{0}\" couldn't be found in the SSIS {1} Package Store. Please ensure that the correct unit test engine is used when accessing the SSIS {1} Package Store.", PackagePath, SsisPackageStoreVersion));
}
if (dtsEx.ErrorCode == HResults.DTS_E_LOADFROMSQLSERVER)
{
throw new DtsPackageStoreException(string.Format("There was an error while attempting to load the package \"{0}\" from MSDB. Please ensure the package path is valid and the correct unit test engine is used to execute the package. The current unit test engine is SSIS {1}.", PackagePath, SsisPackageStoreVersion));
}
if (dtsEx.ErrorCode == HResults.DTS_E_LOADFROMXML)
{
throw new DtsPackageStoreException(string.Format("There was an error while attempting to load the package \"{0}\" from the file system. Please ensure the package path is valid and the correct unit test engine is used to execute the package. The current unit test engine is SSIS {1}.", PackagePath, SsisPackageStoreVersion));
}
throw;
}
catch (KeyNotFoundException)
{
throw new KeyNotFoundException(string.Format(CultureInfo.CurrentCulture, "The package attribute is {0}, which does not reference a valid package.", PackagePath));
}
_package = package;
return(package);
}
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();
}
}
private static void CreateProjectAndDeploy(CatalogFolder catalogFolder, string strProjectLocation, Package package)
{
byte[] projectStream = null;
if (catalogFolder.Name != "CHEFFolder")
{
return;
}
//Create temporary location if it doesn't exist
if (!Directory.Exists(strProjectLocation))
{
Directory.CreateDirectory(strProjectLocation);
}
//Create Project and add the Package to it
using (Project project = Project.CreateProject(strProjectLocation + @"\TempProject.ispac"))
{
project.Name = processID + "_" + processName + "_" + processType;
project.Description = "Project Description";
//package.Parameters.Add("logValue", TypeCode.Boolean);
//package.Parameters["logValue"].Value = true;
project.PackageItems.Add(package, "Package.dtsx");
project.PackageItems[0].Package.Description = "Package Description";
//project.Parameters.Add("logValue", TypeCode.Boolean);
project.Save();
}
//Convert the Project to equivalent byte stream
using (FileStream fileStream = new FileStream(strProjectLocation + @"\TempProject.ispac", FileMode.Open, FileAccess.Read))
{
byte[] stream = new byte[fileStream.Length];
int numberOfBytesLeft = (int)fileStream.Length;
int numberOfBytesRead = 0;
while (numberOfBytesLeft > 0)
{
int n = fileStream.Read(stream, numberOfBytesRead, numberOfBytesLeft);
if (n == 0) //end of file
{
break;
}
numberOfBytesRead += n;
numberOfBytesLeft -= n;
}
projectStream = stream;
}
//Deploy the Project
using (Project project = Project.OpenProject(strProjectLocation + @"\TempProject.ispac"))
{
catalogFolder.DeployProject(processID + "_" + processName + "_" + processType, projectStream);
catalogFolder.Alter();
}
//Delete temporary location
Directory.Delete(strProjectLocation, true);
//SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["CHEF"].ConnectionString);
//conn.Open();
//SqlCommand comm = conn.CreateCommand();
//comm.CommandType = CommandType.Text;
//comm.CommandText = "INSERT INTO [CHEF].[RequestQueue] VALUES(MONTH(SYSDATETIME()), YEAR(SYSDATETIME()), " + processID + ", " + strStartStepID + ", 1, NEWID(), SUSER_SNAME(), SYSDATETIME(), SYSDATETIME())";
//comm.ExecuteNonQuery();
//conn.Close();
//comm.Dispose();
}
public static Microsoft.SqlServer.Management.IntegrationServices.PackageInfo GetPackageFromCatalog(CatalogFolder catalogFolder, string projectName)
{
Microsoft.SqlServer.Management.IntegrationServices.PackageInfo package = null;
ProjectInfo project = catalogFolder.Projects[projectName];
if (project == null)
{
return(package); //returns null
}
package = project.Packages["Package.dtsx"];
return(package);
}
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();
}
}
} // 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)