The SSIS ForEach Loop container will repeat the control flow task for N number of times, which is similar to Foreach loop in any programming language. The SQL Server Integration Services provides 8 types of enumerators and In this article we will show you the steps involved in configuring the SSIS ForEach Loop SMO Enumerator.
SMO Enumerator: SMO means SQL Server management Object and this is used to enumerate SQL Server management Objects. For example, it can enumerate the list of tables, Views etc present in Database.
SSIS ForEach Loop SMO Enumerator
In this example, we will enumerate tables present in AdventurWorksDW 2014 database and copy the Table names into another database table. In order to do so, First Drag and drop the Foreach Loop Container into the Control Flow region as shown below
Double click on it will open the Foreach Loop Editor to configure it. Within the General tab, Please specify valid and more meaningful Name and Description. From the below screenshot you can see that, we changed the default name to SSIS ForEach Loop SMO Enumerator
Next go to the Collections tab to select the Enumerator and to configure the connections. Before we step into the remaining properties, we have to understand the Enumerator property. From the below screenshot you can observe that, we are selecting Foreach SMO Enumerator because we want to loop over the tables present in our AdventurWorksDW 2014 database.
Next, we have to configure the Connection Manger. So, click on the down arrow button and select the New Connection.. button to create new one or else select the existing connection manager (if any).
Once you click on the New Connection.. button, SMO Connection manager Editor will be opened to configure it. For this example, we are selecting suresh as server instance and we are using Windows authentication.
TIP: Please refer SMO Connection Manger in SSIS article to understand the properties.
Click on the Browse button to select the Tables present in our Database. From the below screenshot you can observe that we are selecting the AdventureWorksDW2014 database table names.
Next, we have to store the file names into a variable so that we can use it in our Execute SQL Task. In order to do that, go to Variable Mappings section and select the user variable (if any) or create new variable as we shown below
Once you click on the <New Variable..>, Add Variable window will be opened to create new variable. In this example, we require variable to store the Table Name so we are creating string variable and assigned the name as Table_Name. Click OK to finish creating string variable.
Now select the newly created variable and assign it to index 0 as we shown in below screenshot. Once you are done, Click OK to finish configuring the Foreach Loop Container.
Next, Drag and drop the Execute SQL Task from toolbox to Foreach loop container. This task will help us to insert the new values into our Empty table.
Double click on it will open the Execute SQL Task Editor window. From the below screenshot you can observe that, we selected OLE DB as Connection type and selected the existing connection which is pointing to [SSIS Tutorials] Database.
TIP: Please refer the OLE DB Connection manager article to understand the steps involved in creating new OLE DB Connection.
Next, we are writing the following SQL Query to insert the table name into SMO_Enumerator table.
INSERT INTO SMO_Enumerator ([Table_Name]) VALUES (?)
Here question mark will be replaced by the parameter.
Next, go to parameter mapping and select the parameter that we assigned in Foreach Loop container. It means, Foreach loop container will loop through the tables present in AdventureWorksDW database and store the table names in User::Table_name variable. Next we are passing those table names into Execute SQL task
Click OK to finish configuring the SSIS ForEach Loop SMO Enumerator package. Let us Run the package
Let us open the SQL Server management studio and check whether we inserted the tables names into the destination table using SSIS ForEach Loop SMO Enumerator or not
Thank You for Visiting Our Blog