For Each Loop Container Task

We are covering following points in this article 

• Why For Each Loop Container Task is Use ?
• How For Each Loop Container Task is look like ?
• Features of For Each Loop Container Task
• How to use For Each Loop Container Task ?
• What is alternative For Each For each Loop Container Task ?
• Demonstration of using For Each Loop Container Task ?
• Various Properties For each For Each Loop Container Task ?
• Reference link For each For Each Loop Container Task

Now lets go through each aspects of For Each Loop Container Task :

Why For Each Loop Container Task is Use ?

• For Each Loop container is falls under container and looping tasks
• Use containers like the For Each Loop and For Loop to execute a set of tasks multiple times.
• For example, you can loop over all the tables in a database, performing a standard set of operations like updating index statistics.
• In short when we have to iteratively execute set of task we will insert all those task under For Loop Container and set the values accordingly
• The for each loop container acts as a repeating control flow in a package. Its operations are similar to work of For each keyword in any advanced programming language. We have a definite type of enumerator for each type of objects.
• Loop implementation in the For Each Loop Container is similar to the Foreach looping concept in various programming languages.

The Foreach Loop container defines a repeating control flow in a package. The loop implementation is similar to Foreach looping structure in programming languages. In a package, looping is enabled by using a Foreach enumerator. The Foreach Loop container repeats the control flow for each member of a specified enumerator.

Enumerator is nothing but an iterator where an object that enables a programmer to traverse a container

SQL Server Integration Services provides the following enumerator types:

1. Foreach File Enumerator: It enumerates files in a folder. The plus point here is it can traverse through subfolders also.
2. Foreach Item Enumerator: It enumerates items in a collection. Like enumerating rows and columns in an Excel sheet.
3. Foreach ADO Enumerator: Useful for enumerating rows in tables.
4. Foreach ADO.NET Schema Rowset Enumerator: To enumerate through schema information about a data source. For example, to get list of tables in a database.
5. Foreach From Variable Enumerator: Used to enumerate through the object contained in a variable. (if the object is enumerable)
6. Foreach NodeList Enumerator: Used to enumerate the result set of an XML Path Language (XPath) expression.
7. Foreach SMO Enumerator: It enumerates through SQL Server Management Objects (SMO) objects.

How For Each Loop Container Task is look like ?

This is Second task in tab itself !!

Drag this out in your development plane

Features of For Each Loop Container Task

The following diagram shows a Foreach Loop container that has a File System task. The Foreach loop uses the Foreach File enumerator, and the File System task is configured to copy a file. If the folder that the enumerator specifies contains four files, the loop repeats four times and copies four files.

Where we can use a combination of variables and property expressions to update the property of the package object with the enumerator collection value. First you map the collection value to a user-defined variable, and then you implement a property expression on the property that uses the variable.

Enumerator Types

How to use For Loop Container Task ?

We can configure For Each Loop Container in following three ways

1. SSIS Foreach Loop Container – General
2. SSIS Foreach Loop Container – Collection
3. SSIS Foreach Loop Container – Variable Mappings

SSIS Foreach Loop Container – General

Use the General page of the Foreach Loop Editor dialog box to name and describe a Foreach Loop container that uses a specified enumerator to repeat a workflow for each member in a collection.

In this tab we can just specify name of for each loop container as we required :

SSIS Foreach Loop Container – Collection

Here we can actually select various enumeration and iterate accordingly

See the following list when we select collection tab and Click on dropdown Enumerator

Following three way to configure this tab :

1.Select Enumeration Type

2.Select Path path or variable list for main Enumeration in 7 option

Here we are getting different option for every enumeration

(I) For each File Enumeration

Select Path of the file and type/extension for required file to traverse

(II)For Each Item Enumerator

Select or defined the item as show in configuration plan

 

(III)For Each ADO Enumerator

In this we have three option as follows

(IV)Foreach ADO.NET Schema Rowset Enumerator

To enumerate through schema information about a data source. For example, to get list of tables in a database.

(V)Foreach From Variable Enumerator

