In this article, we will show you the steps involved in configuring the SSIS ForEach Loop SMO Enumerator.
SSIS ForEach Loop SMO Enumerator: SMO means SQL Server Management Object, which is used to enumerate SQL Server Management Objects. For example, SSIS ForEach Loop SMO Enumerator can enumerate the list of tables, Views, etc., present in the 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.
To do so, First Drag and drop the Foreach Loop Container into the SSIS Control Flow region
Double-click on it will open the Foreach Loop Editor to configure it. Within the General tab, Please specify valid and more meaningful Names and Descriptions. From the below, 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 configure the messengers. Before we step into the remaining properties, we must understand the Enumerator property. Here, 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 Manager. So, click on the down arrow button and select the New Connection..button to create a new one, or else select the existing connection manager (if any).
Once you click on the New Connection.. button, SMO Connection Manager Editor will open to configure it. For this SSIS ForEach Loop SMO Enumerator example, we are selecting suresh as a server instance and using Windows authentication.
TIP: Please refer SMO Connection Manager 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 choosing the AdventureWorksDW2014 database table names.
Next, we have to store the file names into a variable to use in our Execute SQL Task. To do that, go to the Variable Mappings section and select the user variable (if any) or create a new variable
Once you click on the <New Variable..>, the Add Variable window opens to create a new variable. In this example, we require variables to store the Table Name. So, we created a string variable and assigned the name as Table_Name. Click OK to finish creating a string variable.
Now select the newly created variable and assign it to index 0. Once you have completed, Click OK to finish configuring the Foreach Loop Container.
Next, Drag and drop the Execute SQL Task from the toolbox to the 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 the Connection type and selected the existing connection, which is pointing to the Database.
Next, we are writing the following SQL Query to insert the table name into the 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 the Foreach Loop container. It means the Foreach loop container will loop through the tables present in the AdventureWorksDW database and store the table names in the User:: Table_name variable. Next, we pass 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 names of the tables into the destination table using SSIS ForEach Loop SMO Enumerator or not
