CaseI 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.
SolutionYou 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 solutionAdd 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 Enumerator1) VariablesAdd an Object variable and name it connectionManagers and add a String variable named connectionString.
 |
| Variables |
2) Script TaskAdd a Script Task to the Control Flow and give it a suitable name.
 |
| Script Task |
3) ReadWriteVariableAdd the object variable from step 1 as ReadWriteVariable.
 |
| ReadWriteVariable |
4) The ScriptEdit 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 LoopAdd 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 MappingGo to the Variable Mappings tab and add the String variable from step 1.
 |
| Variable Mappings |
7) The ResultI added a Script Task with a Messagebox to test the loop.
 |
| The result |
