A blog about SQL Server, SSIS, C# and whatever else I happen to be dealing with in my professional life.

Find ramblings

Friday, October 28, 2016

What packages still use Configuration?

What packages still use Configurations?

I'm sitting in Tim Mitchell's excellent "Deep Dive into the SSISDB session" and someone asked how they can figure out what packages use the classic deployment model's Configuration option.

Create an SSIS package. Add a Variable to your package called FolderSource and assign it the path to your SSIS packages. Add a Script Task to the package and then add @[User::FolderSource] to the ReadOnly parameters.

Double click the script, assuming C#, and when it opens up, use the following script as your Main

public void Main()
{
    // Assign the SSIS Variable's value to our local variable
    string sourceFolder = Dts.Variables["FolderSource"].Value.ToString();
    Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
    string message = "Package {0} uses configuration {1}";
    bool fireAgain = false;
    Package pkg = null;
    foreach (string packagePath in System.IO.Directory.GetFiles(sourceFolder, "*.dtsx", System.IO.SearchOption.AllDirectories))
    {
    try
    {
        pkg = app.LoadPackage(packagePath, null);
        // EnableConfigurations is a boolean specifying whether you have checked the first button
        if (pkg.EnableConfigurations)
        {
        Dts.Events.FireInformation(0, "Configuration Finder", string.Format(message, packagePath, string.Empty), string.Empty, 0, ref fireAgain);

        // This will expose all the configurations that are being used
        // because you could have specified different configuration mechanisms
        foreach (Configuration config in pkg.Configurations)
        {
            Dts.Events.FireInformation(0, "Configuration Details", string.Format(message, packagePath, config.ConfigurationType), string.Empty, 0, ref fireAgain);
        }
        }
    }
    catch (Exception ex)
    {
        Dts.Events.FireWarning(0, "Config finder", packagePath, string.Empty, 0);
        Dts.Events.FireWarning(0, "Config finder", ex.ToString(), string.Empty, 0);

    }
    }

    Dts.TaskResult = (int)ScriptResults.Success;
}

Save and close the package and hit F5.

How cool is that, we're using an SSIS package to inspect the rest of our packages. Now, if you store your packages in the MSDB, the above changes ever so slightly. We'd need to provide a connection string to the database and then change our first foreach loop to enumerate through all the packages in the MSDB. Perhaps that'll be a followup post.

No comments: