Skip to content

Instantly share code, notes, and snippets.

@S1r-Lanzelot

S1r-Lanzelot/DeploySSISProjectWithEnv.cs

Last active April 21, 2022 18:04
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save S1r-Lanzelot/e0863b33cca1f8956c028e211b555dd4 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save S1r-Lanzelot/e0863b33cca1f8956c028e211b555dd4 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. John Lanz revised this gist Apr 21, 2022. 1 changed file with 0 additions and 1 deletion.
    1 change: 0 additions & 1 deletion DeploySSISProjectWithEnv.cs
    View file
    Original file line number Diff line number Diff line change
    @@ -36,7 +36,6 @@ private static void DeployIspac(string targetConnectionString, string folderName
    Console.WriteLine($"Deploying {projectName}.");

    byte[] projectFile = File.ReadAllBytes(projectIspac);
    targetFolder.Projects[projectName]?.Drop();
    var op = targetFolder.DeployProject(projectName, projectFile);
    targetFolder.Alter();
    ProjectInfo targetProj;
  2. John Lanz revised this gist Apr 21, 2022. 1 changed file with 90 additions and 88 deletions.
    178 changes: 90 additions & 88 deletions DeploySSISProjectWithEnv.cs
    View file
    Original file line number Diff line number Diff line change
    @@ -1,109 +1,111 @@
    private const string SSIS_AUTO_GENERATED_PARAM_PREFIX = "CM.";

    private static void DeployIspac(string targetConnectionString, string folderName, string ispacSourcePath)
    {
    using (var targetConn = new SqlConnection(targetConnectionString))
    {
    IntegrationServices targetIntegrationSvcs = new IntegrationServices(targetConn);
    Catalog targetCatalog = targetIntegrationSvcs.Catalogs["SSISDB"];

    CatalogFolder targetFolder = targetCatalog.Folders.FirstOrDefault(f => f.Name == folderName);
    if (targetFolder == null)
    {
    using (var targetConn = new SqlConnection(targetConnectionString))
    {
    IntegrationServices targetIntegrationSvcs = new IntegrationServices(targetConn);
    Catalog targetCatalog = targetIntegrationSvcs.Catalogs["SSISDB"];
    Console.ForegroundColor = ConsoleColor.Yellow;
    Console.WriteLine("Folder did not exist in target server, creating folder.");
    Console.ResetColor();
    targetFolder = new CatalogFolder(targetCatalog, folderName, string.Empty);
    targetFolder.Create();
    }

    CatalogFolder targetFolder = targetCatalog.Folders.FirstOrDefault(f => f.Name == folderName);
    if (targetFolder == null)
    {
    Console.ForegroundColor = ConsoleColor.Yellow;
    Console.WriteLine("Folder did not exist in target server, creating folder.");
    Console.ResetColor();
    targetFolder = new CatalogFolder(targetCatalog, folderName, string.Empty);
    targetFolder.Create();
    }
    EnvironmentInfo targetEnv = targetFolder.Environments.FirstOrDefault(e => e.Name == ENVIRONMENT_NAME);
    if (targetEnv == null)
    {
    Console.ForegroundColor = ConsoleColor.Yellow;
    Console.WriteLine("Environment did not exist in target server, creating environment in destination folder.");
    Console.ResetColor();
    targetEnv = new EnvironmentInfo(targetFolder, ENVIRONMENT_NAME, string.Empty);
    targetEnv.Create();
    }

    EnvironmentInfo targetEnv = targetFolder.Environments.FirstOrDefault(e => e.Name == ENVIRONMENT_NAME);
    if (targetEnv == null)
    {
    Console.ForegroundColor = ConsoleColor.Yellow;
    Console.WriteLine("Environment did not exist in target server, creating environment in destination folder.");
    Console.ResetColor();
    targetEnv = new EnvironmentInfo(targetFolder, ENVIRONMENT_NAME, string.Empty);
    targetEnv.Create();
    }
    Console.WriteLine("Deploying projects.");
    var projectIspacList = Directory.GetFiles(ispacSourcePath, "*.ispac");

    Console.WriteLine("Deploying projects.");
    var projectIspacList = Directory.GetFiles(ispacSourcePath, "*.ispac");
    foreach (var projectIspac in projectIspacList)
    {
    string projectName = Path.GetFileNameWithoutExtension(projectIspac);
    Console.WriteLine($"Deploying {projectName}.");

    foreach (var projectIspac in projectIspacList)
    {
    string projectName = Path.GetFileNameWithoutExtension(projectIspac);
    Console.WriteLine($"Deploying {projectName}.");

    byte[] projectFile = File.ReadAllBytes(projectIspac);
    targetFolder.Projects[projectName]?.Drop();
    var op = targetFolder.DeployProject(projectName, projectFile);
    targetFolder.Alter();
    ProjectInfo targetProj;
    try
    {
    //creating new integrations services to pull back recently deployed project as refreshing at services/catalog/and or folder level only pulls back refreshed properties.
    targetProj = new IntegrationServices(targetConn).Catalogs["SSISDB"].Folders[folderName].Projects
    .First(p => p.Name == projectName);
    }
    catch
    {
    Console.ForegroundColor = ConsoleColor.Red;
    Console.Error.WriteLine($"Deployment of {projectName} to taget server failed, project could not be found.");
    Console.ResetColor();
    return;
    }
    byte[] projectFile = File.ReadAllBytes(projectIspac);
    targetFolder.Projects[projectName]?.Drop();
    var op = targetFolder.DeployProject(projectName, projectFile);
    targetFolder.Alter();
    ProjectInfo targetProj;
    try
    {
    //creating new integrations services to pull back recently deployed project as refreshing at services/catalog/and or folder level only pulls back refreshed properties.
    targetProj = new IntegrationServices(targetConn).Catalogs["SSISDB"].Folders[folderName].Projects
    .First(p => p.Name == projectName);
    }
    catch
    {
    Console.ForegroundColor = ConsoleColor.Red;
    Console.Error.WriteLine($"Deployment of {projectName} to taget server failed, project could not be found.");
    Console.ResetColor();
    return;
    }

    Console.ForegroundColor = ConsoleColor.Yellow;
    bool didAddEnvParam = false;
    foreach (var param in targetProj.Parameters)
    {
    if (param.Required && !param.Name.StartsWith(SSIS_AUTO_GENERATED_PARAM_PREFIX) && targetEnv.Variables[param.Name] == null)
    {
    didAddEnvParam = true;
    AddEnvVariable(param, targetEnv, projectName);
    }
    }
    Console.ForegroundColor = ConsoleColor.Yellow;
    bool didAddEnvParam = false;
    foreach (var param in targetProj.Parameters)
    {
    if (param.Required && !param.Name.StartsWith(SSIS_AUTO_GENERATED_PARAM_PREFIX) && targetEnv.Variables[param.Name] == null)
    {
    didAddEnvParam = true;
    AddEnvVariable(param, targetEnv, projectName);
    }
    }

    foreach (var package in targetProj.Packages)
    foreach (var package in targetProj.Packages)
    {
    foreach (var param in package.Parameters)
    {
    if (param.Required && !param.Name.StartsWith(SSIS_AUTO_GENERATED_PARAM_PREFIX) && targetEnv.Variables[param.Name] == null)
    {
    foreach (var param in package.Parameters)
    {
    if (param.Required && !param.Name.StartsWith(SSIS_AUTO_GENERATED_PARAM_PREFIX) && targetEnv.Variables[param.Name] == null)
    {
    didAddEnvParam = true;
    AddEnvVariable(param, targetEnv, projectName);
    }
    }
    didAddEnvParam = true;
    AddEnvVariable(param, targetEnv, projectName);
    }
    }
    }

    if (didAddEnvParam)
    targetEnv.Alter();

    Console.ResetColor();
    if (didAddEnvParam)
    targetEnv.Alter();

    //must add all params before refrencing
    if(!targetProj.References.Any(r => r.Name.Equals(ENVIRONMENT_NAME, StringComparison.OrdinalIgnoreCase)))
    targetProj.References.Add(ENVIRONMENT_NAME);
    Console.ResetColor();

    foreach (var param in targetProj.Parameters)
    {
    if (param.Required && !param.Name.StartsWith(SSIS_AUTO_GENERATED_PARAM_PREFIX) && targetEnv.Variables[param.Name] != null)
    param.Set(ParameterInfo.ParameterValueType.Referenced, param.Name);
    }
    //must add all params before refrencing
    if(!targetProj.References.Any(r => r.Name.Equals(ENVIRONMENT_NAME, StringComparison.OrdinalIgnoreCase)))
    targetProj.References.Add(ENVIRONMENT_NAME);

    foreach (var package in targetProj.Packages)
    {
    foreach (var param in package.Parameters)
    {
    if (param.Required && !param.Name.StartsWith(SSIS_AUTO_GENERATED_PARAM_PREFIX) && targetEnv.Variables[param.Name] != null)
    param.Set(ParameterInfo.ParameterValueType.Referenced, param.Name);
    }
    package.Alter();
    }
    foreach (var param in targetProj.Parameters)
    {
    if (param.Required && !param.Name.StartsWith(SSIS_AUTO_GENERATED_PARAM_PREFIX) && targetEnv.Variables[param.Name] != null)
    param.Set(ParameterInfo.ParameterValueType.Referenced, param.Name);
    }

    targetProj.Alter();
    foreach (var package in targetProj.Packages)
    {
    foreach (var param in package.Parameters)
    {
    if (param.Required && !param.Name.StartsWith(SSIS_AUTO_GENERATED_PARAM_PREFIX) && targetEnv.Variables[param.Name] != null)
    param.Set(ParameterInfo.ParameterValueType.Referenced, param.Name);
    }
    package.Alter();
    }

    targetProj.Alter();
    }
    }
    }

    private static void AddEnvVariable(ParameterInfo param, EnvironmentInfo targetEnv, string projectName)
    {
  3. John Lanz created this gist Apr 21, 2022.
    115 changes: 115 additions & 0 deletions DeploySSISProjectWithEnv.cs
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,115 @@
    private static void DeployIspac(string targetConnectionString, string folderName, string ispacSourcePath)
    {
    using (var targetConn = new SqlConnection(targetConnectionString))
    {
    IntegrationServices targetIntegrationSvcs = new IntegrationServices(targetConn);
    Catalog targetCatalog = targetIntegrationSvcs.Catalogs["SSISDB"];

    CatalogFolder targetFolder = targetCatalog.Folders.FirstOrDefault(f => f.Name == folderName);
    if (targetFolder == null)
    {
    Console.ForegroundColor = ConsoleColor.Yellow;
    Console.WriteLine("Folder did not exist in target server, creating folder.");
    Console.ResetColor();
    targetFolder = new CatalogFolder(targetCatalog, folderName, string.Empty);
    targetFolder.Create();
    }

    EnvironmentInfo targetEnv = targetFolder.Environments.FirstOrDefault(e => e.Name == ENVIRONMENT_NAME);
    if (targetEnv == null)
    {
    Console.ForegroundColor = ConsoleColor.Yellow;
    Console.WriteLine("Environment did not exist in target server, creating environment in destination folder.");
    Console.ResetColor();
    targetEnv = new EnvironmentInfo(targetFolder, ENVIRONMENT_NAME, string.Empty);
    targetEnv.Create();
    }

    Console.WriteLine("Deploying projects.");
    var projectIspacList = Directory.GetFiles(ispacSourcePath, "*.ispac");

    foreach (var projectIspac in projectIspacList)
    {
    string projectName = Path.GetFileNameWithoutExtension(projectIspac);
    Console.WriteLine($"Deploying {projectName}.");

    byte[] projectFile = File.ReadAllBytes(projectIspac);
    targetFolder.Projects[projectName]?.Drop();
    var op = targetFolder.DeployProject(projectName, projectFile);
    targetFolder.Alter();
    ProjectInfo targetProj;
    try
    {
    //creating new integrations services to pull back recently deployed project as refreshing at services/catalog/and or folder level only pulls back refreshed properties.
    targetProj = new IntegrationServices(targetConn).Catalogs["SSISDB"].Folders[folderName].Projects
    .First(p => p.Name == projectName);
    }
    catch
    {
    Console.ForegroundColor = ConsoleColor.Red;
    Console.Error.WriteLine($"Deployment of {projectName} to taget server failed, project could not be found.");
    Console.ResetColor();
    return;
    }

    Console.ForegroundColor = ConsoleColor.Yellow;
    bool didAddEnvParam = false;
    foreach (var param in targetProj.Parameters)
    {
    if (param.Required && !param.Name.StartsWith(SSIS_AUTO_GENERATED_PARAM_PREFIX) && targetEnv.Variables[param.Name] == null)
    {
    didAddEnvParam = true;
    AddEnvVariable(param, targetEnv, projectName);
    }
    }

    foreach (var package in targetProj.Packages)
    {
    foreach (var param in package.Parameters)
    {
    if (param.Required && !param.Name.StartsWith(SSIS_AUTO_GENERATED_PARAM_PREFIX) && targetEnv.Variables[param.Name] == null)
    {
    didAddEnvParam = true;
    AddEnvVariable(param, targetEnv, projectName);
    }
    }
    }

    if (didAddEnvParam)
    targetEnv.Alter();

    Console.ResetColor();

    //must add all params before refrencing
    if(!targetProj.References.Any(r => r.Name.Equals(ENVIRONMENT_NAME, StringComparison.OrdinalIgnoreCase)))
    targetProj.References.Add(ENVIRONMENT_NAME);

    foreach (var param in targetProj.Parameters)
    {
    if (param.Required && !param.Name.StartsWith(SSIS_AUTO_GENERATED_PARAM_PREFIX) && targetEnv.Variables[param.Name] != null)
    param.Set(ParameterInfo.ParameterValueType.Referenced, param.Name);
    }

    foreach (var package in targetProj.Packages)
    {
    foreach (var param in package.Parameters)
    {
    if (param.Required && !param.Name.StartsWith(SSIS_AUTO_GENERATED_PARAM_PREFIX) && targetEnv.Variables[param.Name] != null)
    param.Set(ParameterInfo.ParameterValueType.Referenced, param.Name);
    }
    package.Alter();
    }

    targetProj.Alter();
    }
    }
    }

    private static void AddEnvVariable(ParameterInfo param, EnvironmentInfo targetEnv, string projectName)
    {
    Type paramType = Type.GetType($"System.{param.DataType}");
    object defaultValue = paramType.IsValueType ? Activator.CreateInstance(paramType) : string.Empty;

    targetEnv.Variables.Add(param.Name, param.DataType, defaultValue, param.Sensitive, param.Description);
    Console.Error.WriteLine($"Parameter [{param.Name}] not found for {projectName}. Creating parameter however the value must be manually set in the given environment.");
    }