Category Archives: SSIS

File System Task (Day 6 of 30)

The file system task perform operations (Copy, Rename, Delete and Create) on files and folders in file system. We can also set the attributes (hidden, read-only etc.) of file or folder by using this task. File system task uses these operations on Source-File or Folder. We can configure source file connection by using File Connection Manager or using Variable that have the source path as value.

This task works for only single file or folder. Yes it will work on all files and folder inside one folder. We must configure the file system task by using File Connection Manager by setting SourceConnection and DestinationConnection properties and there are two ways to configure them: Either you will go for File Connection Manager or will go for Variables.
When we drag the file system task into control flow surface and double click to edit it, the following dialog box will open.

File System Task

 

  1. This is showing to configure Destination Connection. We have below options to configure it:
    IsDestinationPathVariable: Available values are True and False (Default). Which gives       us the freedom to either configure with file connection manager or with variable.
    - DestinationConnection: You have to create a file connection manager for it. When     IsDestinationPathVariable is true then it will convert into DestinationVariable and we     have to choose the variable which contains the path of destination folder.
    - OverwriteDestination: Available values are True and False (Default). Which overwrite    the file at destination or not.
    When it is False and we are copying or moving the same file then File System Task will fail.

2.     Operation will give us the freedom to choose operations to perform.  Below are the list           of all available operations which a File System Task can perform:

Operation Description
Copy directory Copies a folder from one location to another
Copy file Copies a file from one location to another
Create directory Creates a folder in a specified location.
Delete directory Deletes a folder in a specified location
Delete directory content Deletes all files and folders in a folder.
Delete file Deletes a file in a specified location.
Move directory Moves a folder from one location to another.
Rename file Renames a file in a specified location
Set attributes Sets attributes on files and folders. Attributes include Archive, Hidden, Normal, Read-only, and System. Normal is the lack of attributes, and it cannot be combined with other attributes. All other attributes can be used in combination.

3. This is showing to configure Source Connection. We have below options to configure it:
-  IsSourcePathVariable: Available values are True and False (Default). Which gives us the    freedom to either configure with file connection manager or with variable.
SourceConnection: You have to create a file connection manager for it. When IsSourcePathVariable is true then it will convert into SourceVariable and we have to
choose the variable which contains the path of Source folder or file.

Summary: We have already seen an example of File System task in our Day 5. We will see how to use File System Task with variables when we will be at our Day 10.

Script Task (Day 5 of 30)

