################################# Architecture: ##########################################
The SSIS Service is a Windows service that is installed when you install the SSIS component of SQL Server 2005.The SSIS Service is turned off by default and is set to disabled. It only turns on when a package is executed for the first time.
The SSIS runtime engine and its complementary programs actually run your SSIS packages.
The runtime executables provide Containers,Tasks,Event Handlers,Precedence Constraints.
A core component of SSIS and DTS is the notion of a package. A package best parallels an executable program in Windows. Essentially, a package is a collection of tasks that execute in an orderly fashion. Precedence constraints help manage which order the tasks will execute in. A package can be saved onto a SQL Server, which in actuality is saved in the msdb database. It can also be saved as a .DTSX file, which is an XML-structured file much like .RDL files are to Reporting Services.
A task can best be described as an individual unit of work. They provide functionality to your package, in much the same way that a method does in a programming language.The following are some of the tasks available to you:ActiveX Script Task,Analysis Services Execute DDL Task,Analysis Services Processing Task,Bulk Insert Task,Data Flow Task,Data Flow Task,Data Mining Query Task,Execute DTS 2000 Package Task,Execute Package Task,Execute Process Task,Execute SQL Task,File System Task,FTP Task,Message Queue Task,Script Task,Send Mail Task,Web Service Task,WMI Data Reader Task,WMI Event Watcher Task,XML Task.
Data Source Elements:
A data source can be nearly any OLE-DB-compliant data source such as SQL Server, Oracle, DB2, or even nontraditional data sources such as Analysis Services and Outlook. The data sources can be localized to a single SSIS package or shared across multiple packages in BIDS.
Data Source Views:
This feature allows you to create a logical view of your business data. They are a collection of tables, views, stored procedures, and queries that can be shared across your project and leveraged in Analysis Services and Report Builder.
DSVs are deployed as a connection manager. Unlike connections, though, DSVs are disconnected from the source connection and are not refreshed as the source structure changes.
#################################### Precedence Constraints #############################
Precedence constraints direct the tasks to execute in a given order. They direct the workflow of your SSIS package based on given conditions. Precedence constraints have been enhanced dramatically in SQL Server 2005 Integration Services conditional branching of your workflow based on conditions.
Constraint Value:
three types of constraint values:Success(A task that's chained to another task with this constraint will execute only if the prior task completes successfully.),Completion(A task that's chained to another task with this constraint will execute if the prior task completes. Whether the prior task succeeds or fails is inconsequential.),Failure.
Conditional Expressions:
Once you create a precedence constraint, you can set the EvalOp property to any one of the following options:
Constraint: This is the default setting and specifies that only the constraint will be followed in the workflow.
Expression: This option gives you the ability to write an expression (much like VB.NET) that allows you to control the workflow based on conditions that you specify.
ExpressionAndConstraint: Specifies that both the expression and the constraint must be met before proceeding.
ExpressionOrConstraint: Specifies that either the expression or the constraint can be met before proceeding.
################################# Containers ################################################
By using containers, SSIS variables and event handlers (these will be discussed in a moment) can be defined to have the scope of the container instead of the package. There are four types of containers that can be employed in SSIS:
Task host container: The core type of container that every task implicitly belongs to by default.
Sequence container: Allows you to group tasks into logical subject areas. In BIDS, you can then collapse or expand this container for usability.
For loop container: Loops through a series of tasks for a given amount of time or until a condition is met.
For each loop container: Loops through a series of files or records in a data set and then executes the tasks in the container for each record in the collection.
############################### Variables ############################################
Variables are one of the most powerful components of the SSIS architecture. In SQL Server 7.0 and 2000 DTS, these were called global variables, but they've been drastically improved on in SSIS. Variables allow you to dynamically configure a package at runtime.
############################## Data Flow Elements ###################################
Data flow handles the transformation of data.
Sources:OLE DB Source,Excel Source,Flat File Source,Raw File Source,XML Source,Data Reader Source
Destinations:Data Mining Model Training,DataReader Destination,Dimension Processing,Excel Destination,Flat File Destination,OLE DB Destination,Partition Processing,Raw File Destination,Recordset Destination,SQL Server Destination,SQL Server Mobile Destination
Transformations:Aggregate,Audit,Character Map,Conditional Split,Copy Column,Data Conversion,Derived Column,Data Mining Query,Export Column,Fuzzy Grouping,Fuzzy Lookup,Import Column,Lookup,Merge,Merge Join,Multicast,OLE DB Command,Percentage Sampling,Pivot,Row Count,Row Sampling,Script Component,Slowly Changing Dimension,Sort,Term Extraction,Term Lookup,Union All,Unpivot
############################ Error Handling and Logging ##############################
In the data flow, you can specify in a transformation or connection what you wish to happen if an error exists in your data.You can select that the entire transformation fails and exits upon an error, or the bad rows can be redirected to a failed data flow branch. You can also choose to ignore any errors.