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.
