Search Suggest

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

Post a Comment