SSIS Data Transfer with Integration services

Question

I have one excel sheet contains the userid, deptcode   ..like

User id	Dept Code
101	9900
102	7777
101	55555
102	77777

 

And one Table in Oracle

User id	Dept Code	Salary	Tax
101	9900
102	7777	8999999$	777
101	55555	7777777$	666
102	77777	6666666$	666

 ......

,,,,... thik that there is more rows .////

Now i like to append salary to the given sheet, only those user id are matching in both table and excel sheet ..

These are not a real data,  actuall table contains more then 10 lacs rows, I am seeking to know that how can do these kind of look up operation, record by record from the actual data source if suppose i consider the excel sheet as data source within data transfer logic.

More often, i don;t have a chance to make DB link between these excel and Oracle table? I like to read record by record from the excel sheet and do some lookup operation for this purpose what kind of control flows should use, pls provide me any quick some links ... 

Answer 1

This should be pretty straight forward to do with SSIS. My only concern would be trying to "update" the fields into the Excel spreadsheet. I've never tried it but I suspect there would be some locking between reading and writing to the same spreadsheet.

 

You will need to create 2 connection managers. An excel connection manager pointing to your spreadsheet and an OLEDB connection manager pointing to your Oracle DB. Then:

1. Add a Data Flow Task (DFT) and go to the Data Flow tab (or double click the DFT in the Control Flow tab)
2. Add 2 data source componenets, 1 for the spreadsheet and one for your Oracle DB and point them at the relevant connection manager
3. Edit them if you need to define the Query for retrieval. If your Oracle DB has over a million rows (I assume 1 "lac" =100,000) then you want to minimise the amount of information retrieved, so only bring back the columns you need and filter the rows that don't have Salary and Tax information
4. Add a Merge join transformation and define it as a left join. Things to note about this component are that:
   1. It's case sensitive
   2. It requires it's input to be sorted, so you may need to either add a sort transformation to each flow or do your sorting in the queries and then go into the advanced editor to define sorting properties of the columns
5. The output of the left join can then be directed to wherever you want. Initially I would add another excel connection manager and output the data into a separate spreadsheet to confirm that the output is what you want. After that you try to modify the logic to update the data back into the original spreadhseey if you want.

Hope this helps.

Answer 2

Thanks lot, i got it .. 

finally small dout ? what is the purpose of "Lookup" control utlity ?

Answer 3

The Lookup transformation will lookup a value or values in a table based on a lookup value from another table. E.g. when loading a fact table you will want to lookup the product dimension key based on the product code.

 

It's very similar to a merge join (actually more of a left join) except it doesn't require the inputs to be sorted and does not support "inner join" behaviour. It's also configurable so that the lookup list either can be cached in memory or a separate lookup query is executed for every row. Also, by default, a failed lookup will fail the process, but you can change this to ignore failure in which case the looked up columns will be set to NULL (much like a left join) or to redirect the row (usually used to either default the value to 0 or some "unknown" code or to write the row to an error table).