Post reply

How do I update an Oracle table using SSIS from a flat file source

  • January 26, 2011 at 7:07 am

    #244194

    Hello,

    I got SSIS 64 bits working to connect to a 32 bits database and now I'm toying with this.

    I'm a vivid EvE Online player and each year I attemp to make something that allows me to track

    my mining supplies.

    This year I made a database for it in oracle and I'm using SSIS to toy with it.

    Now i got a table called OreQuantity, comprising off OreID and Quantity.

    OreID refers to ID in the table Ore, while quantity is the supply of the ore I currently have.

    I now want to read in a flat file with the following information OreID;OreName;Quantity;A

    I would like to know how I can update the table each day instead of having to clear it out and refil it.

  • January 26, 2011 at 7:46 am

    #1278476

    Nevermind found the solution

  • January 26, 2011 at 8:56 am

    #1278534

    Resender (1/26/2011)

    Nevermind found the solution

    Good for you! would you mind in sharing? other people may benefit from it 🙂

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  • February 1, 2011 at 1:09 am

    #1280787

    Well basically I'm using a dataflow task with a flat file source and a recordset destination.

    Easy right, the same principles that apply to do this for sql server apply.

    My flow starts with an flat file source and an ole db source (The ole db i'm using to collect the ID that are in the database), then I do a merge join of the 2 based on the OreName, so i get a record comprising of my DB ID, the orename and the quantity.

    Then I do a lookup to see if there are new records (or if theirs new ores being mined), the new records (no match found on the ID) are being inserted with an ole db destination. The ones that are found go to a second lookup to see if the quantity has changed. If so these are passed through.

    Now comes the part that was giving problems (both in Oracle and in the SQL server version I made).

    The destination table consists off ID a number and Quantity allso a number.

    When I converted my fields from the flat file into numbers and then pass then to a for each loop task this failed, so I had to convert them in the dataflow to string of maximum length.

    The package variables for the fields allso had to be off the string type, after the dataflow the record set is run through with a for each loop container.

    In the the for each loop a single execute sql task exist, this task sql basically says to update the table where the OreID is the one in the recordset, it is at the point of the execute sql task that we set the variables to numeric.

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

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