Here I am going to show you how we can use Lookup in SSIS to
manage our source data. Here I am following simple steps; you go through and
let me know in case you facing any issues.
Step 1: Open Visual Studio 2012 go to new project and Select
Business Intelligence Template from left panel and Integration Services from
there. You will see the output as below show pic..
Write the name of the project and chose the location.
Step 2: Go to the solution
explorer from right panel as shown in pic, there is an SSIS Package right click
and click to new SSIS package you will get the Package.dtsx
Step 3: go to the SSIS Toolbox from left panel and drag the Dataflow
Task on the page Control Flow Tab, right click on and click to edit you will go
directly to screen Dataflow Tab:
Drag OLEDB Source from Other Source from SSIS Toolbox on the
screen. Right click and click on edit.
Step 4: OLEDB Source Editor Screen popup will open, the
Connection Manager Tab will be highlight, it will create OLEDB Connection
Manager click on new to create connection Manager go to select Server Name , select
or enter a database name and click on test connection to confirm successful connection.
Click O.K. to finish.
There is a Data Access mode Drop down just below to
connection manager. By default it got selected Table or view, you can choose as
per your requirements (it’s better to use SQL Command to ignore unnecessary column
and proper data)
Enter your query in SQL Command Text, and click preview to
see the data.
Click on Column Tab below to connection Manger check whether
all column(s) are populated click O.K. to finish the step.
Step 5: go to SSIS Toolbox and choose Lookup from Common Tab
and drag it on the screen. Drag the green arrow from OLEDB Source to Look up
and right click on Lookup and click on Edit.
Go to connection tab and click new to add connection manger and
click below Radio Button use a Table or a view to select for Lookup table where
you will get the lookup value.
Click on Columns tab below to Connection and Map the column.
Click on Available input column filed Edit Mapping to map with lookup Column as
below and click ok to finish the steps.
Step 6: Click to OLEDB Destination from Other Destination
Tab from SSIS Toolbox and drag it to on screen.
Step 7: Drag green arrow from Lookup to OLEDB Destination to
connect. And you will get popup Input Output Selection.
Select in Output –Lookup Match Output
Select in Input- OLEDB Destination Input and click O.K to
finish.
Step 7: Click on OLEDB Destination and Edit to go OLEDB
Destination Editor to map the Source and destination Column. Select the OLEDB
Connection Manager (Destination DB). And select Data Access and below the name
of table or Views to import the data.
Step 8: Click on Mappings below the connection manager to
map the source and destination Column. Click on O.K and finally you have done.
Step 9: Right Click on screen and Execute Task to Start
Lookup and import data to our destination table.
I hope you will enjoy the topic.
Comments