Tutorial Gateway

  • C
  • C#
  • Java
  • Python
  • SQL
  • MySQL
  • Js
  • BI Tools
    • Informatica
    • Talend
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • R Tutorial
    • Alteryx
    • QlikView
  • More
    • C Programs
    • C++ Programs
    • Python Programs
    • Java Programs

For Loop Container in SSIS

by suresh

The For Loop Container in SSIS will repeat the SQL Integration Services tasks for a given number of times until the given condition is False. It is the same as the For Loop in any programming language. In this chapter, we show you the step by step approach to configure the For Loop container in SSIS with example.

Before we start working with the SSIS For Loop Container, Let me create an Empty Table in SQL Server:

-- For Loop Container in SSIS Example
USE [SQL Tutorial]
GO

CREATE TABLE [dbo].[SSISForLoopExample](
 [Key] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
 [CounterNumber] [int] NULL,
 [LoopNumber] [varchar](50) NULL,
 [LastUpdated] [datetime] NULL,
)
GO

For Loop Container in SSIS Configuration

In this example, we will create a variable. Next, we are going to increment that variable by 1 using the For Loop, and then save that value in the table that we created above. To do so, First Drag and drop the SSIS For Loop Container into the Control Flow region

For Loop Container in SSIS 1

Double click on it will open the SSIS For Loop Editor. You can use this editor to configure it. Please refer For Loop in C Programming to understand the programming flow of the For Loop in the step by step manner. Even if you don’t know C language, it helps you understand the loop concept.

  • InitExpression: For loop starts with the initialization statement so, initialization of the counter variable should be done first (For example, @counter = 1 or @i = 1.).
  • EvalExpression: The value of the counter variable tested against the condition. If the condition is True, it will execute the For loop container. Otherwise, For loop terminated.
  • AssignExpression: This expression will execute after the end of each iteration. It helps to increase or decrease the counter variable as per our requirement.

Under the SSIS For Loop Container General section, Please specify valid and more meaningful Name and Description.

For Loop Container in SSIS 2

I think I forgot to create a variable that can use in the For Loop. So, let me close the for Loop editor, and right-click on the design will open the context menu. Please select the Variables option.

For Loop Container in SSIS 3

As you can from the below screenshot, we created a variable called CounterNumber of Integer type and assigned the default value is 0.

For Loop Container in SSIS 4

Next, open the For Loop Editor, and assign the appropriate values. From the below, For Loop will start at 0, will execute until it reaches to 10, and for each iteration, CounterNumber value incremented by 1.

For Loop Container in SSIS 5

Click OK to close the SSIS For Loop Container. Let me drag and drop the Execute SQL Task inside the For Loop Container. We are going to use this Execute SQL task to INSERT data into the SQL table.

For Loop Container in SSIS 6

Double click on it will open the Execute SQL Task Editor to configure it. Let me select the Connection Type as ADO.NET Connection, which is connecting to the SQL Tutorial database. Next, we are using the Direct Input as the SQL statement, so click the … button.

For Loop Container in SSIS 7

Please write your custom SQL statement here. As you can from the below screenshot, we are writing an SQL Insert statement to insert data into SSIS For Loop Example table.

-- For Loop Container in SSIS Example
INSERT INTO [dbo].[SSISForLoopExample]
           ([CounterNumber]
           ,[LoopNumber]
           ,[LastUpdated])
     VALUES
           (@CounterNumber
           ,'Loop Number' + CAST(@CounterNumber AS VARCHAR(50))
           ,getdate())
For Loop Container in SSIS 8

Verify the remaining SSIS For Loop Container settings

For Loop Container in SSIS 9

Next, go to the SSIS For Loop Container parameter settings to assign the parameter that we used in the SQL Command. As you can see, we assigned the User variable (Variable we are getting from the For Loop) to Parameter Name (this is the name that we used in SQL Statement)

For Loop Container in SSIS 10

Click OK to finish configuring the Execute SQL Task parameter Mapping. Let us Run the SSIS For Loop container package

For Loop Container in SSIS 11

Let me open the SQL Server management studio, and check whether we inserted the records multiple counter values into the destination table using SSIS For Loop container or not

For Loop Container in SSIS 12

Placed Under: SSIS

  • What is SSIS
  • Install SQL Server Data Tools
  • Learn SSIS in 28 Days
  • Create a SSIS Project
  • Create SSIS Package
  • SSIS Connection Manager
  • OLE DB Connection Manager
  • SSIS ADO Connection Manager
  • ADO.NET Connection Manager
  • SSIS Cache Connection Manager
  • SSIS Excel Connection Manager
  • SSIS File Connection Manager
  • SSIS FTP Connection Manager
  • SSIS SMO Connection Manager
  • Source Assistance in SSIS
  • Flat File Source in SSIS
  • OLE DB Source in SSIS
  • Excel Source in SSIS
  • ADO.NET Source in SSIS
  • SSIS FLAT FILE Destination
  • SSIS OLE DB Destination
  • SSIS ADO.NET Destination
  • SSIS Transformations
  • SSIS Audit Transformation
  • SSIS Aggregate Transformation
  • SSIS Aggregate Advanced Mode
  • SSIS Cache Transformation
  • SSIS Character Map
  • SSIS Conditional Split
  • SSIS Copy Column
  • SSIS Data Conversion
  • SSIS Derived Column
  • SSIS Export Column
  • SSIS Fuzzy Grouping
  • SSIS Fuzzy Lookup
  • SSIS Import Column
  • SSIS Lookup Introduction
  • Lookup – OLE DB Connection
  • SSIS Lookup in Full Cache Mode
  • SSIS Lookup – Case Sensitivity
  • SSIS Merge Transformation
  • SSIS Merge Join Transformation
  • SSIS Merge Join – Left Outer Join
  • SSIS Merge – Right Outer Join
  • SSIS Merge Join – Full Outer Join
  • SSIS Multicast Transformation
  • SSIS OLEDB Command
  • OLEDB Command – Delete Data
  • OLEDB Command-Update Data
  • SSIS Percentage Sampling
  • SSIS Pivot Transformation 2008
  • SSIS Pivot Transformation
  • SSIS Row Count Transformation
  • SSIS Row Sampling
  • Script Component as Source
  • Script Component as Destination
  • SSIS Script as Transformation
  • SSIS Sort Transformation
  • SSIS SCD Type 0
  • SSIS SCD Type 1
  • SSIS SCD Type 2
  • SSIS Term Lookup
  • SSIS Term Extraction Intro
  • Term Extraction – Extract Nouns
  • SSIS Extract Noun Phrases
  • Extract Nouns & Noun Phrases
  • Term Extraction – Exclusion Tab
  • SSIS Unpivot Transformation
  • SSIS Union All Transformation
  • SSIS For Loop Container
  • SSIS ForEach File Enumerator
  • SSIS ForEach SMO Enumerator
  • SSIS ForEach Variable
  • SSIS Foreach NodeList
  • Foreach ADO.NET Schema Rowset Enumerator
  • SSIS Bulk Insert Task
  • SSIS Data Profiling Task
  • Execute T-SQL Statement Task
  • SSIS Execute SQL Task Intro
  • SSIS Execute SQL Task Example
  • Execute SQL Task- Single Rowset
  • Execute SQL Task – Full Row Set
  • SSIS Execute Package Task
  • Execute Packages in SQL Server
  • Execute Packages in File System
  • SSIS Execute Package Project Reference
  • SSIS File System Task
  • SSIS File System Task- Copy Files
  • File System Task-Copy Directory
  • File System Task – Delete Files
  • File System Task – Delete Folder
  • File System Task -Move Directory
  • SSIS File System Task -Move File
  • File System – Move Multiple files
  • File System Task – Rename File
  • File System Task – Set Attributes
  • SSIS FTP TASK
  • SSIS Create Local Directory
  • SSIS Create Remote Directory
  • SSIS FTP Task Send Files
  • SSIS FTP – Send Multiple Files
  • SSIS FTP Task Delete Local Files
  • FTP TASK Delete Local Directory
  • FTP Task Delete Remote files
  • SSIS Delete Remote Directory
  • SSIS FTP Task Receive Files
  • SSIS FTP Receive Multiple Files
  • SSIS Script Task
  • Transfer SQL Server Objects Task
  • Transfer SQL Table Structures
  • Transfer SQL Tables with Data
  • Transfer SQL Stored Procedures
  • Transfer User Defined Functions
  • Transfer SQL Views in SSIS
  • SSIS Web Service Task
  • SSIS XML Task-Validate XML File
  • Transform XML File using XSLT
  • XML Task-XML files Differences
  • Create SSIS Catalog
  • Package Deployment using BIDS
  • Deploy Package Using SQL
  • Deploy using SQL Server Wizard
  • SSIS Breakpoints
  • SSIS Checkpoints
  • SSIS Error Handling
  • SSIS Event Handlers
  • SSIS Transactions
  • SSIS Logging
  • SSIS Parameters
  • SSIS Package Configuration
  • Configure using SQL Server
  • Config using Registry Entry
  • Conf with Environment Variable
  • SSIS XML Configuration File
  • XML Configuration File Part 2
  • SSIS Package Protection Level
  • SSIS Incremental Load
  • Incremental Load Example 2
  • SSIS Remove Double Quotes
  • C Tutorial
  • C# Tutorial
  • Java Tutorial
  • JavaScript Tutorial
  • Python Tutorial
  • MySQL Tutorial
  • SQL Server Tutorial
  • R Tutorial
  • Power BI Tutorial
  • Tableau Tutorial
  • SSIS Tutorial
  • SSRS Tutorial
  • Informatica Tutorial
  • Talend Tutorial
  • C Programs
  • C++ Programs
  • Java Programs
  • Python Programs
  • MDX Tutorial
  • SSAS Tutorial
  • QlikView Tutorial

Copyright © 2021 | Tutorial Gateway· All Rights Reserved by Suresh

Home | About Us | Contact Us | Privacy Policy