Script task gives us freedom to write code (in C# or VB.Net) to give the functionality to SSIS which are not inbuilt tasks and transformations provides. By using script task we can clubbed functionalities of multiple tasks and transformations. We can use script task for following purposes:

  1. We can read files in a folder and if file size is zero bytes (empty file) then set the value of variable to zero and move this file to archive location using this variable in precedence constraint.
  2. For data cleansing: to delete incomplete rows from a file.
  3. One most widely use of script task is to send emails.

In this article we will use script task to calculate the size of a file inside a folder and move the zero byte file into Archive folder.
To demonstrate this we will configure a For Each Loop using File Enumerator, Script Task and File System Task.

Step 1.Create a new SSIS package and name it Script Task Demo.
Step 2. Create a folder and inside this folder create another one and name it Archive. Now create two text files one has some data and another have no data.
Step 3.Now create two variables on Package level scope FileName: String Type and FileSize: Integer type.
Step 4. Now drag a Foreach Loop Container on control flow and again drag Script Task and File System task into Foreach loop container.
Double click on Foreach loop containerà Go to Collection PageàBrowse your folder where you have created the files at step 2 aboveàGo to Variable Mapping and choose FileName variable from drop down and click OK.
ForeachLoop

Foreach_Variables

Step 5: Setting up Script Task. Initially when we edit Script Task we can choose either C# or Visual Basic to write code (1), once we have selected this we need to give Read Only and Read Write variables (2) and edit the script.
Note: When we click on Edit Script button then we finalized that we will write our script either C# or in Visual Basic. Script task will not allow us to change this in again.

Script Task

In our case we will choose C# as for programming and FileName variable to ReadOnly Variables and FileSize for ReadWrite Variables. To select the variables click on eclipse button and select variables from the list.

Set Variables

When we click on edit script button we will see below screen and we have to write our code in the section highlighted.

Script

 

Now import system.IO namespace and add below code (in Red Color) into the above highlighted area and close the editor.

using System.IO;
public void Main()
{
// TODO: Add your code here
string filename = Dts.Variables["FileName"].Value.ToString();
                   FileInfo filesize = new FileInfo(filename);
                   var size = filesize.Length;
                   Dts.Variables["Filesize"].Value = Convert.ToInt32(size);
Dts.TaskResult = (int)ScriptResults.Success;
}

Step 6: Setting up Precedence constraint. Double click on the green link between script task and file system task and select “Expression and Constraint” from Evaluation Operation and use the below expression in Expression text box: @[User::FileSize]==0 and click on OK.

Constraint

 

Step 7. Edit the File System Task and configure the Destination and Source connection by using below sub-steps.

  1. Use IsDestinationPathVariable False and click on Destination Connection and New Connection.
  2. Choose Existing Folder from usage type and browse the folder path (created at Step 2 above) and click on OK.
  3. Use IsSourcePathVariable True and select FileName variable from drop down.

File Connection

 

File System Task

 

Now, SSIS will look like:

Package

 

At the end you all set to execute the package. Execute it to check the result.
Summary: In this article we have checked how to work with script task, we will see ForEach Loop Container and File System task in our separate post.

FTP Task (Day 4 of 30)

In this article we will see how to work with FTP task in SSIS. FTP task is basically used to transfer (download/upload) files on servers using FTP. In SSIS we can use FTP task for following purposes:

• Send files to remote server
• Receive files from remote server
• Create Remote Directories
• Remove remote directories
• Delete remote files

When FTP task execute it will connect server by using FTP Connection Manager. FTP Connection manager configures separately and then used in FTP task. To make a FTP connection you need to configure below properties:

•   Specify a server name and server port.
•  Specify anonymous access, or provide a user name and a password for basic authentication.
•   Set the time-out, number of retries, and the amount of data to copy at a time.
•   Indicate whether the FTP connection manager uses passive or active mode.
FTP Connection Manager

FTP task can receive multiple files and delete multiple remote files; but the task can send only one file and delete only one local file if it uses a connection manager, because a File connection manager can access only one file. To access multiple local files, the FTP task must use a variable to provide the path information. For example, a variable that contains “C:\Test\*.txt” provides a path that supports deleting or sending all the files that have a .txt extension in the Test directory.

To send multiple files and access multiple local files and directories, you can also execute the FTP task multiple times by including the task in a Foreach Loop. The Foreach Loop can enumerate across files in a directory using the For Each File enumerator.

 

Use Container for tasks (Day 3 of 30)

In today’s article we will discuss about Containers in SQL Server Integration Services. Container is logical grouping of tasks which allows us to manage the scope of tasks and execute the tasks in sequential manner or loop them.
The all containers available in SSIS are:
1. Task Host Container
2. For Loop Container
3. Foreach Loop Container
4. Sequence Container
Each container has its own properties and way to configure it. Here is some more description of these tasks:

1. Task Host Container: This is not visible to IDE but any single task comes under it. It provide service to a single task.
2. For Loop Container: Runs repeatedly tasks by testing a condition, it will repeat the task until the condition or expression evaluates to False. The following diagram will repeat for loop 6 times.

For Loop For Loop_SSIS

 

 

 

 

The For Loop container uses the following elements to define the loop:

• An optional initialization expression that assigns values to the loop counters. In above diagram it should be @InitExp=0.
• An evaluation expression that contains the expression used to test whether the loop should stop or continue. In above diagram it should be @InitExp<6
• An optional iteration expression that increments or decrements the loop counter. In above diagram it should be @InitExp=@InitExp+1

3. Foreach Loop Container: Runs repeatedly tasks by using an Enumerator,it will repeat the task until the Enumerator evaluates to False. You can configure Foreach Loop by using below 7 enumerators, MSDN description:

  Enumerator   Configuration requirements
Foreach ADO Specify the ADO object source variable and the enumerator mode. The variable must be of Object data type.
Foreach ADO.NET Schema Rowset Specify the connection to a database and the schema to enumerate.
Foreach File Specify a folder and the files to enumerate, the format of the file name of the retrieved files, and whether to traverse subfolders.
Foreach From Variable Specify the variable that contains the objects to enumerate.
Foreach Item Define the items in the Foreach Item collection, including columns and column data types.
Foreach Nodelist Specify the source of the XML document and configure the XPath operation.
Foreach SMO Specify the connection to a database and the SMO objects to enumerate.

The following diagram shows a Foreach Loop container configures as Foreach file enumerator. If the folder that the enumerator specifies contains two files, the loop repeats two times and copies two files.

For Each Loop For Each Loop_SSIS1

4. Sequence Container: Sequence container works as a subset of Package. Sequence container groups similar tasks or containers all together. It can include multiple tasks and containers. The benefits of using sequence container are:
• Disabling/enabling group of tasks.
• To define scope of variables.
• Set transaction to sequence container. Example: if we are using two tasks in sequence container and one task is to delete the data and another for insert. We have to make sure that insert will not happen until delete run successfully. In this case we can set the transaction properties in such a manner that insert action roll back if delete fails.

Precedence Constraints (Day 2 of 30)

Precedence constraints add tasks to execute them in a given order. Infect precedence constraints not only link tasks together but also define the workflow of SSIS package. A constraint controls the execution of the two tasks linked to gather in such a way that execution of task 2 depends on the status of task1 and also based on the expression (business rules) used to run it. With precedence constraint and expressions, we can design the workflow of our SSIS package based on all business rules and given conditions.

To set up a precedence constraint between two tasks, we must set the constraint value. There are two ways to define the workflow:

Workflow defined by Success, Completion and Failure: Three constraint values are possible:
Success: Task will execute only, when the prior task completed successfully. This is the default option and is in green color.
Completion: Task will execute only, when the prior task completes (whether or not prior task succeeds or fails). This precedence constraint is in blue color.
Failure: Task will execute only, when the prior task fails. This precedence constraint is in red color.

Workflow defined by Expressions or by Logical AND or Logical OR: The workflow logic is just not limited to success, failure and completion. In some of the cases this is also defined by some expressions also. In this mixed case, the outcome (success, failure and completion) also depend on the any valid expression is to be either true or false. Below is the sample of Precedence Constraint Editor:

 Precedence Constraint

To set the expression double click on precedence constraint and in select anyone of them from evaluate expression:
Expression
Expression and Constraint
Expression or Constraint

Expression: The precedence constraint is evaluated based on the expression defined in the Expression text box. If the expression evaluates to true, the workflow continues and the constrained executable runs. If the expression evaluates to false, the constrained executable does not run.
Expression and Constraint: The precedence constraint is evaluated based on both the Value property and the expression. Both must evaluate to true for the constrained executable to run.
Expression or Constraint: The precedence constraint is evaluated based on either the Value property or the expression. At least one of these properties must evaluate to true for the constrained executable to run.

Logical AND or Logical OR: This will only come in picture when one task is directed to at least 2 tasks.
Logical AND: All precedence constraints that point to the constrained executable must evaluate to true in order for that executable to run. This is the default option. If it is selected, the arrow is solid.
Logical OR: Only one precedence constraint that points to the constrained executable must evaluate to true in order for that executable to run. If this option is selected, the arrow is dotted.