Resources:

• http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.executable.aspx

• http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.taskhost.aspx

Control Flow objects for the most part hew closely to the SSIS Designer, but we’ll see some classes here that have no GUI analog, and will have to do some recasting to get specific properties.

The Executable abstract class corresponds to what we see listed in the Control Flow toolbox. Like the ConnectionManager, there’s no strong typing here, and creation is controlled by name strings:

//using Microsoft.SqlServer.Dts.Runtime;
//Package pkg; - a loaded Package
//string cfType = "STOCK:BulkInsertTask"; - the name string of the control flow desired
Executable ex = pkg.Executables.Add(cfType);

Containers:
We’ve got a couple different Control Flow types – containers, and the tasks themselves. There’s only a few containers, but unfortunately there’s no generic Container type, so we have to check for each separately:

if (ex is Sequence)
{
    Sequence seq = ex as Sequence;
}
else if (ex is ForLoop)
{
    ForLoop forloop = ex as ForLoop;
}
else if (ex is ForEachLoop)
{
    ForEachLoop foreachloop = ex as ForEachLoop;
}
else if (ex is Package)
{
    Package package = ex as Package;
}
//else is taskhost of some kind:
else if (ex is TaskHost)
{
    TaskHost th = ex as TaskHost;
}

The Sequence, For and ForEach classes correspond to their GUI equivalents. The Package class itself is derived from the Executable, but obviously we wouldn’t encounter it in this context. The last, TaskHost, is a generic container class that describes any single-task Control Flow. The TaskHost class’ InnerObject property returns an object (of type object) which can be recast into one of the specific SSIS task classes. This makes it much easier to access task-specific properties and methods. Note that it isn’t necessary, however; we could also just access the TaskHost’s Properties key/value store, but we’d have to know the keys to look up.

Iterating through an existing package’s Control Flows is very straightforward: examine each of the Package’s Executables, and check if it’s a container class. If it is, recast and check its children:

public List<Executable> getAllControlFlowObjects(Executables executables)
{
    List<Executable> foundexecutables = new List<Executable>();

    foreach (Executable ex in executables)
    {
        foundexecutables.Add(ex);

        if (ex is Sequence)
        {
            Sequence seq = ex as Sequence;
            foundexecutables.AddRange(getAllControlFlowObjects(seq.Executables));
        }
        else if (ex is ForLoop)
        {
            ForLoop forloop = ex as ForLoop;
            foundexecutables.AddRange(getAllControlFlowObjects(forloop.Executables));
        }
        else if (ex is ForEachLoop)
        {
            ForEachLoop foreachloop = ex as ForEachLoop;
            foundexecutables.AddRange(getAllControlFlowObjects(foreachloop.Executables));
        }
    }

    return foundexecutables;
}

getAllControlFlowObjects(pkg.Executables);

Creation Names:
The above example used a name string with the STOCK
moniker. Many of the components (but not all) are covered by STOCK, and the name string won’t change between SSIS versions. That is, “STOCK:BulkInsertTask” will work in 2005 up through the present.

Since the Containers are all STOCK, it makes sense to handle them in this way:

• STOCK:FORLOOP //For Loop
• STOCK:FOREACHLOOP //For Each Loop
• STOCK:SEQUENCE //Sequence

If we want to create a Control Flow component that isn’t “stock” or analyze existing components, we need to know the component’s “long name”, which can be retrieved from the component’s Assembly Qualified Name:

//using Microsoft.SqlServer.Dts.Tasks.BulkInsertTask;
string name = typeof(Microsoft.SqlServer.Dts.Tasks.BulkInsertTask.BulkInsertTask).AssemblyQualifiedName;

Each Control Flow specific class is in its own namespace. Here’s the namespaces we’ll need:

