SSIS Package Control Flow Components

The control flow in the Integration Services package is constructed by using different types of control flow elements: the containers that provide structure in packages and services to tasks, tasks that provide functionality in packages, and precedence constraints that connect containers and tasks into a control flow. Below is the description of the heavily used component in control flow task;

1.) Sequence Container:  This container can be used when business wants to create logical group of control flow tasks.

Example: Consider a scenario when based on some condition we want to perform set of operation. In that case 
we can combine task corresponding to conditional result and accommodate tasks in various container where we can 
flow the control of the SSIS package.

2.) For Loop Container: This component is used when business wants to execute set of tasks multiple time based on 
some conditions.Example: Consider a scenario when we want to perform data transfer from source to destination
 for 5 files stored in the database. With condition set as the 5 iteration, we can run the control flow tasks 5 times. 

3.) For each Loop Container: This component is used when business wants to execute set of tasks based on
 some dataset that is mostly dynamic in nature. This dataset can be accommodating more conditional information
 compare to for loop container. 

Example: Consider a scenario when we have files stored in some folder and for each file we want to fetch data and transfer file data to destination database.

4.) Analysis Services Execute DDL Task: This component can be used when business wants to run the OLAP query on
 SSAS cubes.Example: Consider a scenario when we want to create replica of the cube. Using this task we can
 fire OLAP query that can create replica of SSAS cube.

5.) Analysis Services Processing Task:  This component can be used to process the SSAS cube full or partial mode.

Example: Consider a scenario when SSIS job is populating data into data warehouse that contains fact and
 dimension table and we want to process the cube after pushing data into dimension and fact tables.

6.) Bulk Insert Task: This component can load data into a table by using the BULK INSERT SQL command.

Example: Consider a scenario when we have large flat files in some folder and we want to transfer data
 from flat files to SQL server. If we have flat file size large and we want to transfer data using traditional 
data flow task, it might affect performance of the package. Using bulk insert task we can push data
 into destination with performance improvement but here we cannot transform the data in between as 
we can do in data flow task.

7.) Data Flow Task:  This component should be used when business wants to perform ETL operation 
i.e. Extract data from source, Apply Transformation to data and Load data into destination database.

8.) Execute Package Task: This component can be used when business wants to execute SSIS packages
 from some other SSIS packages.Example: Consider a scenario when we have data warehouse and we want to
 execute dimension packages and fact packages from a master package that control execution hierarchy.

9.) Execute Process Task: This component can be used when business wants to runs an application or
 batch file as part of a SQL Server Integration Services package workflow.Example: Business can use the 
Execute Process task to expand a compressed text file. Then the package can use the text file as a data 
source for the data flow in the package.

10.) Execute SQL Task: This component can be used when business wants to execute SQL statement of SQL 
objects like function, stored procedure on a particular DBMS.

11.) File System Task: This component can be used to perform file operation such as to crate directory and files, copy 
or delete directory or files, to move files or directory, rename files, set attributes etc.

12.) FTP Task: This component can be used when business want to download and uploads data files and manages 
directories on servers.Example: a package can download data files from a remote server or an Internet location 
as part of an Integration Services package workflow. You can use the FTP task for the following purposes:

·         Copying directories and data files from one directory to another, before or after moving data, 
and applying transformations to the data.

·         Logging in to a source FTP location and copying files or packages to a destination directory.

·         Downloading files from an FTP location and applying transformations to column data before loading
 the data into a database.

13.) Message Queue Task:  The Message Queue task allows you to use Message Queuing (also known as MSMQ
) to send and receive messages between SQL Server Integration Services packages, or to send messages to an
 application queue that is processed by a custom application. These messages can take the form of simple text, files
, or variables and their values.By using the Message Queue task, you can coordinate operations throughout your enterprise. Messages can be queued and delivered later if the destination is unavailable or busy.

Example: The output from a restaurant cash register can be sent in a data file message to the corporate payroll system, where data about each waiter's tips is extracted.

14.) Script Task: This component can be used when business require code to perform functions that are not
 available in the built-in tasks and transformations that SQL Server Integration Services provides. This code can
 be written in C# or VB.NET.
Example: A script can use Active Directory Service Interfaces (ADSI) to access and extract user names from
 Active Directory.

15.) Send Mail Task: This component can be used when business want to send email notification. Business can 
configure subject, body text along with details such as CC, BCC, from, to, attachments etc.

Example: Send mail task can be used to send notifications that indicate execution of SSIS package and end of failure of SSIS package.

16.) Transfer Database task: This component can be used when business wants to transfers a SQL Server database
 between two instances of SQL Server only. The Transfer Database task supports SQL Server 2000 and SQL 
Server. It can transfer a database between instances of SQL Server 2000, instances of SQL Server, and from an
 instance of SQL Server 2000 to an instance of SQL Server.

17.) Transfer Error Messages Task: This component can be used when business wants to transfers one or more
 SQL Server user-defined error messages between instances of SQL Server. User-defined messages are messages 
with an identifier that is equal to or greater than 50000. Messages with an identifier less than 50000 are 
system error messages, and cannot be transferred by using the Transfer Error Messages task. The Transfer Error
 Messages task supports a source and destination that is SQL Server 2000 or SQL Server. There are no restrictions 
on which version to use as a source or destination.

18.) Transfer Jobs Task: This component can be used when business wants to transfers one or more SQL
 Server Agent jobs between instances of SQL Server. There are no restrictions on which of the two versions to use as a
 source or destination.

19.) Transfer SQL Server Objects Task: This component can be used when business wants to transfers one or
 more types of objects in a SQL Server database between instances of SQL Server. For example, the task 
can copy tables and stored procedures. The Transfer SQL Server Objects task supports a source and destination
 that is SQL Server 2000 or SQL Server. There are no restrictions on which version to use as a source or destination.

20.) Web Service Task: This component can be used when business wants to execute a Web service method 
and utilize its result. You can use the Web Service task for assigning value to a SSIS package variable or for writing
 value to flat/XML file that has been returned by Web service method.

Example:  Business could obtain the highest temperature of the day from a Web service method, and then
 use that value to update a variable that is used in an expression that sets a column value.

21.) Maintenance Plan Task: SQL Server Integration Services includes a set of tasks that perform database 
maintenance functions. These tasks are commonly used in database maintenance plans, but the tasks can 
also be included in SSIS packages. The maintenance tasks can be used with SQL Server 2000 and MS-SQL 
Server databases and database objects. The following table lists the maintenance tasks and its usage;

Task	Description
Back Up Database Task	Performs different types of SQL Server database backups.
Check Database Integrity Task	Checks the allocation and structural integrity of database objects and indexes.
Execute SQL Server Agent Job Task	Runs SQL Server Agent jobs.
Execute T-SQL Statement Task	Runs Transact-SQL statements
History Cleanup Task	Deletes entries in the history tables in the SQL Server msdb database.
Maintenance Cleanup Task	Removes files related to maintenance plans, including reports created by maintenance plans and database backup files.
Notify Operator Task	Sends notification messages to SQL Server Agent operators.
Rebuild Index Task	Rebuilds indexes in SQL Server database tables and views.
Reorganize Index Task	Reorganizes indexes in SQL Server database tables and views.
Shrink Database Task	Reduces the size of SQL Server database data and log files.
Update Statistics Task	Updates information about the distribution of key values for one or more sets of statistics on the specified table or view.

HttpHandlers in C#

In this post i want to write about HttpHandlers in C#.

An overview on how web requests are handled and executed by IIS

Generally whenever we request for a resource like localhost\MyWebApp\Firstpage.aspx

1. First of all the request reaches IIS and it identifies that this particular request should be handled by the .Net framework.

2. Next the request is handled by the worker process.

3. Next the worker process identifies the appropriate class that should handle this request.

This particular class is nothing but a HttpHandler. Now let us go through this.

HttpHandler

A HttpHandler is nothing but a class that handles incoming requests. Asp.Net framework identifies the appropriate handler based on the file extension in the request url.

IHttpHandler
A HttpHandler is nothing but a class that implements IHttpHandler interface. IHttpHandler has two methods.

1	public interface IHttpHandler

2

{

3	// Methods

4	void ProcessRequest(HttpContext context);

5

6	// Properties

7	bool IsReusable { get; }

8

}

So any class implementing IHttphandler interface should define these two (ProcessRequest method and IsReusable property).

ProcessRequest should have the key logic to handle the http request. This is the method that is executed whenever a request comes.

