SSIS: Loop over all connection managers

Case
I want log all my connection managers, delete all my excel files, check whether my flat files exists. And I don’t want to add task for all of them.Solution
You can use a Script Task to loop through the collection of Connection Managers and do something with the connection manager (log, delete, check, etc.). Either do the task in .Net code or fill a SSIS object variable which can be used in Foreach From Variable Enumerator. I will show you both.Note: these solutions don’t work for dynamic Connection Managers. For example with a foreach loop and expressions on the connectionstring.

A) Script Task only solution
Add a Script Task to the Control Flow and edit it. Copy the code of this main method to your main method. There are a couple of examples so adjust it to your own needs.

C# Code:
// C# Code
// This isn’t a complete solution.
// There are a couple of examples.
// Adjust them to you own needs.
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO; // Added
namespace ST_ae645b8974b54c7abd6d5058ded524b6.csproj
{
[System.AddIn.AddIn(“ScriptMain”, Version = “1.0”, Publisher = “”, Description = “”)]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion

public void Main()
{
// Used for information events
Boolean fireAgain = true;

// Loop through all your package connections
foreach (ConnectionManager connectionManager in Dts.Connections)
{
// Optional filter on TYPE. For example FLATFILE, OLEDB, EXCEL
// Let’s check if the flatfile exists
if (connectionManager.CreationName.Equals(“FLATFILE”))
{
if (File.Exists(connectionManager.ConnectionString))
{
Dts.Events.FireInformation(-1, “Connection Manager Loop”, connectionManager.Name + ” (” + connectionManager.ConnectionString + “) does exist”, string.Empty, 0, ref fireAgain);
}
else
{
Dts.Events.FireError(-1, “Connection Manager Loop”, connectionManager.Name + ” (” + connectionManager.ConnectionString + “) does not exist”, string.Empty, 0);
}
}

// Optional filter on PREFIX. For example starting with “tmp”
// Let’s delete all tmp files.
if (connectionManager.Name.StartsWith(“tmp”))
{
try
{
File.Delete(connectionManager.ConnectionString);
Dts.Events.FireInformation(-1, “Connection Manager Loop”, connectionManager.Name + ” (” + connectionManager.ConnectionString + “) was deleted”, string.Empty, 0, ref fireAgain);
}
catch (Exception Ex)
{
Dts.Events.FireError(-1, “Connection Manager Loop”, connectionManager.Name + ” (” + connectionManager.ConnectionString + “) not deleted. Error: ” + Ex.Message, string.Empty,0);
}
}

// Optional filter on TYPE.
// Let’s move all excel files to a certain folder.
if (connectionManager.CreationName.Equals(“EXCEL”))
{
// Excel connection string contains a lot more then just the file path. We need to extract the ExcelFilePath property
// Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\MyExcelFile.xls;Extended Properties=”EXCEL 8.0;HDR=YES;IMEX=1″;
string ExcelFilePath = connectionManager.Properties[“ExcelFilePath”].GetValue(connectionManager).ToString();

FileInfo myExcelFile = new FileInfo(ExcelFilePath);
File.Move(ExcelFilePath, @”D:\MyExcelFiles\Archive\” + myExcelFile.Name);
}

// Just log all connection managers
Dts.Events.FireInformation(-1, “Connection Manager Loop”, connectionManager.Name + ” (” + connectionManager.ConnectionString + “)”, string.Empty, 0, ref fireAgain);
}

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

B) Foreach Connection Manager Enumerator

1) Variables
Add an Object variable and name it connectionManagers and add a String variable named connectionString.

Variables

2) Script Task
Add a Script Task to the Control Flow and give it a suitable name.

Script Task

3) ReadWriteVariable
Add the object variable from step 1 as ReadWriteVariable.

ReadWriteVariable

4) The Script
Edit the script and copy the code from the main method to your main method.

// C# Code
// This isn’t a complete solution.
// There are a couple of examples.
// Adjust them to you own needs.
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;namespace ST_ae645b8974b54c7abd6d5058ded524b6.csproj
{
[System.AddIn.AddIn(“ScriptMain”, Version = “1.0”, Publisher = “”, Description = “”)]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{

#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion

public void Main()
{
// Create array list for storing the connection managers
System.Collections.ArrayList connectionManagers = new System.Collections.ArrayList();

// Loop through all your package connections
foreach (ConnectionManager connectionManager in Dts.Connections)
{
// Optional filter on TYPE. For example FLATFILE, OLEDB, EXCEL
// Let’s check if the flatfile exists
if (connectionManager.CreationName.Equals(“FLATFILE”))
{
// Add item to array list
connectionManagers.Add(connectionManager.ConnectionString);
}

// Optional filter on TYPE.
// Let’s move all excel files to a certain folder.
if (connectionManager.CreationName.Equals(“EXCEL”))
{
// Excel connection string contains a lot more then just the file path. We need to extract the ExcelFilePath property
// Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\MyExcelFile.xls;Extended Properties=”EXCEL 8.0;HDR=YES;IMEX=1″;
string ExcelFilePath = connectionManager.Properties[“ExcelFilePath”].GetValue(connectionManager).ToString();

// Add item to array list
connectionManagers.Add(ExcelFilePath);
}
}

// Fill object variable with array list
Dts.Variables[“User::connectionManagers”].Value = connectionManagers;

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

5) Add Foreach Loop
Add a foreach loop and choose Foreach From Variable Enumerator. Select the Object variable from step 1 as the variable to loop through.

Foreach From Variable Enumerator

6) Variable Mapping
Go to the Variable Mappings tab and add the String variable from step 1.

Variable Mappings

7) The Result
I added a Script Task with a Messagebox to test the loop.

The result
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s