Here we have choose variable or we can defined new variable too

(VI)Foreach NodeList Enumerator

Used to enumerate the result set of an XML Path Language (XPath) expression.

(VII)Foreach SMO Enumerator

It enumerates through SQL Server Management Objects (SMO) objects.

In Short Select the enumerator type from the list. This property has the options listed as follows:

1. Foreach File Enumerator: Enumerate files
2. Foreach Item Enumerator: Enumerate values in an item
3. Foreach ADO Enumerator: Enumerate tables or rows in tables
4. Foreach ADO.NET Schema Rowset Enumerator: Enumerate a schema
5. Foreach From Variable Enumerator: Enumerate the value in a variable
6. Foreach Nodelist Enumerator: Enumerate nodes in an XML document
7. Foreach SMO Enumerator: Enumerate a SMO object

SSIS Foreach Loop Container – Variable Mappings

In this last tab we are going to just map the variable as we required

As I have map Var:User:MyFirstVar in index 0

Various Properties for For Each Loop Container Task ?

Following are various configurable properties for For loop container

Things to remember For Each Loop task

This is iterative task so where ever you will find things to iterate over file of variable we

are going to use for each loop task

XML task in SSIS

XML task in SSIS

XML task in SSIS
In this article we will learn about XML task in SSIS. XML task is used to validate, modify, extract or even create files in an XML format.
Drag XML task from tool box and drop on Control flow tab as shown in Figure1
 Figure 1
Double click xml task which displays XML task editor as shown in Figure 2 which is having only two tabs. In General tab, you need to specify the connections and type of operation should be performed.
Figure 2
General tab properties changed based on the OperationType selected. The XML operation types are as follows.

• Validate: This option allows XML file schema validation against Document Type Definition (DTD) or XML Schema Definition (XSD) binding control documents. XML task with this option makes sure that XML file is in required format.
• XSLT: The Extensible Stylesheet Language Transformations (XSLT) are a subset of the XML language that enables transformation of XML data.
• XPATH: This option uses the XML Path Language and allows the extraction of sections or specific nodes from the structure of the XML document. This option will be used to extract data from XML nodes.
• Merge: This option allows for the merging of two XML documents with the same structure. this option will be used to combine the results of two extracts from different systems into one document.
• Diff: This option allows us to compare two XML documents to produce a third document called an XML Diffgram that contains the differences between them.
• Patch: This option applies the results of a Diff operation to an XML document to create a new XML document.

You can specify the XML file source either through direct input or variable or file connection. This is common for all options.
Secondoperation tab specifies source type and source that contains second XML document to complete the selected operation.
Lets us understand XML task with an example which uses validates an XML using validate option
Steps to follow:

1. Create one dummy xml file as mentioned below. You can create using any available XML editor.



    col1
    col2
    col3
    col4
    col5
    col6
2. Generate XSD/XML schema either through any generate tool or online. I have created below XSD document using online tool saved as text_xml.xsd
http://www.w3.org/2001/XMLSchema"

>
 
   
     
       
         
           
             
             
             
             
             
             
           
         
       
     
   
 

Create a package and add XML task on the control flow design editor.

Open configuration configuration editor and select operation type as validate

Select a source type as direct input and Write the XML. You can specify even through file connection

In OperationResult property in Output tab specify how to capture the result of validation. Add output result to file test.txt and set OverWriteDestination property to true to allows result to be over written.

In SecondOperand tab you need to create file connection with XSD document which is created in Step2

Set validation type as XSD file to validate XML file.

XML task editor should be shown as displayed below after configuration completed.

If you execute the package, Task will be executed successfully and turned into Green color and writes true into text.txt file which indicates XML file contains the correct schema as defined by XSD file. If you want you can test giving wrong XML file which writes false into file

File System Task in SSIS

File System Task in SSIS will do File Operations – Copy File, Move File, Rename File and Delete File. Several times we need to do some file operations like copy, move, delete or rename file while performing the integration tasks. In SSIS we can accomplish these by File System Task control. The File System Task control is used for performing operations on files and directories such as move, create, delete, rename file and directories. We will see each and every operation separately.

