Post reply

Loading Transactions into a Data Warehouse

  • January 15, 2009 at 12:00 pm

    #128045

    Hi All,

    Would really appreciate some advice on an SSIS problem I have.

    To give an example I have extracted 5,000 transactions from yesterday's business and these need to be loaded into our DW. Some of these transactions can be reversals and have a field called "REVERSAL IND" which is set to "Y".

    In this instance I then need to locate the original transaction in the DW (which I can do via a tracer no) and update the "REVERSED IND" field to "Y" to show that it has now been reversed by this transaction I'm loading in today.

    I'd really appreciate any advice on the best way to go about doing this.

    Thanks

  • January 16, 2009 at 3:37 am

    #927926

    Hi Guys,

    Any thoughts?

    Thanks.

  • January 19, 2009 at 7:53 am

    #929236

    It looks like I can use the OLE DB Command for this type of action but if I had 2k rows going through the flow I might actually want to only update, say, 50 ? But doing this would carry out an update for all ?

    Could I use a conditional split before I use an OLE DB Command?

    Thanks,

    Gary

  • January 19, 2009 at 8:28 am

    #929254

    A conditional split would be used for this,

    You can do a look-up transformation against the target DW and check for the row, and then split out the data depending on if you want to update or insert new data

  • January 19, 2009 at 10:10 am

    #929303

    The OLE DB command will run an update for every row. So if you have 50 reversal rows that’s 50 individual updates.

    If it is permissible to add a staging table on the server that your target table is on you could use a Conditional Split to direct the REVERSAL IND Y rows to that staging table. After the data flow task completes you can have a control flow Execute SQL task that does one update for the entire REVERSAL IND Y data set.

  • January 19, 2009 at 10:21 am

    #929308

    Thanks guys.

    I think it would be ok to have a staging table on the same server and that would definately be the quickest solution. (thanks Eric!)

    Gary

Viewing 6 posts - 1 through 5 (of 5 total)

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