• Microsoft.DataTransformationServices.Tasks.DTSProcessingTask.ASExecuteDDLTask //Analysis Services Execute DDL Task
• Microsoft.DataTransformationServices.Tasks.DTSProcessingTask.DTSProcessingTask //Analysis Services Processing Task
• Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceBackupTask //Back Up Database
• Microsoft.SqlServer.Dts.Tasks.BulkInsertTask.BulkInsertTask // Bulk Insert
• Attunity.SqlServer.CDCControlTask.CdcControlTask //CDC Control
• Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceCheckIntegrityTask //Check Database Integrity
• Microsoft.SqlServer.Dts.Pipeline.Wrapper.MainPipe //Data Flow
• Microsoft.SqlServer.Dts.Tasks.DMQueryTask.DMQueryTask //Data Mining Query
• Microsoft.SqlServer.Dts.Tasks.DataProfilingTask.DataProfilingTask //Data Profiling
• ??? //Execute Package
• Microsoft.SqlServer.Dts.Tasks.ExecuteProcess.ExecuteProcess //Execute Process
• Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask //Execute SQL
• Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceExecuteAgentJobTask //Execute SQL Server Agent Job
• Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceTSQLExecuteTask //Execute T-SQL Statement
• Microsoft.SqlServer.Dts.Tasks.ExpressionTask.ExpressionTask //Expression
• Microsoft.SqlServer.Dts.Tasks.FileSystemTask.FileSystemTask // File System
• Microsoft.SqlServer.Dts.Tasks.FtpTask.FtpTask // FTP
• Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceHistoryCleanupTask //History Cleanup
• Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceFileCleanupTask //Maintenance Cleanup
• Microsoft.SqlServer.Dts.Tasks.MessageQueueTask.MessageQueueTask, Microsoft.SqlServer.MSMQTask //Message Queue
• Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceNotifyOperatorTask //Notify Operator
• Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceReindexTask //Rebuild Index
• Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceDefragmentIndexTask //Reorganize Index
• Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask // Script
• Microsoft.SqlServer.Dts.Tasks.SendMailTask.SendMailTask // Send Mail
• Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceShrinkTask //Shrink Database
• Microsoft.SqlServer.Dts.Tasks.TransferDatabaseTask.TransferDatabaseTask // Transfer Database
• Microsoft.SqlServer.Dts.Tasks.TransferErrorMessagesTask.TransferErrorMessagesTask // Transfer Error Messages
• Microsoft.SqlServer.Dts.Tasks.TransferJobsTask.TransferJobsTask // Transfer Jobs
• Microsoft.SqlServer.Dts.Tasks.TransferLoginsTask.TransferLoginsTask // Transfer Logins
• Microsoft.SqlServer.Dts.Tasks.TransferStoredProceduresTask.TransferStoredProceduresTask // Transfer Master Stored Procedures
• Microsoft.SqlServer.Dts.Tasks.TransferSqlServerObjectsTask.TransferSqlServerObjectsTask // Transfer SQL Server Objects
• Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceUpdateStatisticsTask //Update Statistics
• Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTask // WQeb Service
• Microsoft.SqlServer.Dts.Tasks.WmiDataReaderTask.WmiDataReaderTask // WMI Data Reader
• Microsoft.SqlServer.Dts.Tasks.WmiEventWatcherTask.WmiEventWatcherTask // WMI Event Watcher
• Microsoft.SqlServer.Dts.Tasks.XMLTask.XMLTask, Microsoft.SqlServer.XMLTask // XML

Notice that the Execute Package doesn’t have a corresponding class – unfortunately we can’t use this method to obtain the Creation Name for it.
This works well for creating (most) Control Flow components, but for examining an existing package, this won’t be very efficient – we’d have to create an instance of every class’ type, get the creation name, and check it against the Control Flow component that’s in the package. So the final technique is to just write down the name strings and use them directly.
[Note: to obtain the Execute Package name, create the Control Flow using the SSIS Designer, save the package as XML, and examine the document]
With the long Creation Name, we do have to worry about versioning. The following are the long name strings from SSIS 2012:

