MSBI Tutor

S.no DTS SSIS 1 Data Transformation Services Sql Server Integration Services 2 Using Activex Script Using Scripting Language 3 No Deployment wizard Deployment wizard 4 Limited Set of Transformation available Huge of Transformations available 5 Not Supporting BI Functionality Completely supporting end to end process of BI 6 Single Tasks at a time Multi Tasks run parallel 7 It is Un managed script Managed by CLR 8 DTS can develop thru Enterprise manage SSIS can thru Business Intelligence Development Studio (BIDS, nothing but new version of VS IDE) 9 We can deploy only at local server It can be deployed using multiple server using BIDS 10 Designer contains Single Pane SSIS designer contains 4 design panes: a) Control Flow b) Data Flow

Blocking Transformation: Aggregate Fuzzy Grouping Fuzzy Lookup Row Sampling Sort Term Extraction Script Component - When configured to receive all rows. Semi Blocking Transformation: Data Mining Query Merge Merge Join Pivot Term Lookup Unpivot Union All Non-Blocking Transformation: Audit Cache Transform Character Map Conditional Split Copy Column Data Conversion Derived Column DQS Cleansing Export Column Lookup Multicast OLE DB Command Percent Sampling Row Count Script Component - Except when configured for all.

The Lookup transformation performs lookup by joining data in input columns with columns in a reference dataset. You use the lookup to access additional information in a related table that is based on values in common columns. 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 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 and edit it. Step 5:  To edit the Data Flow Task then it open a Data Flow tab. Stem 6: Drag and drop OLE DB source and select table as Person. Address. Step 7: Drag and drop look up transformation and table as Person. Address. And map the City column. Step 8: I selected Matching Outputs as Input to First Data reader destination an

The Slowly Changing Dimension transformation coordinates the updating and inserting of records in data warehouse dimension tables.   The Slowly Changing Dimension transformation provides the following functionality for managing slowly changing dimensions: ·          Matching incoming rows with rows in the lookup table to identify new and existing rows. ·          Identifying incoming rows that contain changes when changes are not permitted. ·          Identifying inferred member records that require updating. ·          Identifying incoming rows that contain historical changes that require insertion of new records and the updating of expired records. ·          Detecting incoming rows that contain changes that require the updating of existing records, including expired ones. The Slowly Changing Dimension transformation supports four types of changes: changing attribute, historical attribute, fixed attribute, and inferred member. ·          Changing attribute changes