Create SSIS Project

First we need to create an SSIS Project before starting with SSIS File Operations, you can create a SSIS Project by looking in to this Let’s say we created an SSIS project as FileOperations.

Copy File:

We can copy file from one place to another with the help of SSIS. We will look into the whole process of copy file from one place to another in this section, for this you need a New Package in your project which you can create easily by looking into. Now we have a SSIS project named FIleOperations, it is the time to create a package in our SSIS project. Add a new package and name it as CopyFile.

Adding File System Task in Your Package

Now we have a package and it is time to add File System Task to your package, to add File System Task control to the package go to control flow times in toolbox and double click on File System Tasks or drag and drop it in your package(See the image below).

 

Configuring File System Task

Now we have File System Task control added to your package and we can configure it. To configure right click on the control and choose edit(See the image below).

After that you will be navigated to a new window named File System Task Editor in which you have to configure the File System Task(See the image below).

The options on File system task editor are as below.

1. IsDestinationPathVariable : Destination path is stored as variable or not.
2. DestinationConnection : File Connection or vairable.
3. OverwriteDestination : Overwrite if file exists with same name. True/False
4. Name : name you want to give.
5. Description : Little description
6. Operation : Choose copy file.
7. IsSourcePathVariable : Source path is stored as variable or not.
8. SourceConnection : File Connetion or variable.

Here we have to choose operation as Copy File choose OverwriteDestination as True and IsDestinationPathVariable and IsSourcePathVariable as False. Now we have to configure Source and destination connections.

Configuring Source and Destination Connections

Now we have to configure source and destination connections.

Source Connection : In File System Task Editor navigate to SourceConnection Tab and choose New Connection(See the image below).

 

Now you will be navigated to new window named File Connection Manager Editor where you have to configure your source connection. ChooseExisting FIle as Usage Type and provide fully qualified path for file which you want to copy to other location(See the image below).

Destination Connection : Once you done with your SourceConnection you have to configure Destination Connection. Go to DestinationConnection tab and choose New Connection(See the image below).

Now you will be navigated to new window named File Connection Manager Editor where you have to configure your destination connection(See the image below).

 

Here we also have to provide Usage Type & Folder as in Usage Type we choose Existing Folder so we have to provide the destination folder where we want to have a copy of file.

Now you done with both Source and Destination connection and your final File System Task Editor looks like this(See the image below).

Click OK  and you are done with your Copy File Task. Save it and execute package as we did in previous post and this will copy specific file from one place to another.

Rename File:

You done with Copy File, we will see the Rename File operation next. This operation will rename any file to the another name you want, for this we need to create a package named Rename. Once you have package added to your project open it and drag and drop File System Task control to your package as we did earlier in this post. Now we will configure that file system task to rename files. For this you have to right click on the package and choose Edit as we did earlier in this post. This will open up File System Task Editor, we have to configure that editor to rename files(See the image below).

We will choose Operation as Rename File(See the image below).

Now we have to configure Source and Destination Connection for Rename File System Task.

Configure Source and Destination Connection :

Source Connection : In File System Task Editor navigate to SourceConnection Tab and choose New Connection(See the image below).

Now you will be navigated to a new window named File Connection Manager Editor and here you have to configure your SourceConnection chooseExisting File as Usage Type and provide Fully Qualified Path in File(See the image below).

click Ok and your are done with your SourceConnection.

Destination Connection : Once you are done with your SourceConnection you have to configure Destination Connection where you want your renamed file, for this navigate to DestinationConnection Tab and choose New Connection(See the image below).

Now you will be navigated to new window named File Connection Manager Editor and here you have to configure your DestinationConnection chooseCreate File as Usage Type and provide Fully Qualified Path in File(See the image below).

Click Ok and you are done with your DestinationConnection.

Now you will be navigated back to File System Task Editor here click ok and you are done with your Rename File Task. You can run this package as shown in previous post and you will get new file with name Renamed.txt at your destination path.

Move File :

In this Move File Task we will see how to move files from one location to another location. For this we have to add a package name Move in our project and add File System Task Control to your package by Drag & Drop from ToolBox. Once you have File System Task added to your project you have to configure this to move file by right click and choose edit on File System Task as we did earlier in this post(See the image below).

Now after this we need to configure Source and Destination Connection as we did earlier in this post.

Configure Source and Destination Connection :

Source Connection : In File System Task Editor navigate to SourceConnection Tab and choose New Connection(See the image below).

Now you will be navigated to a new window named File Connection Manager Editor and here you have to configure your SourceConnection chooseExisting File as Usage Type and provide Fully Qualified Path in File(See the image below).

click Ok and your are done with your SourceConnection.

Destination Connection : Once you are done with your SourceConnection you have to configure Destination Connection where you want to move file, for this navigate to DestinationConnection Tab and choose New Connection(See the image below).

Now you will be navigated to new window named File Connection Manager Editor and here you have to configure your DestinationConnection chooseExisting Folder as Usage Type and provide Folder Path in File(See the image below).

Click Ok and you are done with your DestinationConnection.

Now you will be navigated back to File System Task Editor here click ok and you are done with your Move File Task. You can run this package as shown in previous post and you will get a new file at DestinationConnection and deleted from SourceConnection.

Delete File:

Delete File Task  is used to delete specific file from the specific location. For this we need to create a new package by name delete  in our sample project and add File System Task control to your package by Drag & Drop it from ToolBox. Once you have File System Task added to your packageRight Click on File System Task and choose Edit to configure it to delete files from a specific location. Now we get File System Task Editor window on our screen choose Delete File as operation in File System Task Editor window(See the image below).

Now we have to configure Source Connection for this from where we like to delete files.

Configure Source Connection :

Go to SourceConnection tab in File System Task Editor and choose New Connection(See the image below).

Now you will be navigated to a new window named File Connection Manager Editor and here you have to configure your SourceConnection chooseExisting File as Usage Type and provide Fully Qualified Path in File(See the image below).

Click Ok and you are done with SourceConnection and navigated back to File System Task Editor, Click Ok here and you are done with your Delete File Task run this package as shown in previous post and the targeted file will be deleted from the path specified in SourceConnection.

Expression Task in SSIS 2012

 inSSIS 2012 , we can see a new task in Control flow, named Expression Task.

Expression Task created to do assignment on package variables or parameters.

you can write any expression you want there and assign it to any variable or parameter.

for example this is sample expression :

@[User::PreviousMonth]= MONTH( DATEADD( "MM", -1, GETDATE()  )  )

This will assign previous month number to the PreviousMonth variable.

If you experienced previous version of SSIS, you remember that you can do same thing with resort of expression property of variables and set EvaluateAsExpression of that property to true.

So what is difference between Expression Task and previous method?

Actually the previous method is still working and you can use it, and Expression task didn't do any new thing. But the operation of assigning an expression result to a variable was not isolated in a TASK, the Expression Tasks comes to perform this as a TASK.

this is sample of Expression Task usage:

create a package first, add Expression task from toolbox

also add a package variable of type Int32 in package scope, and name it as PreviousMonth,

then go to expression task editor, and write this expression there:

@[User::PreviousMonth]= MONTH( DATEADD( "MM", -1, GETDATE()  )  )

then add a breakpoint to PostExecute event of expression Task,

and run the package, now when package pause at breakpoint you can see the PreviousMonth variable value in the watch window

Execute Process Task in ssis

For SSIS packages, most of the times you will get zip, rar or tar etc files and need to extract them during the extraction operation in SSIS. Let us see how we can do this directly from SSIS.

You can use the Execute Process Task in the Control Flow task. In BIDS Simply drag and drop an Execute Process task to the control flow and configure as shown below:

In the Execute Process, you need to perform three configurations:

1. Executable:  This is the path of the application you are going to use. This specific example has used Winrar.
2. Arguments: In this you need to supply the arguments to extract the zipped files. In the particular example e stands for Extract files to current directory. Then the full path name of the zipped file. –o+ is a parameter to say overwrite files if they exist.
3. Working Directory: This is the current directory for the process. In the given example test1.rar will be extracted to the directory given in the Working Directory attribute.

Example 2: execute a command line commands such as DIR, COPY, DEL, RENAME

Execute Process Task is used to execute the external application in SSIS.

If you need to execute a command line commands such as DIR, COPY, DEL, RENAME, etc. then you have to use cmd.exe command that is located in c:\windows\system32\

CMD.exe requires an additional argument /C while executing command line commands in Execute Process Task. Otherwise, It will not execute the given command.

In this example, I will show you how to create a folder using command line commands.

Step 1: Create a SSIS package

Step 2: Add Execute Process Task into the package

Step 3: Configure the task as shown below. The command we are using in this task will create a new folder in e drive. But, you can use the available drive letter in your computer.

Step 4: Execute the package

On successful package execution, you will have a new folder SSRider in the specified drive. So, it is very simple to execute an external commands or applications in SSIS using Execute Process Task and never forget to add /C option when you are using command line commands.

Note: Command Line or Command Prompt is a Windows program that emulates many of the command line abilities available in MS-DOS but it is not actually MS-DOS.

execute a package in ssis

How to execute a package from another package?

Execute Package Task. This mechanism of executing one package from another is popularly knows as Parent Child paradigm. Let me tell you, this isn’t new in 2012. There are, however, a few changes to the Execute Package Task. Let’s take a look.

I added two packages to the project and conveniently named them Parent.dtsx and Child.dtsx.

Child Package

In the child package, I added a script task and used the following script. This will display a message box and tells that it is executing.

MsgBox(“Howdy! I’m the child package”)

Parent Package

In the parent package, I added an Execute Package Task and renamed it to Run Child Package.

In the Package page of the Execute Package Task editor, there is a new property called reference type. It is set to Project Reference by default. This means you can point the execute package task to another package within the same project. I selected Child.dtsx from the drop down.

The following is the output when I execute the parent package.

In prior versions, you’ll have to choose either file system or SQL Server as the source for child package and have connection managers to all the child packages in the parent. For example, if you have one parent package calling 50 child packages, the parent needs 50 connection managers to each of those child packages. This is still supported for legacy packages – change the reference type property to External Reference.

Passing a variable from parent to child

You often pass variables from parent package to the child package; connection strings, parent package start time, parent package name (ok. May be). Again, in the previous versions, you would use a parent package configurations to do this.

In this example, I’ll pass parent package name to the child package.

Another change to the Execute Package Task is parameter binding. You can map a parameter or a variable from the parent package to a parameter in the child package.

In the below image, I created a parameter in the child package. I wrote about SSIS parameters in an earlier post.

From the parent package, open the Execute Package Task Editor. In the parameter bindings page, I mapped the child parameter to the parent’s PackageName system variable.

Then I changed the script in the child package as follows:

MsgBox(“Howdy! I’m the Child Package. I was called by ” & Dts.Variables(“$Package::ParentName”).Value.ToString)

When I execute the parent package, I see…

SSIS 2012: Better Execute Package Task

Execute Package Task had some rough edges in previous versions of SSIS. to name a few, you had to create a connection manager to be used by each task/package, and there was not a simply way to parameterize the connection managers all at once. Passing values from parent to children packages was something that did not have a good story either.

A plethora of connection managers in the old days...

What has changed in SSIS 2012?

With SSIS 2012 when you use the new project deployment model, the Execute package task is now easier to setup and configure:
For one, we just need to select the name of the package – any package within the current project – from a drop down list, as we will save quite a few clicks a we don’t need to create a configuration manager each time.

Point and click

Second, we have the ability to bind the parameters of the child package to variable or parameters in the parent package, and once again, just by selecting the appropriate values from the dropdown lists.

Nice: The execute package task 'sees' the parameters defined in the child package

Third, there are no connection managers to maintain and configure.