• “Microsoft.DataTransformationServices.Tasks.DTSProcessingTask.ASExecuteDDLTask, Microsoft.SqlServer.ASTasks, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” //Analysis Services Execute DDL Task
• “Microsoft.DataTransformationServices.Tasks.DTSProcessingTask.DTSProcessingTask, Microsoft.SqlServer.ASTasks, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” //Analysis Services Processing Task
• “Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceBackupTask, Microsoft.SqlServer.MaintenancePlanTasks, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” //Back Up Database
• “Microsoft.SqlServer.Dts.Tasks.BulkInsertTask.BulkInsertTask, Microsoft.SqlServer.BulkInsertTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” // Bulk Insert
• “Attunity.SqlServer.CDCControlTask.CdcControlTask, Attunity.SqlServer.CDCControlTask, Version=1.0.0.0, Culture=neutral, PublicKeyToken=aa342389a732e31c” //CDC Control
• “Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceCheckIntegrityTask, Microsoft.SqlServer.MaintenancePlanTasks, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” //Check Database Integrity
• “SSIS.Pipeline.3” //Data Flow
• “Microsoft.SqlServer.Dts.Tasks.DMQueryTask.DMQueryTask, Microsoft.SqlServer.DMQueryTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” //Data Mining Query
• “Microsoft.SqlServer.Dts.Tasks.DataProfilingTask.DataProfilingTask, Microsoft.SqlServer.DataProfilingTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” //Data Profiling
• “SSIS.ExecutePackageTask.3” //Execute Package
• “Microsoft.SqlServer.Dts.Tasks.ExecuteProcess.ExecuteProcess, Microsoft.SqlServer.ExecProcTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” //Execute Process
• “Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” //Execute SQL
• “Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceExecuteAgentJobTask, Microsoft.SqlServer.MaintenancePlanTasks, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” //Execute SQL Server Agent Job
• “Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceTSQLExecuteTask, Microsoft.SqlServer.MaintenancePlanTasks, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” //Execute T-SQL Statement
• “Microsoft.SqlServer.Dts.Tasks.ExpressionTask.ExpressionTask, Microsoft.SqlServer.ExpressionTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” //Expression
• “Microsoft.SqlServer.Dts.Tasks.FileSystemTask.FileSystemTask, Microsoft.SqlServer.FileSystemTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” // File System
• “Microsoft.SqlServer.Dts.Tasks.FtpTask.FtpTask, Microsoft.SqlServer.FtpTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” // FTP
• “Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceHistoryCleanupTask, Microsoft.SqlServer.MaintenancePlanTasks, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” //History Cleanup
• “Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceFileCleanupTask, Microsoft.SqlServer.MaintenancePlanTasks, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” //Maintenance Cleanup
• “Microsoft.SqlServer.Dts.Tasks.MessageQueueTask.MessageQueueTask, Microsoft.SqlServer.MSMQTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” //Message Queue
• “Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceNotifyOperatorTask, Microsoft.SqlServer.MaintenancePlanTasks, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” //Notify Operator
• “Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceReindexTask, Microsoft.SqlServer.MaintenancePlanTasks, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” //Rebuild Index
• “Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceDefragmentIndexTask, Microsoft.SqlServer.MaintenancePlanTasks, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” //Reorganize Index
• “Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask, Microsoft.SqlServer.ScriptTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” // Script
• “Microsoft.SqlServer.Dts.Tasks.SendMailTask.SendMailTask, Microsoft.SqlServer.SendMailTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” // Send Mail
• “Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceShrinkTask, Microsoft.SqlServer.MaintenancePlanTasks, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” //Shrink Database
• “Microsoft.SqlServer.Dts.Tasks.TransferDatabaseTask.TransferDatabaseTask, Microsoft.SqlServer.TransferDatabasesTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” // Transfer Database
• “Microsoft.SqlServer.Dts.Tasks.TransferErrorMessagesTask.TransferErrorMessagesTask, Microsoft.SqlServer.TransferErrorMessagesTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” // Transfer Error Messages
• “Microsoft.SqlServer.Dts.Tasks.TransferJobsTask.TransferJobsTask, Microsoft.SqlServer.TransferJobsTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” // Transfer Jobs
• “Microsoft.SqlServer.Dts.Tasks.TransferLoginsTask.TransferLoginsTask, Microsoft.SqlServer.TransferLoginsTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” // Transfer Logins
• “Microsoft.SqlServer.Dts.Tasks.TransferStoredProceduresTask.TransferStoredProceduresTask, Microsoft.SqlServer.TransferStoredProceduresTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” // Transfer Master Stored Procedures
• “Microsoft.SqlServer.Dts.Tasks.TransferSqlServerObjectsTask.TransferSqlServerObjectsTask, Microsoft.SqlServer.TransferSqlServerObjectsTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” // Transfer SQL Server Objects
• “Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceUpdateStatisticsTask, Microsoft.SqlServer.MaintenancePlanTasks, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” //Update Statistics
• “Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTask, Microsoft.SqlServer.WebServiceTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” // WQeb Service
• “Microsoft.SqlServer.Dts.Tasks.WmiDataReaderTask.WmiDataReaderTask, Microsoft.SqlServer.WMIDRTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” // WMI Data Reader
• “Microsoft.SqlServer.Dts.Tasks.WmiEventWatcherTask.WmiEventWatcherTask, Microsoft.SqlServer.WMIEWTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” // WMI Event Watcher
• “Microsoft.SqlServer.Dts.Tasks.XMLTask.XMLTask, Microsoft.SqlServer.XMLTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” // XML

