The Project and Parameter Object Model

[SSIS-Team]

First published on MSDN on Aug 04, 2011

Back to the Denali theme - today’s post is from Xiaochen Wu, a tester on the SSIS team based in Shanghai.

-------------------

In SSIS, we can manipulate packages programmatically with the SSIS object model. In Denali, we extended the object model to support the new concepts we introduced including project and parameter.

Project Object Model

In Denali SSIS, we can create, load, modify and save projects dynamically with object model. Following is the code sample of how to manipulate projects programmatically:

//Update it to a valid file path. The “.ispac” is the extension of project file
string projectFileName = @"d:\temp\test.ispac";

//Create a new project and specify the project file name as the project storage
using (Project project = Project.CreateProject(projectFileName))
{
//Set the project property
project.Description = "This is a new project";

//Add a package to the project
project.PackageItems.Add(new Package(), "package.dtsx");

//Get the package and modify its property
project.PackageItems[0].Package.Description = "This is a new package";

//Save the project
project.Save();
}

//Load the project from an existing project file
using (Project project = Project.OpenProject(projectFileName))
{
project.Description = "Loaded from exsting file";
}


Create and Load Projects

When we create a new project, we can specify where we want to save this project as a parameter. The project storage can be a file (the full file name) or stream (System.IO.Stream). The storage will take effort when you save the project. We will talk about the details in next sections.

We can also load the project from a project file (.ispac) or stream. Please notice that if we load project from stream, we should first set the position within the stream to the beginning. If our project file or stream is protected or partially protected by password, we can also specify the password when load the project.

Save Projects

In Denali SSIS, there’re 3 ways to save the projects: Save(), SaveAs() and SaveTo().

If the project storage is already specified, we can use the Save function to save the project to the default storage. If the file or stream is not empty, the existing content will be overwritten. If we call the Save function before we specify the project storage, we will get exception.

The SaveAs function can save project to the specified storage and change to default project storage to the specified one.

The SaveTo function also can save project to the specified storage. But it will not change the current default project storage.

Following is the code sample of saving projects.

using (MemoryStream ms = new MemoryStream())
{
//Create a new project and specify the project file name as the project storage
using (Project project = Project.CreateProject(ms))
{
//Save the project to the default storage
project.Save();

string projectFileName = @"d:\temp\test1.ispac";
string anotherFileName = @"d:\temp\test2.ispac";

//Save the project to the file storage and update the default storage
project.SaveAs(projectFileName);

//Save the project to the file storage but don't update the default storage
project.SaveTo(anotherFileName);
}
}


Manipulate Packages in Project

In Denali SSIS, we can add, get or remove packages in an existing project:

When we add packages to the project, we need to specify the package stream name which is a string ended with “.dtsx”. The stream name can identify a package within a project and it is not necessary to be the same with the package name in package properties.

Parameter Object Model

Parameter is another new concept we introduced in Denali SSIS. We can use the object model to add, modify and remove the parameter for packages or projects.

When we add a parameter, we need to specify the parameter name and data type. The parameter name should be identical for a project or a package. But we can have a project parameter and a package parameter with the same name, because they can be distinguished by the namespace. The namespace of project parameter is “$Project” and namespace of package parameter is “$Package”.

In Denali SSIS, the following data types are not supported for parameters: Empty, Object, DBNull, Char and UInt16. And when we specify values of parameters, it should have the same data type with the parameter. For example, the following code will throw exception:

using (Project project = Project.CreateProject())
{
//Create a project parameter with type of Int64
Parameter param = project.Parameters.Add("Param", TypeCode.Int64);

//Assign a value with Int32 to the parameter. Exception thrown!
param.Value = (Int32)100;
}

In the project or package, we can get the parameter by name or index and use it in the package as a variable. Following is the sample code of manipulating parameters:

using (Project project = Project.CreateProject())
{
//Add a package to the project
project.PackageItems.Add(new Package(), "Dataflow.dtsx");
Package package = project.PackageItems[0].Package;

//Add a package parameter with name "PkgParam" and type string
package.Parameters.Add("PkgParam", TypeCode.String);

//Add a project parameter with name "PrjParam" and type datetime
project.Parameters.Add("PrjParam", TypeCode.DateTime);

//Get parameter by name
package.Parameters["PkgParam"].Value = "I'm a package parameter";

//Get parameter by index
project.Parameters[0].Value = "I'm a project parameter";

//Use parameters in expression
package.Properties["Description"].SetExpression(package, "@[$Project::PrjParam]");

//Remove parameters
project.Parameters.Remove("PrjParam");
package.Parameters.RemoveAt(0);
}




Project Protection Level

If we want to protect our projects with password or user key, we need to specify the protection level and password of the project. When we add a package to project, the protection level and password of the package will be automatically updated to the same value with the corresponding project properties. And if we want to change it, we will get the exception.

In this article, we discussed how to use SSIS object model to manipulate projects and parameters. For more details, please refer to MSDN or SQL Server Book Online.