SSIS

    All the dataflow components available in SSIS can be categorized as either Synchronous or Asynchronous components. Synchronous components (non-blocking) A simple explanation of Synchronous transformation is that a synchronous transformation processes incoming rows and passes them on in the data flow one row at a time. The output is synchronous with input, meaning that it occurs at the same time. Therefore, to process a given row, the transformation does not need information about other rows in the data set. The output of a synchronous component uses the same buffer as the input. Reusing the input buffer is possible because the output of a synchronous component always contains exactly the same number of records as the input. Synchronous (non-blocking) transformations always offer the highest performance. Synchronous transformations are either stream-based or row-based. Streaming transformations are calculated in memory and do not require any data from outside resources...

  DATACONVERSION:   Converts columns data types from one to another type. It stands for Explicit Column Conversion. DATAMININGQUERY:  Used to perform data mining query against analysis services and manage Predictions Graphs and Controls. DERIVEDCOLUMN:  Create a new (computed) column from given expressions. EXPORTCOLUMN:  Used to export a Image specific column from the database to a flat file. FUZZYGROUPING:  Used for data cleansing by finding rows that are likely duplicates. FUZZYLOOKUP:  Used for Pattern Matching and Ranking based on fuzzy logic. AGGREGATE:  It applies aggregate functions to Record Sets to produce new output records from aggregated values. AUDIT:  Adds Package and Task level Metadata: such as Machine Name, Execution Instance, Package Name, Package ID, etc.. CHARACTERMAP:  Performs SQL Server column level string operations such as changing data from lower case to upper case. MULTICAST:  Sends a copy of supplied Dat...

Data transformation is the process of extracting required data from a data source and is the most critical SSIS step. Post extraction, the process aids in managing and transferring the data to a specific file destination. There are several rules implemented by this process for loading the extracted data to the destination target file. Based on this, the transformations are classified as: Blocked Non Blocked Semi Blocked Non-Blocking  –  No blocking Partial Blocking  –  The downstream transformations wait for certain periods, it follows start then stop and start over technique Full Blocking :  The downstream has to be waiting till the data has been released from the upstream transformation. Non- blocking transformations Audit Cache Transform Character Map Conditional Split Copy Column Data Conversion Derived Column Export Column Import Column Lookup Multicast OLE DB Command Percentage Sampling Script Component Slowly Changing Dimension Partial blocking transforma...

  Connection as its name suggests is a component to connect to any source or destination from SSIS — like a sql server or flat file or lot of other options that SSIS provides. Connection manager is a logical representation of a connection. Connection Managers are used for gathering data from various sources and sending it to a destination. It facilitates system connection by including information regarding server, data source, authentication details, database, etc.

  What are the different types of event handlers? OnPreValidate,OnPostValidate,OnProgress,OnPreExecute,OnPostExecute,OnError,OnWarning,OnInformation,OnQueryCancel,OnTaskFailed,OnVariableValueChanged,OnExecStatusChanged Q130. What are the general cases that event handlers can be helpful in? Cleanup stage tables after a bulk load completed Send an email when a specific component failed Load lookup tables after a task completed Retrieve system / resource information before starting a task. Q131. How to implement event handlers in SSIS? Create log tables (As per the requirement) on centralized logging database On BIDS / SSDT add event handler Add control flow elements. Most of the times “Execute SQL Task” Store the required information (RowCounts – Messages – Time durations – System / resource information). We can use expressions and variables to capture this information. Q132. What is container hierarchy in attaching event handlers? Container hierarchy plays a vital role in implementi...

  SSIS Project & Package Parameters Project parameters are new with SSIS 2012.  A project parameter can be shared among all of the packages in an SSIS project.  You want to use a project parameter when, at run-time, the value is the same for all packages.  Package parameters are also new with SSIS 2012.  A package parameter is exactly the same as a project parameter – except that the scope of a package parameter is the individual package it resides in.  You want to use a package parameter when, at run-time, the value is different for each package.  Note that project parameters do *not* have an expressions property to define their value.  They are intended to hold a literal value which does not change while the package executes. See in the image at the top of the page that project parameters can pass a value to variables?  Parameters can also pass values to all kinds of objects in SSIS – basically any property that allows an expression. Yo...

