SMO enumerator in Foreach Loop container in SSIS

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.SMO enumerator to enumerate SQL Server Management Objects (SMO) objects. SMO enumerator is use to loop the sql server object. Object may be

Ø  Tables

Ø  View

Ø  Sql server agent

Ø  Linked servers

Ø  SP etc.

Here I am taking one example. I want to get the list of table name in from the TestSSIS Database.

  

Let’s see how we use SMO enumerator

Take the Foreach loop container

Double click on the container

  

Go to Collection and select Foreach SMO Enumerator

Now we need to create a connection.

Create a new connection. Also select the Authentication mode and click ok.

Now click on the Browser

And select the Database object which we want to enumerate.

Here I am taking Table

I am selecting Database and TestSSIS

Select Enumeration type is Name. Click ok.

Database[@Name='TestSSIS']/SMOEnumObj [@Name='Tables']/SMOEnumType[@Name='Names']

Now we need to map the variable. Now we need to create a variable which store the name of the table.

  

Click ok.

I have created a table on different Database to store the table name.

Now I am taking Execute Sql task to insert the table name into a table.

Now I am creating the required connection.

Click ok and Map the parameter

Click ok.

Now we are ready to run this package. Let’s see the result.

Package executed successfully. Let’s see the result.

  

Hope this will be helpful to you.