Loop through 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
// 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;
        }
    }
}

or with VB.Net

' VB.Net code
' This isn't a complete solution.
' There are a couple of examples.
' Adjust them to you own needs.  
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO ' Added

<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum

    Public Sub Main()
        ' Used for information events
        Dim fireAgain As [Boolean] = True


        ' Loop through all your package connections
        For Each connectionManager As 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") Then
                If File.Exists(connectionManager.ConnectionString) Then
                    Dts.Events.FireInformation(-1, "Connection Manager Loop", connectionManager.Name + " (" + connectionManager.ConnectionString & ") does exist", String.Empty, 0, fireAgain)
                Else
                    Dts.Events.FireError(-1, "Connection Manager Loop", connectionManager.Name + " (" + connectionManager.ConnectionString & ") does not exist", String.Empty, 0)
                End If
            End If


            ' Optional filter on PREFIX. For example starting with "tmp"
            ' Let's delete all tmp files.
            If connectionManager.Name.StartsWith("tmp") Then
                Try
                    File.Delete(connectionManager.ConnectionString)
                    Dts.Events.FireInformation(-1, "Connection Manager Loop", connectionManager.Name + " (" + connectionManager.ConnectionString & ") was deleted", String.Empty, 0, fireAgain)
                Catch Ex As Exception
                    Dts.Events.FireError(-1, "Connection Manager Loop", connectionManager.Name + " (" + connectionManager.ConnectionString & ") not deleted. Error: " & Ex.Message, String.Empty, 0)
                End Try
            End If


            ' Optional filter on TYPE.
            ' Let's move all excel files to a certain folder.
            If connectionManager.CreationName.Equals("EXCEL") Then
                ' 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";
                Dim ExcelFilePath As String = connectionManager.Properties("ExcelFilePath").GetValue(connectionManager).ToString()

                Dim myExcelFile As New FileInfo(ExcelFilePath)
                File.Move(ExcelFilePath, "D:\MyExcelFiles\Archive\" & myExcelFile.Name)
            End If


            ' Just log all connection managers
            Dts.Events.FireInformation(-1, "Connection Manager Loop", connectionManager.Name + " (" + connectionManager.ConnectionString & ")", String.Empty, 0, fireAgain)
        Next

        Dts.TaskResult = ScriptResults.Success
    End Sub
End Class

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;
        }
    }
}

or VB.Net

' VB.Net code
' This isn't a complete solution.
' There are a couple of examples.
' Adjust them to you own needs.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
 Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

 Enum ScriptResults
  Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
  Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
 End Enum
 
    Public Sub Main()
        ' Create array list for storing the connection managers
        Dim connectionManagers As New System.Collections.ArrayList()

        ' Loop through all your package connections
        For Each connectionManager As 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") Then
                ' Add item to array list
                connectionManagers.Add(connectionManager.ConnectionString)
            End If


            ' Optional filter on TYPE.
            ' Let's move all excel files to a certain folder.
            If connectionManager.CreationName.Equals("EXCEL") Then
                ' 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";
                Dim ExcelFilePath As String = connectionManager.Properties("ExcelFilePath").GetValue(connectionManager).ToString()

                ' Add item to array list
                connectionManagers.Add(ExcelFilePath)
            End If
        Next

        ' Fill object variable with array list
        Dts.Variables("User::connectionManagers").Value = connectionManagers

        Dts.TaskResult = ScriptResults.Success
    End Sub
End Class

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