Pages

Monday, May 11, 2015

Let me go with a scenario where
we use lookup. The functionality of lookup is verifies the data coming from
source to destination if the data is not available in destination. It will pass
the data to other transformation or destination. If the same data coming from
source it prevent the passage of data to other transformation. Configuration is
given below Drag lookup transformation from the ssis tool box and configure to
source and destinations. When you double click on lookup it will pop up
configuration window like below picture

Cache mode: Full Cache for better performance (depends on
requirement)

Connection type: OLEDB connection manager

Specify how to handle rows with no matched entries: Redirect
rows to no matched output .later

Go to connection and select which table you want look up for
existing data.

Go to columns option and check mappings in that for avoiding
null inserts

I used adventure works for explaining look up transformation

Source table is [Sales].[CreditCard] and destination I selected in test database. [CreditCard]

Step1:- open lookup transformation and specify how to move
rows.

Step2:- go connection select which table you want to look up
for existing data

Cursor is a database items to recover information from an outcome set one column at once, rather than the T-SQL orders that work on all the lines in the outcome set at one time. We utilize cursor when we have to redesign records in a database table in singleton style means push by column.Life Cycle of Cursor1.Declare Cursor
A cursor is declared by defining the SQL statement that returns a result set. DECLARE cursor_name CURSOR [LOCAL | GLOBAL] --define cursor scope [FORWARD_ONLY | SCROLL] --define cursor movements (forward/backward) [STATIC | KEYSET | DYNAMIC | FAST_FORWARD] --basic type of cursor [READ_ONLY | SCROLL_LOCKS | OPTIMISTIC] --define locks FOR select_statement --define SQL Select statement FOR UPDATE [col1,col2,...colon] --define columns that need to be updated

2.Open

A Cursor is opened and populated by executing the SQL statement defined by the cursor.

Opened locally or globally. By default it is opened locally.

OPEN [GLOBAL] cursor_name --by default it is local

3.Fetch
When cursor is opened, rows can be fetched from the cursor one by one or in a block to do data manipulation. Fetch statement provides the many options to retrieve the rows from the cursor. NEXT is the default option.FETCH [NEXT|PRIOR|FIRST|LAST|ABSOLUTE n|RELATIVE n]FROM [GLOBAL] cursor_nameINTO @Variable_name[1,2,..n]

4.Close
After data manipulation, we should close the cursor explicitly.
Close statement closed the cursor explicitly. The basic syntax to close cursor is given below:

CLOSE cursor_name --after closing it can be reopen

5.De-allocate
Finally, we need to delete the cursor definition and released all the system resources associated with the cursor.
DE allocate statement delete the cursor definition and free all the system resources associated with the cursor. The basic syntax to close cursor is given below: