OLEDB COMMAND TRASFORMATION

OLEDB  COMMAND TRASFORMATION

It is used to Execute SQL Command that Works  For each and every record (or) row in a dataset.

Note: OLEDB Command Works like cursor in the SQL (or)T-SQL.

Steps to configure OLEDB Command:

Open Business Intelligence Development Studio     --------->

Create a new package and rename it as OLEDB Command. dtsx     --------->

In Data Flow drag and drop OLEDB source       --------->

Double click on OLEDB Source to configure it     --------->

Provide connection manager if exists and select SQL Command option from     --------->

Data access mode drop down list.     --------->

Provide the following SQL Command text Select * from Human Resources. Employee     --------->

Where Employee ID = 1      --------->

Select columns and click ok     --------->

Drag and Drop OLEDB Command and make a connection from  OLEDB source to OLEDB Command     --------->

Double click on OLEDB Command to configure it.     --------->

In Connection Manager Tab Select the Connection manager

In components property tab, set

SQL Command - SQL Command ----- update Human Resources. Employee,

Set Martial Status =’D’ Where employee ID = 1     --------->

Click Refresh and click ok     --------->

Execute package

Desired to gain proficiency on MSBI? Explore the blog post on MSBI Training to become a pro in MSBI.

FUZZY Look up Transformation :

I t is used to compare the data from source to reference dataset by usingFuzzy matching [closely matching]

Note:

IT can be spelled customers in customer’s dimensions table.

Fuzzy Look up uses To leant Index (ETI) to find matching rows in the reference table.

Each record in the reference table is broken up into tokens or words. If you take the

Name as an Example from the customer Dimension Table and if your reference dataset

Contains Rama  Rao, the ETI will Contain Rama Rao. By using Token delimiters.

Steps to Configure Fuzzy Look Up

Open Business Intelligence Development Studio     --------->

Create a new package and rename it as Fuzzy Look Up. dtsx     --------->

In Control Flow drag and drop Data FLOW Task      --------->

In Data Flow drag and drop OLEDB source        --------->

Double click on OLEDB Source to configure it     --------->

Provide connection manager if exists     --------->

Select Production.  Product category table from the drop down list       --------->

Select Columns     --------->

Click ok     --------->

Drag and Drop Fuzzy Look Up transformation  and make a connection from Source to Fuzzy Look Up      --------->

Double Click on fuzzy look up to Configure it.     --------->

In Reference Tab,

Connection - Provide destination connection Manager

Reference Table - Select [Production].[Product Sub category]     --------->

In Columns Tab,

Make a Join or Mapping between Product Category Id from input

Column to output column     --------->

Click ok     --------->

Drag and Drop OLEDB destination and make a connection from Look up to OLEDB destination     --------->

Provide destination Connection Manager and click new to create a new table     --------->

Rename it as Fuzzy Look up     --------->

Select Mapping      --------->

Click ok     --------->

Execute Package

Import Column Transformation:

It is used to load binary data (photos, documents and media etc) from the file System in to data flow. From the data flow it can then be loaded in to a table.

Steps to Configure Import Column Transformation

Open Business Intelligence Development Studio     --------->

Create a new package and rename it as Import column. dtsx     --------->

In Control flow drag and drop data flow task and it as DFT. import column.       --------->

In Data Flow drag and drop OLEDB source       --------->

Double click on OLEDB Source to configure it     --------->

Provide connection manager if exists      --------->

Select Production. Product Photo     --------->

Select Columns and Click ok     --------->

Drag and drop import column transformation and make a connection from Source to import column     --------->

Double click on import column transformation to configure it     --------->

Select input columns and check thumb nail photo file name. Input column     --------->

(The input Column must be of type DT_STR or DT_WSTR)     --------->

Select input and output Properties tab and expand input column     --------->

Output and expand output columns     --------->

Click add Column to add a new output Column and rename it as image     --------->

File and note down the ID of image file output column     --------->

Expand Import Column Input and Expand Input Columns     --------->

Select thumbnail photo file name and set,     --------->

File Data Column ID – 65

Learn more about MSBI Interview Questions in this blog post.

Script Component:   It is used to Execute Scripts on micro Soft Visual basics.Net to Perform certain Operations.

Steps to configure script component

Open Business Intelligence Development studio     --------->

Create a new package and Rename it as script component dtsx.     --------->

In control flow drag and drop Data flow task     --------->

In Data Flow drag and drop script component and select “source” radio button as script component type [Here script component is a source in the data flow and provides data to the output column]     --------->

Click ok     --------->

In inputs and outputs page Expand output o     --------->

Select output column and click add columns to add new output columns which will be carry for word it to next level     --------->

Rename the column o as SNO and column 1 as SName     --------->

Select script page and click design script     --------->

In Microsoft visual studio for application editor providing the fall code to add new rows by adding Add row () method .

Output O Buffer. Add Row ()

Out put O Buffer . SNo  = 123

Output O Buffer. SName= “Rama”

Click ok and close Vs for application Editor

Drag and drop OLEDB destination and make a connection from script component to OLEDB destination.     --------->

Double click on destination to configure it and provide destination connection manager.     --------->

Click new to create destination table and rename it as script component     --------->

Click ok     --------->

Select mapping     --------->

Click

For an Indepth knowledge on MSBI click on:

• SQL Server Analysis Services
• Report Server
• Transfer SQL server Objects Task
• Flat file source
• Slowly Changing Dimension