Sunday 26 December 2010

Uploading all packages from your Visual Studio Project

Case
I want to upload all SSIS packages in my local Visual Studio Project folder to the SQL development server at once.

Solution
You can use the SSIS api for that!

1) Foreach Loop
At a Foreach Loop Container and give it a suitable name:
Foreach Loop Container














2) File Enumerator
Use a Foreach File Enumerator to loop through the (bin) folder of your project. (Unfortunately you can't get the folder name of a package, so you will have enter the path in the foreach loop manually.) Select only the *.dtsx files and use Fully qualified to retrieve the filename.
All packages from your VS project



















3) Variable
Go to the Variable Mapping tab and select <New Variable...>. Create a new String variable with the name localPackageFile. After creating the new variable the Index is set to the default 0.
New String variable to store the path



















4) Script Task
Drag a Script task into the Foreach loop and give it a suitable name.
Script task













5) Pass through variable
Select the new create String variable localPackageFile as a ReadOnlyVariable on the first tab of the Script task.
Select the variable



















6) The script
We will use C# in this example. Hit the Edit Script button and edit the Main method. This script will upload (and overwrite) all packages.
public void Main()
{
    // Set application context
    Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();

    // Get local Package with path in variable
    Package localPackage = new Package();
    localPackage = app.LoadPackage(Dts.Variables["User::localPackageFile"].Value.ToString(), null);

    // Upload if the server version isn't newer
    app.SaveToSqlServer(localPackage, null, "Name_Of_Your_Server", null, null);

    // Log information about uploading
    bool fireAgain = true;
    Dts.Events.FireInformation(0, "UploadScript", "Package '" + localPackage.Name.ToString() + "' has been uploaded.", string.Empty, 0, ref fireAgain);

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


7) More advanced script
Or use a little more advanced script that checks the Package version before uploading, so you don't overwrite newer packages with older versions.
public void Main()
{
    // Set application context
    Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();

    // Get local Package with path in variable
    Package localPackage = new Package();
    localPackage = app.LoadPackage(Dts.Variables["User::localPackageFile"].Value.ToString(), null);

    // Create server Package to compare versions before upload
    Package serverPackage = new Package();
    try
    {
        // Try to find a package with the same name on the server
        serverPackage = app.LoadFromSqlServer("\\" + localPackage.Name, "Name_Of_Your_Server", null, null, null);
    }
    catch
    {
        // Ignore error that package doesn't exist on server
    }

    // Compare versions
    if ((localPackage.VersionMajor >= serverPackage.VersionMajor) && (localPackage.VersionMinor >= serverPackage.VersionMinor) && (localPackage.VersionBuild >= serverPackage.VersionBuild))
    {
        // Upload if the server version isn't newer
        app.SaveToSqlServer(localPackage, null, "Name_Of_Your_Server", null, null);

        // Log information about uploading
        bool fireAgain = true;
        Dts.Events.FireInformation(0, "UploadScript", "Package '" + localPackage.Name.ToString() + "' has been uploaded.", string.Empty, 0, ref fireAgain);
    }
    else
    {
        // Log warning that server version was newer
        Dts.Events.FireWarning(0, "UploadScript", "Package '" + localPackage.Name.ToString() + "' has not been uploaded.", String.Empty, 0);
    }

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

You can add some checks/messages yourself. Like, don't upload the upload package itself.
The result on the progress tab















Let me know what your solution is or what kind of extra checks you built in.

No comments:

Post a Comment

Please use the SSIS MSDN forum for general SSIS questions that are not about this post. I'm a regular reader of that forum and will gladly answer those questions over there.

All comments are moderated manually to prevent spam.

Related Posts Plugin for WordPress, Blogger...