Control Flow    Control Flow Tab   is a separate tab in a SQL Server Integration Services package which contains various tasks, executable and containers to execute the flow of operations and tasks. All the tasks, executable and containers are connected by  Precedence Constraints  which has constraint (Success, Failure or completed)  and  expressions. In Control Flow Tab one task need to be completed to execute next task. So basically Control Flow is process oriented and handle the execution flow. ( Figure 1 - Control Flow Tab ) Data Flow Data Flow Task  is part of Control Flow Tab which handle flow of the data from source to destination by including various transformations and business logic .  Data Flow Tab is made up through Sources, Transformations and Destinations and none of it's component will wait for other's to complete.  So basically Data Flow Task is information oriented and handle the data flow. ( Figure 2 - Data Flow Tab ) R...

Data flow is the data transferring process from the source to a specified donation. It defines the way how data or information is sent from one point to other. It consists of 3 main components: Source Transformation Destination SSIS gives control to the users to perform the data transformation process from source to destination utilizing various built-in transformation components. The components provided by the SSIS helps users in data cleaning and manipulation in the pipeline. For achieving this, the user needs to attach a data flow task to the package control flow which is later executed within the package by creating, ordering, and running the data flow. It is worthy to note that each data flow task has a separate data flow engine instance.

Data Flow Task Execute Sql Task Analysis Services Processing Task Bulk Insert Task Data Profiling Task Execute Package Task Execute Process Task Expression Task File System Task FTP Task Script Task Send Mail Task Web Service Task XML Task

SSIS containers are defined as a set of logically linked tasks that allows the management of the task scope effectively. Containers allow looping through tasks set until tasks are grouped logically or until specified criteria are met. Nesting of containers is also allowed and the containers are set in the Package designer section in the Control flow tab.   The following containers are present in SSIS: Task Host Containers:  Default container where all single tasks are available and are used for background scenes in SSIS. This is not present in the toolbox of Visual Studio and is assigned to the task implicitly. This container is also used to extend event handlers and variables. Sequence Containers:  These handle the flow of package subset and helps to divide packages into smaller pieces. These are displayed in the Control Flow tab. We can drag and drop containers from the toolbox in the design pane and then add a set of tasks into the container. Following things can be ac...

  Precedence constraints are the green, red, and grey connectors in the Control Flow that link the tasks together and can be used to manage the workflow of a package and handle error conditions. Precedence Constraint Basics The main purpose of precedence constraints is to control when tasks and containers should run in relation to one another. This revolves around whether tasks succeed (green), fail (red), or just complete with either a success or a failure (grey). Precedence constraints can also be more granularly controlled through advanced properties, which are addressed in the next section. Precedence Constraint is used for enabling the developers to define a set of tasks logically and define the order of execution. These are connectors used for connecting all the tasks. A task will only execute if the condition that is set by the precedence constraint preceding the task is met.  By using these constraints, it will choose different execution paths depending on the success ...

Structures provided by Containers Functionality provided by Tasks Precedence constraints that connect the executables, containers, and tasks into an ordered control flow.

  SSIS packages are organized collections of various connections and elements related to data flow, event handlers, variables, control flow, configurations, or parameters that would be used for assembling and programmatically building graphical design tools. They can also be used for populating data from different sources which can be later used for standardizing and administrative purposes. They are created in BIDS - Business Intelligent Development Studio. Expand the SSIS Packages node under the SSMS-Samples project in Solution Explorer and you will see the following: Double click on the SSIS package SSMS-Export-to-Excel.dtsx to open the package.  Let's walk through the following aspects of the SSIS package as displayed in BIDS: Designer Connection Managers Toolbox Properties Window The designer is the large area in the middle of the window and is shown below: The following tabs are available in the designer: The Control Flow tab (shown above) contains the tasks that the SSI...