Problem: What are containers
and how does a container work in SSIS?
Solution:
Containers:
Container is an object of SQL Server Integration
Services which help in formatting the tasks of a package in a boxed structure
and they are also used to repeated executions called as for loop containers. Also
Container can include another container
Use cases of containers in development of packages
are as follows:
·
Looping for files in different directories based on enumeration.
·
Running list of stored procedures in sequence or
logically expression.
·
Can be used in sending a different e-mail message n
times, one time for every day of the week.
·
a package can group tasks that delete and add rows in
a database table, and then commit or roll back all the tasks when one fails.
Below is the list of Containers and the
Container
|
Description
|
Runs a control flow repeatedly by using an enumerator.
|
|
Runs
a control flow repeatedly by testing a condition.
|
|
Sets the order of tasks inside a package.
|
|
Provides
services to a single task.
|
Example:
In the example, I would like to show we can use for loop container
to execute a sql statement for n number of times.
Step 0: Create a variable I and use the data type integer.
Step 1: From the tool box drag a for loop container and set
the initial value for the variable and conditions to evaluate and how to increment
in each iteration.
Step 2: Drag and drop an execute sql task inside the
container and use a statement or procedure that you wish to execute for n
number of times.
SQL
Statement used:
INSERT INTO dbo.TableA([Col1]
,[Col2]
,[Col3]
,[col4])
SELECT 'Zone'+cast(max(RIGHT(col1,1))+1 as varchar(2)),max(col2)+1,max(col3)+1,'for loop' from dbo.tableA
After setting up step 1 and step 2 you should have a container like below:
Step 3: Execute the container or task.
Output:
Col1
|
Col2
|
Col3
|
col4
|
Zone5
|
4
|
1445
|
test
|
Zone6
|
5
|
1446
|
for loop
|
Zone7
|
6
|
1447
|
for loop
|
Zone8
|
7
|
1448
|
for loop
|
Zone9
|
8
|
1449
|
for loop
|
Zone10
|
9
|
1450
|
for loop
|
Zone10
|
10
|
1451
|
for loop
|
Zone10
|
11
|
1452
|
for loop
|
Zone10
|
12
|
1453
|
for loop
|
Zone10
|
13
|
1454
|
for loop
|
Zone10
|
14
|
1455
|
for loop
|
Zone10
|
15
|
1456
|
for loop
|
As you can see the tables has been populated ten times with
the max values of the last run.
0 comments:
Post a Comment