MSBI # 15 – SSIS # 8 – Control Flow Tasks # 3- Details of Control Flow Task {Part – III}

From my last continuing post

1.First one is on Basics of Control flow task MSBI # 13 – SSIS # 6 – Control Flow Tasks #1- What is Control Flow Task ? {Part – I} « (B)usiness (I)ntelligence Mentalist

2.Second one is on Basics Details of first five category of control floe task

MSBI # 14 – SSIS # 7 – Control Flow Tasks # 2- Details of Control Flow Task {Part – II} « (B)usiness (I)ntelligence Mentalist

In this post we will have details of following Control Flow Task and remember this is just introduction to task category

1. Analysis Services Tasks
2. File and Network Tasks
3. Instrumentation Tasks
4. Maintenance Plan Tasks
5. Custom Tasks

Analysis Services Tasks

• The Data Mining task runs an Analysis Services data mining query and saves the results to a table.
• One of the primary functions of SSIS is to support the loading of data to your data models.
• In addition to the ETL components of SSIS, there are also components allowing you to manipulate your cubes and dimensions, process objects within analysis service and finally run prediction queries.
• The Analysis Services Processing task will launch processing on Analysis Services dimensions and databases.
• Use the Analysis Services DDL task to create new Analysis Services partitions, or perform any data definition language operation.
• There are Data Mining and Analysis Services Data Flow transforms, as well as these control flow tasks.
• Use the Analysis Services control flow tasks to fully or incrementally update your databases and models.

Task Under Analysis Services Tasks are as follows

1. Analysis Services Execute DDL
2. Analysis Services Processing
3. Data Mining Query

Analysis Services Tasks are as follows

 

File and Network Tasks

• The file tasks allow you to perform disk operations of directories and files. The network task allow for ftp, message queues, smtp and web service calls. Finally the XML task allows you to work with XML data.
• The XML task retrieves XML documents and applies XML operations to them.
• Use the XML task to validate an XML document against its XSD schema, or to compare or merge two XML documents.
• Use the Web Services task to make calls to a web service.

Task Under File and Network Tasks are as follows

1. File System

2. FTP

3. Message Queue
4. Send Mail
5. Web Service
6. XML

File and Network Tasks are as follows

 

Instrumentation Tasks

• Two components allowing you to interact with the Windows Management Instrumentation.
• The WMI Data Reader task runs queries using the Windows Management Instrumentation (WMI) Query Language that returns information from WMI about a computer system.
• The WMI Event Watcher task watches for a Windows Management Instrumentation (WMI) event using a Management Instrumentation Query Language (WQL) event query to specify events of interest.

Task Under Instrumentation Tasks are as follows

1. WMI Data Reader
2. WMI Event Watcher

Instrumentation Tasks are as follows

 

Maintenance Plan Tasks

• Although not part of a ETL process, these components allow you to perform day-to-day administrative tasks on your SQL database.
• The Back Up Database task performs different types of SQL Server database backups.
• The Execute SQL Server Agent Job task runs SQL Server Agent jobs. SQL Server Agent jobs automate tasks that you perform repeatedly.
• This task is similar to the Execute SQL task. However, the Execute T-SQL Statement task supports only the Transact-SQL version of the SQL language and you cannot use this task to run statements on servers that use other dialects of the SQL language.
• The History Cleanup task deletes entries in the following history tables in the SQL Server msdb database
• The Rebuild Index task rebuilds indexes in SQL Server database tables and views.
• The Shrink Database task reduces the size of SQL Server database data and log files.

Task Under Maintenance Plan Tasks are as follows

1. Back Up Database
2. Check Database Integrity
3. Execute SQL Server Agent Job

4. Execute T-SQL Statement

5. History Cleanup

6. Notify Operator

7. Rebuild Index
8. Reorganize Index
9. Shrink Database
10. Update Statistics

Maintenance Plan Tasks are as follows

Custom Tasks

Additionally, you can write custom tasks using a programming language that supports COM, such as Visual Basic, or a .NET programming language, such as C#.

If you want to access your custom task in the SSIS Designer, you can create and register a user interface for the task. For more information.

To create a custom task, you have to create a class that inherits from the Task base class, apply the DtsTaskAttribute attribute to your new class, and override the important methods and properties of the base class, including the Execute method.

File Properties Task -Reads the properties of any file and stores property in variable. Many companies receive large data files from outside their organizations and run the risk of loading the file prematurely during transmission causing an SSIS failure. Our File Properties Task gives you the power to eliminate this problem by determining if the file is in use or transmitted before loading. The File Properties Task additionally saves drive space by identifying old files ready for archive.

We have n number of example under this category where developer community have various option to develop custom task

The best example of various custom task you will find in this link click here

Task Under Custom Tasks are as follows

1. File Properties Task
2. Surrogate Key Transform
3. Delete Batch Transform

……..And many more this list will never stop

Custom Tasks are as follows

Will have detailed level discussion on each of above task in our upcoming post

Hope this helps !!