Creating a class that implements IHttpHandler

This handler is called whenever a file ending in .nilesh is requested. A file with that extension does not need to exist.

01	using System.Web;

02	public class MyHandler : IHttpHandler

03

{

04	public MyHandler()

05

{

06

}

07	public void ProcessRequest(HttpContext context)

08

{

09	System.Diagnostics.Debugger.Break();

10	HttpRequest Request = context.Request;

11	HttpResponse Response = context.Response;

12

13

14

Response.Write("Hi This is my first httphandler.");

16

17

18

}

19	public bool IsReusable

20

{

21	get { return false; }

22

}

23

}

IsReusable is used to specify whether we want to enable pooling or not. If set to true the handler is placed in the memory and it is pooled.

In the processRequest function i am just writing the text “Hi this is my first httphandler” as a response to the request we got.

So we are done with the creation of Httphandler.

Now you might be having a question. How does IIS know about this handler. How this particular request localhost\MySample\Test.nilesh is going to be redirected to our handler by IIS.

Now let me answer these questions.

1. We are supposed to configure the web.config file

Configuring Web.config For IIS7

i. There is a tag called Add, replace that with this tag or if not there add this tag.

1	<add verb="*" path="*.nilesh" type="MyHandler" />

Here verb corresponds to the http protocol that our handler supports like ‘GET’,'POST’ etc

Path—The extension this particular handler should take care

Type—The class that handles the request. In our case MyHandler (This should be qualified name).

Mapping File extension to handlers in IIS7.0

1. Go to Command prompt and click Inetmgr.

2. create a website and host our website that contains Httphandler. I am naming my app as Test

Click on the HttpHandlerMapping in the below image

Now we can list of handler mappings in this image

Now let me click on .aspx and see to which handler that is actually pointing

Yes it is pointing to aspnet_isapi.dll. This is not a c# class but this will inturn process it and assign the aspx request to appropriate handler.

Now let us click on “Add Managed handler”

Fill the fields with appropriate values.
Requestpath—file extension to be handled.
Type—name of the class
Name—Name of the handler.

Now click on save.

Now go back to our solution and we can find that web.config is changed and few more tags were added to it

1	<system.webServer>

2	<handlers accessPolicy="Read, Execute, Script">

3	<remove name="Test" />

4	<add name="MyHandler" path="*.nilesh" verb="GET,HEAD,POST,DEBUG" type="MyHandler"resourceType="Unspecified" requireAccess="Script" preCondition="integratedMode" />

5	</handlers>

6	</system.webServer>

These  tags we added automatically when we add mappings in iis.

The complete web.config file

01	<configuration>

02	<system.web>

03	<compilation debug="true" />

04	<httpHandlers>

05	<add verb="*" path="*.nilesh" type="MyHandler" />

06	</httpHandlers>

07	</system.web>

08	<system.webServer>

09	<handlers accessPolicy="Read, Execute, Script">

10	<remove name="Test" />

11	<add name="MyHandler" path="*.nilesh" verb="GET,HEAD,POST,DEBUG" type="MyHandler"resourceType="Unspecified" requireAccess="Script" preCondition="integratedMode" />

12	</handlers>

13	</system.webServer>

14

15	</configuration>

Testing our First Httphandler

Type this url in the browser “http://localhost:6192/samplewebservice/test.nilesh” and we can see the result on the screen.

Debugging Httphandler

if we want to debug the httphandler in our local machines , we can add this line

1	System.Diagnostics.Debugger.Break();

Complete HttpHandler class code

01	using System.Web;

02	public class MyHandler : IHttpHandler

03

{

04	public MyHandler()

05

{

06

}

07	public void ProcessRequest(HttpContext context)

08

{

09	System.Diagnostics.Debugger.Break();

10	HttpRequest Request = context.Request;

11	HttpResponse Response = context.Response;

12	// This handler is called whenever a file ending

13	// in .sample is requested. A file with that extension

14	// does not need to exist.

15

16

17	Response.Write("Hello from a synchronous custom HTTP handler.");

18

19

20

}

21	public bool IsReusable

22

{

23	// To enable pooling, return true here.

24	// This keeps the handler in memory.

25	get { return false; }

26

}

27

}

In my next post i want to write about “How .asmx file request were handled”.