SSIS - Loop through recordset and load into multiple file dynamically

In this article let us see an example on
*) how to loop through records in a table in SSIS
*) how to load into multiple (.sql) file dynamically

Consider the requirement is to generate index script for tables that belong to particular schemas and export as separate .sql files.

Long back I wrote an article on How to Generate Index Creation Scripts for all Tables in a Database using T-SQL

Going to create this script as Stored procedure:







In this example, I am going to work with Adventureworks database.
As explained earlier, going to loop through tables list that come under schema :

SELECT Schema_name(Schema_id)+'.'+name TableName,Schema_name(Schema_id) SchemaName
FROM Sys.tables
WHERE Schema_id IN (7,8,9)
ORDER BY SchemaName

--Production
--Purchasing
--Sales

and generate index script using Stored procedure

EXEC GenerateIndex @TableName

and export as separate .sql files.

 Tasks that needs to be added in Control Flow & Data Flow are as follows:






Below screenshots shows variables and respective task settings:

 

See Also:

• My SSIS articles
• My T-SQL articles