SSIS Grammer Series - Article 1

• Hi,
  
  I am writing here some SSIS basics and SSIS glossary. I think it may be useful for quick reference.
  
  
  
  Control Flow Items
  Various Control Flow Items:
  · Bulk Insert task: The Bulk Insert task lets you perform a fast load of data from flat files into a target table. It’s similar to using BCP to bulk copy a file into a table, and is appropriate for loading clean data. If you need to transform data that’s stored in a flat file, you should instead use a data flow task that uses the flat file as a source.
  · Execute SQL task: The Execute SQL task, as you might expect, will execute a SQL statement against a specified database. The database need not be SQL Server, but the statement must be written in the appropriate dialect of SQL. Use this task to perform database operations like creating views, tables, or even databases. A common use of this task is to query data or metadata, and to put the query results into a variable.
  · File Transfer Protocol and File System tasks: Use the File Transfer Protocol task to transfer files and sets of files. Use the File System task to perform file system operations like copying, moving, or deleting files and folders.
  · Execute Package, Execute DTS2000 Package, and Execute Process tasks: The Execute Package task executes an Integration Services package. With it you can (and should) break a complex workflow into smaller packages, and define a parent or master package to execute them. Create a separate package to populate each table in your data warehouse database. The use of parent and children packages enables the modularization and reuse of complex logic. If you have DTS2000 packages running already in your production system, you can run them with the Execute DTS2000 Package task. The Execute Process task will run any operating system process. For example, you may have a custom process that will generate the source system extract, or you may invoke a non-SQL Server relational database’s bulk loader.
  · Send Mail task: The Send Mail task sends an email message. You will almost certainly use this task in an event handler, for example to send a message to an operator about a processing failure.
  · Script and ActiveX Script tasks: These tasks are available to perform an endless array of operations that are beyond the scope of the standard tasks. The ActiveX Script task is provided for backwards compatibility to DTS2000; use the Script task for new work. The Script task uses Visual Basic .NET from the Visual Studio for Applications environment. Or, you can use any .NET language to create a custom task that will become available in the list of control flow tasks. Defining a custom task is a programming job, rather than simply scripting, but has the significant benefit of re-use.
  · Data Mining and Analysis Services Processing tasks: The Data Mining task runs an Analysis Services data mining query and saves the results to a table. 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. The use of the corresponding Data Flow transforms is discussed in the next section.
  · XML and Web Services tasks: 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.
  · Message Queue, WMI Data Reader, and WMI Event Watcher tasks: These tasks are useful for building an automated ETL system. The Message Queue task uses Microsoft Message Queue (MSMQ) to manage tasks in a distributed system. You can use the WMI tasks to coordinate with the Windows Management Interface, and automate the execution of a package or set of tasks when a specific system event has occurred.
  · ForEach Loop, For Loop, and Sequence containers: Use containers like the ForEach 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. The Sequence container groups together several tasks. Use it to define a transaction boundary around a set of tasks so they all fail or succeed together. Or, use it simply to reduce the clutter on the design surface by hiding the detailed steps within the sequence. We used a sequence container in the package, for the Sales Reason and Sales Reason Bridge tables. You can also group control flow objects, and collapse or expand those groups. There’s no task for grouping. Simply select several objects, right-click, and choose Group..
  · Data Flow task: The Data Flow task is where most ETL work is performed. The Data Flow task is discussed in the next section.