SSIS

 Parallel execution in SSIS Parallel execution in SSIS improves performance on computers that have multiple physical or logical processors.  To support parallel execution of different tasks in a package, SSIS uses two properties: MaxConcurrentExecutables and EngineThreads.  If you are like me, you probably did not even know about these two properties, and therefore were unaware of the opportunity to make your SSIS packages execute faster.  A description of each property: The MaxConcurrentExecutables property is a property of the package.  This property defines how many tasks can run simultaneously by specifying the maximum number of executables that can execute in parallel per package.  The default value is -1, which equates to the number of physical or logical processors plus 2. The EngineThreads property is a property of each Data Flow task.  This property defines how many threads the data flow engine can create and run in parallel.  The EngineT...

    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...