MSBI Tutor

Bookmarks: Clear all bookmarks. CTRL-SHIFT-F2 Bookmarks: Insert or remove a bookmark (toggle). CTRL+F2 Bookmarks: Move to next bookmark. F2 Bookmarks: Move to previous bookmark. SHIFT+F2 Cancel a query. ALT+BREAK Connections: Connect. CTRL+O Connections: Disconnect. CTRL+F4 Connections: Disconnect and close child window. CTRL+F4 Database objects information. ALT+F1 Editing: Clear the active Editor pane. CTRL+SHIFT+ DEL Editing: Comment out code. CTRL+SHIFT+C Editing: Copy. You can also use CTRL+INSERT. CTRL+C Editing: Cut. You can also use SHIFT+DEL. CTRL+X Editing: Decrease indent. SHIFT+TAB Editing: Delete through the end of a line in the Editor pane. CTRL+DEL Editing: Go to a line number. CTRL+G Editing: Increase indent. TAB Editing: Make selection ...

Look Up Fuzzy Look Up Fuzzy Grouping Exact matching Partial matching Both Exact and Partial matching It requires reference and look up data set It requires reference and lookup data set It requires only one data set

Union Union All used to combine ( set operation ) two or more query results Used to combine (set operation) two or more query results UNION will eliminate duplicate rows UNION ALL will display all rows.

   Merge Merge Join The data from 2 input paths are merged into one  The data from 2 inputs are merged based on some common key. Works as UNION ALL   JOIN (LEFT, RIGHT OR FULL) Supports 2 Datasets Supports 1 Datasets Metadata for all columns needs to be same  Key columns metadata needs to be same. Data must be sorted. Data must be sorted. Does not support error handling Does not support error handling

Many situations where we need to split data based on some conditions, like we mostly do in switch case statements or if else conditions in other programming languages and for doing the same we have Conditional Split task in SSIS. Whenever you need to split the data coming from a source into many output paths based on some condition then CONDITIONAL SPLIT is the best bet. Step 1: Click Windows button and go to SQL Server 2008 R2 and run as an administrator. It shows a bellow window. Step 2: Go to a views and create a new project (Short cut of New Project is Ctrl + Shift + N). Step 3: Give a project name (e.g. IS) and click OK button and then shows a bellow window. Step 4: To drag and drop the Data Flow Task Step 5:  To edit the Data Flow Task then it open a Data Flow Step 6: To dag and drop the OLE DB source and edit it. Step 7: To take OLE DB connection and select the table from given dat...

The Aggregate transformation is used to perform aggregate operations/functions on groups in a data set. The aggregate functions available are Count, Count Distinct, Sum, Average, Minimum and Maximum. The Aggregate transformation has one input and one or more outputs. It does not support an error output. Aggregate Function Details AVERAGE Give the Average value of the Columns or Attributes value GROUP BY Derived the data set into Group SUM Give the SUM value of the Columns or Attributes value COUNT Give the number of item in Groups COUNT DISTINCT Give the Unique NON NULL Item MINIMUM Give the Minimum Number in a Group MAXIMUM Give the Maximum Number in a Group Step 1: Click Windows button and go to SQL Server 2008 R2 and run as a administrator. It shows  a bellow window. Step 2: Go to a views and create a new project ( Short cut o...