SSIS : Create a simple SSIS package in C# (Part I)

In this post an example on how to build a SSIS with a piece of C# code.For building SSIS packages in C# you need to install visual studio 2010 for C# and add the following references to your project (Project->Add Reference):

• Microsoft.SqlServer.DTSPipelineWrap.dll
• Microsoft.SQLServer.DTSRuntimeWrap.dll
• Microsoft.SQLServer.ManagedDTS.dll
• Microsoft.SqlServer.PipelineHost.dll

Now it’s time to write our first C# program generating a SSIS package. In this C# program only the general properties are programmed. The following (simple) properties of SSIS packages can be programmed:

• VersionComments (“Some Version comments”)
• CreatorName(“Hennie de Nooijer”)
• CreatorComputerName(“HenniePC”)
• CreationDate("01/01/2011")
• PackageType(DTS.DTSPackageType.DTSDesigner100)
• ProtectionLevel(DTSProtectionLevel.DontSaveSensitive)
• MaxConcurrentExecutables(5)
• PackagePriorityClass(DTSPriorityClass.AboveNormal)
• VersionMajor(2)
• VersionMinor(3)
• VersionBuild(7)
• VersionGUID(Readonly)
• EnableConfigurations(false)
• CheckpointFileName(<packagename>.chkpnt)
• SaveCheckpoints(true)
• CheckpointUsage(DTSCheckpointUsage.Always)
• SuppressConfigurationWarnings(false
• LastModifiedProductVersion(“”)
• ForceExecValue(false)
• ExecValue(“Test”)
• ForceExecutionResult(DTSForcedExecResult.Completion)
• Disabled(false)
• FailPackageOnFailure(“false”)
• FailParentOnFailure(“true”)
• MaxErrorCount(2)
• ISOLevel(“IsolationLevel.Snapshot”)
• LocaleID(“8201”). English jamaica
• TransactionOption(“DTS.DTSTransactionOption.Supported”)
• DelayValidation(“true”)
• LoggingMode(“DTS.DTSLoggingMode.Disabled”)
• FilterKind. Has something to do with logging
• EventFilter. Has something to do with logging.
• ObjectName("MyProgrammedSSISPackage"). = Name
• DTSID. UnclearDescription(“This is created with SSIS API”)
• CreationName (Readonly).
• DisableEventHandlers (true)

Off course there is more to set but that will shown in future posts on my blogs.  Here is the snippet from my C# code:

// Create an application
DTS.Application app = new DTS.Application();

// Create a package
DTS.Package pkg = new DTS.Package();

//Setting some properties
pkg.VersionComments = "Some Version comments";
pkg.CreatorName = "Hennie de Nooijer";
pkg.CreatorComputerName = "HenniePC";
pkg.CreationDate = DateTime.Parse("01/01/2011");
pkg.PackageType = DTS.DTSPackageType.DTSDesigner100;
pkg.ProtectionLevel = DTS.DTSProtectionLevel.DontSaveSensitive;
pkg.MaxConcurrentExecutables = 5;
pkg.PackagePriorityClass = DTS.DTSPriorityClass.AboveNormal;
pkg.VersionMajor = 2;
pkg.VersionMinor = 3;
pkg.VersionBuild = 7;
//pkg.VersionGUID (is readonly)
pkg.EnableConfigurations = false;
pkg.CheckpointFileName = String.Format(@"E:\\SSISProgram\\{0}.chkpnt", pkg.Name);
pkg.SaveCheckpoints = true;
pkg.CheckpointUsage = DTS.DTSCheckpointUsage.Always;
pkg.SuppressConfigurationWarnings = false;
pkg.ForceExecutionResult = DTS.DTSForcedExecResult.Completion;
pkg.ForceExecutionValue = false;        // without d
pkg.ForcedExecutionValue = "Test";      // with d
pkg.Disable = false;
pkg.FailPackageOnFailure = false;
pkg.FailParentOnFailure = true;
pkg.MaximumErrorCount = 2;
pkg.IsolationLevel = IsolationLevel.Snapshot;
pkg.LocaleID = 8201; //English.Jamaica
pkg.TransactionOption = DTS.DTSTransactionOption.Supported;
pkg.DelayValidation = true;
pkg.LoggingMode = DTS.DTSLoggingMode.Disabled;
pkg.Name = "MyProgrammedSSISPackage";
pkg.Description = "This is created with SSIS API";
pkg.DisableEventHandlers = true;

app.SaveToXml(String.Format(@"E:\\SSISProgram\\{0}.dtsx", pkg.Name), pkg, null);

Console.WriteLine("Package  {0} created", pkg.Name);

pkg.Dispose();

This will create a SSIS package in the SSISprogram folder. When you open this SSIS package the following XML is shown. On the right i’ve shown the properties as available in the generated SSIS package. I’ve drawn red lines to show the relations.

 There are some properties that can't be set with the SSIS API (as far as i know now):

• PackageFormatVersion.
• PackageType.
• VersionGUID.
• CreationName.

The one i'm unsure about is (These seems properties for the logging. I'll write about them in the future):

• Loggingmode
• Filterkind
• EventFilter

ForceExecutionValueType is set automatically by API.


OffLine mode is greyed out in the properties window.

UpdateObjects is a property that will be removed in a futureversion of SSIS. This is what i found on MSDN : This member will be removed in a future version of MicrosoftSQL Server. Avoid using this member in new development work, and plan to modify applications that currently use this member.
Whether i  set this property or not it will  not be shown in the XML schema of the SSIS package.


Gr,
Hennie