Wiring it together:
Each of the Control Flow Containers and the Package class has a PreecedenceConstraints collection, which controls the workflow of the Control Flow space. While we could create a PrecedenceConstraint, and then join it into the collection, it’s much easier to simply use the Add method:

public override void SetFlow(ControlFlow source, ControlFlow destination)
{
    Executable sourceEx = source.Ex;
    Executable destEx = destination.Ex;
    PrecedenceConstraints constraints = pkg.PrecedenceConstraints;
    PrecedenceConstraint seqConstraint = constraints.Add(sourceEx, destEx);

}

PrecedentConstraints themselves are more complicated than shown here, but most people leave the default properties (simple constraint, progress upon success, logical AND) so we’ll leave that for another post.

Resources:

• http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.connectionmanager.aspx

Working with Connections via the API, we start to see a little bit more complexity, and some deviation from what the SSIS Designer exposes to us.

A brief aside:
Although we’re working with a .NET API, it’s really a thin veneer over the actual COM object (for instance, the collections will only implement ICollection and IEnumerable). This will explain some of the design choices, and especially many of the error messages we get.

The ConnectionManager is the class describing what the SSIS Designer calls a Connection. Also, like the Variable, the ConnectionManager is weakly typed! And the technique for creating one is a bit idiosyncratic:

//using Microsoft.SqlServer.Dts.Runtime;
//Package pkg; - a loaded Package
//string connType; - the type of connection desired
ConnectionManager newConn = pkg.Connections.Add(connType);

OK, it’s not exactly weakly typed; it’s that each of the (presumably strongly typed COM object) is covered with the same .NET class shell. Just like with the Variable class, the Package (only the Package, this time) has a Connections property to expose all existing ConnectionManagers. However, the Connections.Add() method builds a new ConnectionManager based on the input string (yes, really). The documentation lists some common types, but here’s a full list:

• “FLATFILE” // flat file
• “OLEDB” //OLE DB
• “ADO” //ADO
• “ADO.NET” //ADO.NET
• “MSOLAP100” //Analysis Services
• “CACHE” // Cache
• “DQS” // DQS
• “EXCEL” //Excel
• “FILE” // File
• “FTP” // FTP
• “HTTP” // HTTP
• “MSMQ” // MSMQ
• “MULTIFILE” // MultiFile
• “MULTIFLATFILE” // MultiFlatFile
• “ODBC” // ODBC
• “SMOServer” // SMO
• “SMTP” // SMTP
• “SQLMOBILE” // SQL Server Compact
• “WMI” // WMI

