SSIS Package with Dynamic ADO Connection

SSIS Package with Dynamic ADO Connection.

I have created a package that collects database information from different instances all over the network and stored in one central database table.

For this, I have collected Server Name, Database Name, Logical File Name, Physical File Name and File Size in MB.

I have stored all instance info in my central database table.

Table Script.

Create Table SQLServerInstances

    ServerName Varchar(20),
    DBName Varchar(20),

    UserName Varchar(20),

    PWD Varchar(20)
Insert Into SQLServerInstances Values('yourLocalMachineName','AdventureWorks2012','sa','password')

 I had taken one Execute SQL Task to get list of instances then move it to Foreach loop and store the values to database table using Data Flow Task.

Step1 : Drag Execute SQL Task, Foreach Loop Container and Data Flow task under Foreach Loop Container.

DynamicConnection 1

Step2 : Create Variables.

–> Obj_Result : DataType Object.

–> ServerName : DataType String.

–> DBName : DataType String.

–> UserName1 : DataType String.

–> PWD : DataType String.

DynamicConnection 2

Step3 : Right click on Execute SQL Task –> Edit and fill the details as shown in below figure.

DynamicConnection 3

Step4 : Select Result Set on left pane of Execute SQL Task and select your object variable.

DynamicConnection 4

Step5 : Right Click on Foreach Loop Container –> Edit.

Select Foreach ADO Enumerator in Enumerator and Select Obj_Result in ADO Object source varialbe field.

DynamicConnection 5

Step6 : Select Variable Mapping on left pane of Foreach Loop Container and select varialbes as you see in below figure.

DynamicConnection 6

Step7 : Now Right click on Data Flow Task –> Edit.

DynamicConnection 7

Step8 : Right click on OLE DB source. create one OLEDB data connection with name Dynamic Connection with your local database connection.

SQL Command.

SELECT @@ServerName as ServerName,
   DB_NAME(database_id) AS DatabaseName,
   Name AS LogicalName ,
   Physical_Name as PhysicalName,
   (size*8)/1024 DBSize
FROM sys.master_files

DynamicConnection 8

Step9 : Select Columns from left pane of OLE DB Source Editor and confirm Columns.

DynamicConnection 9

Step10 : Right Click on Data Conversion Task –> Edit and set data types for columns.

DynamicConnection 10

Step11 : Create one database table to store all servers’s database info in central database.

Create Table DatabaseInfo
    ServerName Varchar(20),
    DBName varchar(20),
    LogicalName Varchar(200),
    PhysicalName Varchar(200),
    DBSize int

DynamicConnection 11

Step12 : Right click on mapping on left pane of OLE DB Destination Editor and map the varialbes to table columns.

DynamicConnection 12

Step13 : Now right click on “DynamicConnection” that you have created before to make it dynamic.

Rikght click on it and click on Property.

Select Expression field button that will open one popup screen.

DynamicConnection 13

Map DynamicConnection’s parameter with varialbes to make it dynamic and run the project.


Nirav gajjar

SSIS Package Foreach Loop with File Connection

SSIS Package Foreach Loop with File Connection.

This package gives you an idea to get file details and variable mapping between Foreach Loop Container and any inner task contains in loop (here inner task is Script Task).

Step1 : Drag Foreach Loop and script task from SSIS Toolbox.


Step2 : Create one variable with name “FileName” and datatype “string”.
this varialbe maps filename from Foreach Loop Container value to Script task.

Step3 : Right click on Foreach Loop Container –> Edit
On Collection pane –> select Foreach File Enumerator in Enumerator field.
choose your folder by clicking on browse button.
–> Select file extention if you want to filter file extention otherwise put *.* to get all files.
–> Select Name and Extention in Retrieve file name.

Step4 : Select Variable mapping pane on Foreach Loop Editor.
–> Select User::FileName variable.
–> and Index to 0.

Step5 : Right Click on Script Task –> Edit.
–> Select Script Pane –> Select User::FileName as ReadOnlyVarialbe.

Step6 : Click on Edit Scrit to write script code.

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

namespace ST_f1f3822404bc4e8d9a22fbe2749ff1d9
	public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
		public void Main()
            string FileName;
            FileName = Dts.Variables["User::FileName"].Value.ToString();
			Dts.TaskResult = (int)ScriptResults.Success;

        enum ScriptResults
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure


Nirav gajjar