Warning: is_writable() [function.is-writable]: open_basedir restriction in effect. File(/f5/sqllion/public/wp-content/uploads/shareaholic/) is not within the allowed path(s): (/fs3d/sqllion/:/nfsn/apps/php53/lib/php/:/nfsn/apps/php5/lib/php/:/nfsn/apps/php/lib/php/) in /fs3d/sqllion/public/wp-content/plugins/sexybookmarks/sexy-bookmarks.php on line 335
Programming Lookup Transformation | SQL Lion
Home > SSIS, SSIS programming > Programming Lookup Transformation

Programming Lookup Transformation


Lookup Transformation is used to lookup data by joining data in input columns with columns of a reference table. The reference dataset can be a cache file, an existing table or view, a new table, or the result of an SQL statement. The lookup is used to access additional information in a related table that is based on values in common columns.

For Example:
Let us have one employee table and one transaction table. The employee table contains information about all the employees. And the transaction table keeps record of the type of transaction done by any employee.

Details about Employee_Dim table:

Column_name Type
employee_Sl_No int
Department varchar
emp_name varchar
emp_id int
emp_DOB datetime

Data:

employee_Sl_No Department emp_name emp_id emp_DOB

1

sales Arun

101

16-Mar-84

2

sales Akash

102

31-Jan-82

3

marketing Ram

103

3-Mar-86

4

marketing Rasi

104

25-Apr-86

5

manufacture viswa

106

22-Apr-82

6

finance Kishore

107

26-Aug-80

7

sales jaswant

105

22-Apr-80

Details about Transaction_T table:

Column_name Type
Emp_ID int
Transaction_ID int
Transaction_Type varchar

Data:

Emp_ID Transaction_ID Transaction_Type

101

1

Payment

102

2

Sale

103

3

Debit

104

4

Credit

105

5

Void

Our requirement is to get all the information about the employees with their transactions. Our resultant set would look like this.

Emp_ID Transaction_ID Transaction_Type employee_Sl_No Department emp_name emp_DOB

101

1

Payment

1

sales Arun

16-Mar-84

102

2

Sale

2

sales Akash

31-Jan-82

103

3

Debit

3

marketing Ram

3-Mar-86

104

4

Credit

4

marketing Rasi

25-Apr-86

105

5

Void

7

sales jaswant

22-Apr-80



The dataflow component to achieve the above logic with Lookup transformation is:

programminglookup1

Below is given how to set the joining columns in the lookup transformation editor.

programminglookup2

Note: To avoid error messages in SSIS, be sure to have a reference table containing all the matching values for lookup. Otherwise, try to redirect the unmatched rows in some other location.

Now let’s try the same in Integration Service programming. The coding can be done in C# .net or VB .net. First the package will be created (empty one). Then in the Control Flow, the package tasks (i.e. Execute SQL task for logging and data flow task for transformation), precedence constraints and package variables will be created. And inside the data flow task, the various components like data source, data destination, and lookup transformation will be created.

Note: For detail about the other components of the package, please refer to my earlier post named “SSIS Programming Basic

The creation name for Lookup transformation is “DTSTransform.Lookup.1” and the component class ID is “{0FB4AABB-C027-4440-809A-1198049BF117}“. (For SQL Server 2005)


The code below shows the above package programmatically and the code itself is self-descriptive. The code for SQL server 2005 and SQL Server 2008 are a bit different; both the approaches are given below.

Add the below References before developing the code.

Microsoft.SqlServer.Dts.Design
Microsoft.SqlServer.DTSPipelineWrap
Microsoft.SQLServer.DTSRuntimeWrap
Microsoft.SQLServer.ManagedDTS

Programming Integration Service in SQL Server 2005

Expand image C# .Net Expand
Expand image VB .Net Expand

Programming Integration Service in SQL Server 2008

Expand image C# .Net Expand
Expand image VB .Net Expand

To make your work more easy and effortless, SQL Lion team comes up with a unique tool named “SSIS Component Explorer” that will help you in retrieving the Creation Name, Component Class ID, etc about any component like Control Flow Tasks, Data Flow Transformations, Connections Managers, Log Providers, etc in SSIS package. It also provides code snippets for each component.
Download
the free version from here.

References:

MSDN Books Online
http://technet.microsoft.com/en-us/library/ms141821.aspx

Categories: SSIS, SSIS programming
  1. No comments yet.
  1. No trackbacks yet.
You must be logged in to post a comment.