CODEWITHSUNDEEP
c#ssis
navig8tr
navig8tr

Reputation: 1844

Unable to programatically add new package to a new project

In a C# Console app, I created a new SSIS project (.ispac file). However, when I try to copy a package from an existing SSIS project and add it to the new project, an error is thrown (see image below). The error says the the package has already been added but the new project is completely empty. It must be saying that it has already been added to a project and I'm not allowed to add it to another project.

How can I copy an existing SSIS package and add it to a new SSIS project?

            // Get existing project.
        var ssisProject = Project.OpenProject(@"C:\Packages\CommonPackageTest.ispac");

        // Get existing package.
        Package package = ssisProject.PackageItems["CommonPackageTest.dtsx"].Package;

        // Create new project.
        Project newProject = Project.CreateProject();

        // Copy the original package.
        Package newPackage = package;

        // Attempt to add the new package to the new project
        newProject.PackageItems.Add(newPackage, "NewPackage.dtsx");

enter image description here

Upvotes: 0

Views: 357

Answers (1)

Sam Kolli
Sam Kolli

Reputation: 421

Navig8tr,

Here is what I have observed. When you get the existing package from a Project, that package object has a reference to the Microsoft.SqlServer.Dts.Runtime.Interop.ProjectInterop object. You can check this by getting the value of package.Project. When this property's value is not null, but has a reference to a Project, then the package is unable to be added to another project. Which is why you see the error you are seeing.

To work around this, save the original package to disk and then load that package from disk and add it to the new project. Code below:

    // Get existing project.
var ssisProject = Project.OpenProject(@"C:\Users\Administrator\Documents\visual studio 2015\projects\Integration Services Project9\Integration Services Project9\bin\Development\Integration Services Project9.ispac");

// Get existing package.
Package package = ssisProject.PackageItems["Package.dtsx"].Package;

// Check the value of Project property of Package.
// You will notice that Project is not equal to null; which causes problems if you try to add this to another project.
if (package.Project != null) {
    Console.WriteLine("Original-Package has Project ref: " + package.Project);
}
else {
    Console.WriteLine("Original-Package Project ref IS NULL");
}


// Create new project.
Project newProject = Project.CreateProject();

// Copy the original package. (your original code)
Package newPackage = package;

// Check the value of Project property of this Package.
// You will notice that Project is not equal to null; which causes problems if you try to add this to another project.
if (newPackage.Project != null) {
    Console.WriteLine("Direct-Copy-Package has Project ref: " + newPackage.Project);
}
else {
    Console.WriteLine("Direct-Copy-Package Project ref IS NULL");
}


// Let us try to serializ and deserialize using Application class.

// Instantiate an Application object. We will use this to serde the original package.
Application application = new Application();

// Save/Seriliaze the original package to disk
application.SaveToXml(@"C:\Temp\PackageToCopy.dtsx", package, null);

// Load the package back
Package serdedPackage = application.LoadPackage(@"C:\Temp\PackageToCopy.dtsx", null);

// Check the value of Project property of this Package.
// You will notice that Project is equal to null; which means you can add this to another project.
if (serdedPackage.Project != null) {
    Console.WriteLine("[Before adding to Project] Serded-Package has Project ref: " + serdedPackage.Project);
}
else {
    Console.WriteLine("[Before adding to Project] Serded-Package Project ref IS NULL");
}

// Add our serded package to newProject.
newProject.PackageItems.Add(serdedPackage, "NewPackage.dtsx");

// Now Check the value of Project property of this Serded Package Again
// You will notice that Project is NOT equal to null again.
if (serdedPackage.Project != null) {
    Console.WriteLine("[After adding to Project] Serded-Package has Project ref: " + serdedPackage.Project);
}
else {
    Console.WriteLine("[After adding to Project] Serded-Package Project ref IS NULL");
}

// FInally Save our new project to disk
newProject.SaveTo(@"C:\Temp\Copy.ispac");


Console.WriteLine("Done");

Upvotes: 1

Related Questions