What’s really going on:
The ConnectionManager .NET class may appear to be weakly typed, but it’s really a wrapper to the COM object, and the COM classes underneath almost assuredly aren’t. The .NET API’s strategy is to expose properties common to all types in the ConnectionManager class, and then serve up all the extra properties in a properties collection.
It also means that checking existing ConnectionManager objects for their types is a little tricky:

foreach (ConnectionManager connmgr in pkg.Connections)
{
    if (connmgr.CreationName == "FLATFILE")
    {
        Console.WriteLine("Package has a FlatFile Connection");
    }
}

The same strings used to create a new connection are what we look for in the CreationName property…except for ADO.NET. The ADO.NET CreationName will look like this:
“ADO.NET:System.Data.SqlClient.SqlConnection, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089”

It’s no good looking for this text specially, either. Notice the “System.Data.SqlClient.SqlConnection” substring – that will obviously change, depending on the ADO.NET driver used. My suggestion here is to look for the regular strings first, and then check if the string starts with “ADO.NET:”.

Now that we have a connection, what interesting properties are there?
Name – gets/set the Connection name (otherwise it’ll be a weird-looking GUID).
ConnectionString – gets/sets the Connection’s Connection String
Properties – the DtsProperty collection for all the connection-type-specific stuff

Since the Properties collection is a key/value store, there’s no good way to find out the properties for a given Connection type. I therefore recommend simply creating the Connection you’re interested in, and iterating through them:

foreach (DtsProperty prop in connmgr.Properties)
{
    Console.WriteLine("name: " + prop.Name + ", value: " + (prop.GetValue(connmgr) == null ? "" : prop.GetValue(connmgr).ToString());
}

Once we know the property name, though, we can use it as an index:

DtsProperty formatprop = connmgr.Properties["Format"];
Console.WriteLine("name: " + formatprop.Name + ", value: " + formatprop.GetValue(connmgr));

Note the peculiar syntax here of GetValue():

When you request the value of a property by calling the GetValue method, the object that you must pass as a parameter is the object to which the property belongs. For example, if you are working with an OLE DB connection manager as shown in the following example, and you have created a DtsProperty object for its ServerName property, then you would pass the connection manager object as the parameter to the GetValue method.

which leads to silly-looking code like this:

connmgr.Properties["Format"].GetValue(connmgr);

But because of COM Interop, we’ll never do that in production code, as we need
Deterministic Garbage Cleanup:

DtsProperty formatprop = connmgr.Properties["Format"];
formatprop.GetValue(connmgr);

Resources:

• http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.variable.aspx

Starting easy…
First things first, add a reference to the Microsoft.SqlServer.Dts.Runtime, parent of the Microsoft.SQLServer.ManagedDTS assembly (which can be found in the GAC; note the “SQL” capitalization).
Then, creating a variable is simple:

//using Microsoft.SqlServer.Dts.Runtime;
//Package pkg; - a loaded Package
Variable ssisVar = pkg.Variables.Add("myVariable", false, "User", null);

The Variables collection (found in Packages, but also Containers; more on this in a future post) contains an Add method with four parameters:
string name – variable name
bool readOnly – ReadOnly/ReadWrite
string nameSpace – namespace for variable. “User” is typical. “System” is not allowed.
Object val – design-time value for variable. Note that we can set this later

One immediate surprise is that, contrary to what we’d expect from the SSIS Designer, variables are weakly-typed! [The SSIS Designer is sneaky] Setting a value to NULL allows us to punt on making that data type choice until later:

ssisVar.Value = 3;

Once we’ve done this, we can determine the DataType of the variable (note that this is read-only).

Alternately, we can set the Variable to be the result of an
expression:

ssisVar.Value = 3;
ssisVar.EvaluateAsExpression = true;
ssisVar.Expression = "3 * 3";

though it can’t reference other Variables.

Finally, browsing for existing variables is easy:

//using Microsoft.SqlServer.Dts.Runtime;
//Package pkg; - a loaded Package
foreach (Variable variable in pkg.Variables)
{
    Console.WriteLine(variable.QualifiedName);
}