SSIS Interview Questions And Answers. Difference between Control Flow and Data Flow 1. Control flow consists of one or more tasks and containers that execute when the package runs. We use precedence constraints to connect the tasks and containers in a package. SSIS provides three different types of control flow elements Containers that provide structures in packages, Tasks that provide functionality, and Precedence Constraints that connect the executables, containers, and tasks into an ordered control flow. Control flow does not move data from task to task. Tasks are run in series if connected with precedence or in parallel. A Data flow consists of the sources and destinations that extract and load data, the transformations that modify and extend data, and the paths that link sources, transformations, and destinations. The Data Flow task is the executable within the SSIS package that creates, orders, and runs the data flow. Data Sources, Transformations, and Data Destinations are the three important categories in the Data Flow. Data flows move data, but there are also tasks in the control flow, as such, their success or Failure effects how your control flow operates 3. Data is moved and manipulated through transformations. Data is passed between each component in the data flow. What are the different types of Transformations you have workedAGGEGATE The Aggregate transformation applies aggregate functions to column values and copies the results to the transformation output. Besides aggregate functions, the transformation provides the GROUP BY clause, which you can use to specify groups to aggregate across. The Aggregate Transformation supports following operations Group By, Sum, Average, Count, Count Distinct, Minimum, Maximum                                                                                                                   AUDIT Adds Package and Task level Metadata such as Machine Name, Execution Instance, Package Name, Package ID, etc. CHARACTER MAP When it comes to string formatting in SSIS, Character Map transformation is very useful, used to convert data lower case, upper case. CONDITIONAL SPLIT used to split the input source data based on condition. Xtract IS PlugnPlay for SAP and SQL Server Integration Services Xtract IS is a really intelligent interface thanks to the available delta logic and it also. Code Compare is an advanced file and folder comparison tool. Its intuitive interface allows you to merge differing files and folders fast and easily And its FREE Experts Exchange Questions Upgrade SSIS from SQL 2008 to SQL 20142016, what is the risk, pre and post configurationtask. COPY COLUMN Add a copy of column to the output, we can later transform the copy keeping the original for auditing. DATA CONVERSION Converts columns data types from one to another type. It stands for Explicit Column Conversion. DATA MINING QUERY Used to perform data mining query against analysis services and manage Predictions Graphs and Controls. DERIVED COLUMN Create a new computed column from given expressions. EXPORT COLUMN Used to export Image specific column from the database to a flat file. FUZZY GROUPING Groups the rows in the dataset that contain similar values. FUZZY LOOKUP Used for Pattern Matching and Ranking based on fuzzy logic. IMPORT COLUMN Reads image specific column from database onto a flat file. LOOKUP Performs the lookup searching of a given reference object set to a data source. It is used to find exact matches only. MERGE Merges two sorted data sets of same column structure into a single output. MERGE JOIN Merges two sorted data sets into a single dataset using a join. MULTI CAST is used to createdistribute exact copies of the source dataset to one or more destination datasets. ROW COUNT Stores the resulting row count from the data flow transformation into a variable. ROW SAMPLING Captures sample data by using a row count of the total rows in dataflow specified by rows or percentage. UNION ALL Merge multiple data sets into a single dataset. PIVOT Used for Normalization of data sources to reduce analomolies by converting rows into columns UNPIVOT Used for demoralizing the data structure by converts columns into rows in case of building Data Warehouses. What are Row Transformations, Partially Blocking Transformation, Fully Blocking Transformation with examples. In Row Transformation, each value is manipulated individually. In this transformation, the buffers can be re used for other purposes like following OLEDB Data source, OLEDB Data Destinations, Other Row transformation within the package, Other partially blocking transformations within the package. Examples Copy Column, Audit, Character Map Partially Blocking Transformation These can re use the buffer space allocated for available Row transformation and get new buffer space allocated exclusively for Transformation. Example Merge, Conditional Split, Multicast, Lookup, Import, Export Column Fully Blocking Transformation It will make use of their own reserve buffer and will not share buffer space from other transformation or connection manager. Example Sort, Aggregate, Cache Transformation 4. Difference between Merge and Union. All TransformationsThe Union All transformation combines multiple inputs into one output. The transformation inputs are added to the transformation output one after the other no reordering of rows occurs. Merge Transformations combines two sorted data sets of same column structure into a single output. The rows from each dataset are inserted into the output based on values in their key columns. The Merge transformation is similar to the Union All transformations. Use the Union All transformation instead of the Merge transformation in the following situations The Source Input rows are not need to be sorted. The combined output does not need to be sorted. Multicast, Conditional Split, Bulk Insert Tasks. Multicast Transformation is used to extract output from single source and places onto multiple destinations. Conditional Split transformation is used for splitting the input data based on a specific condition. The condition is evaluated in VB Script. Multicast Transformation generates exact copies of the source data, it means each recipient will have same number of records as the source whereas the Conditional Split Transformation divides the source data based on the defined conditions and if no rows match with this defined conditions those rows are put on default output. Bulk Insert Task is used to copy the large volume of data from text file to sql server destination. Explain Audit Transformation It allows you to add auditing information. Auditing options that you can add to transformed data through this transformation are 1. Execution of Instance GUID ID of execution instance of the package 2. Package. ID ID of the package 3. Package. Name Name of the Package 4. Version. ID GUID version of the package 5. Execution Start. Time 6. Machine. Name 7. User. Name 8. Task. Name 9. Task. ID unique identifier type of the data flow task that contains audit transformation. Difference between Synchronous and Asynchronous Transformation. Synchronous TF process the input rows and passes them onto the data flow one row at a time. Synchronous Transformation cannot create new buffer. When the output buffer of Transformation creates a new buffer, then it is Asynchronous transformation. Output buffer or output rows are not sync with input buffer. Difference between DTS and SSIS DTS Limited number of transformations. Limited error handling. Message box in active x scripts. SSIS More number of transformations. Better error handling. Message box in. NET scripting. How would you pass a Parent variable value to Child Package We can pass the parent variable to child package by using Package configuration Parent Package Variable. Create parent variable. File. Path Parent String C RKfile.