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 :
--Production
--Purchasing
--Sales
and generate index script using Stored procedure
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:











*) 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:
No comments:
Post a Comment