Post reply

Need to read a Excel File, validate it and insert into oracle db

  • June 15, 2010 at 3:07 am

    #157016

    Hi,

    I'm new to SSIS and need to read a

    1) Open a Excel file,

    2) Validate each row it by checking if the record already in the oracle db via a stored procedure call. Value to check is not a primary key or unique field, and can't add a index/modify table so can't just try to insert and if it fails continue.

    3) if the stored procedure doesn't return a row, extract some data from the excel row and call an insert stored procedure.

    Can anybody give some pointers on the best way to do this. What objects I should be using in my DataFlow?

    cheers in advance

    Sunny

  • June 15, 2010 at 8:15 am

    #1180447

    For reading the Excel file you certainly need an Excel connection manager and an Excel Source component in your data flow. Make sure you surf the net a bit on how to set-up these things, as reading Excel in SSIS can be a bit tricky.

    For point 2) and 3), I would use a simple Lookup component instead of stored procedures. Much easier to configure.

    Create an OLE DB connection to your Oracle DB, set-up a lookup component and perform a lookup for each row. If the lookup finds a match, than the row already exists in Oracle.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • June 15, 2010 at 8:55 am

    #1180491

    Thanks for the tips, very helpful.

    I had already created a dataflow, & got the Excel Connection wired up to a script component, so for each row in the xls it runs the script file.

    I have managed to make the OLE DB Oracle Connection.

    I had started to use a script to do the rest of the work and I have got as far as being able to see the connection manager from the script.

    But I think I will try your approach of a Lookup based on the value of a cell in the XLS.

    I expect I need to wire it up so that if the query returns no records then I can do a OLE Destination to execute a Oracle insert stored procedure (as it has to make a number of entrys in the db).

    If before I insert the Oracle records I need to do some sort of processing eg work out a difference based on buying and selling price in the XLS, or formatting some data. How would I go about that?

    If I wanted to read the configuration of the Oracle db from a file. eg. app.config / web.config so that when I change dbs from dev -> UAT -> production I don't have to recompile, is that possible?

    If you wanted to use a stored proc instead of a lookup how you you be able to determine from the output if any records were returned on not.

    regards

    Sunny

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply