Post reply

Consolidate or Decompose

  • September 12, 2012 at 7:04 pm

    #149455

    I have an opinion question. I have a sort of round-robin sets of pulls and updates. I'm wondering viewpoints on how others might tackle this when using SSIS.

    1. Receive an email with a CSV attached from system A.

    2. Take CSV from system A and do inserts/updates on system B

    3. Take an extract from system B and perform updates to system C.

    4. Take an extract from system C and perform updates to system B and system D.

    Nice and abstract huh? Anyway, number 1 I'm doing as a stand alone package because I might also get emails with attachments from other sources.

    I'm thinking to also have numbers 2,3, and 4 as individual packasge because that keeps them as logically separate packages that could be tweaked independently and that could even run on widely different schedules.

    I may not be giving enough information but does anyone have any thoughts?

    Thanks

  • September 12, 2012 at 9:34 pm

    #1537001

    corder (9/12/2012)

    ... does anyone have any thoughts?

    Yes. Why are you using SSIS for any of this other than extracting the file from the email? Just do a bulk insert into a staging table, validate the data, and have at the rest as you would with any other "upsert" queries.

    Also, if this is something that's supposed to happen on a regular basis, why not just have the other party setup an FTP (or SFTP) and download the files instead of messing with emails?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • September 12, 2012 at 10:08 pm

    #1537011

    I knew there'd be much missing in my description.

    Getting it via email because it is a hosted Oracle Apps system and the people getting the file can't find any way to extract information other than sending something via email. We are going to pull Oracle in house at some point and the email part will stop. One reason this is a separate process.

    System B I mentioned is a SQL Server database. There is some temp table/stored proc action going on there.

    System C is Active Directory and we can make the connections in SSIS.

    System D is Salesforce.com and we can use some other SSIS components.

    Thanks

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

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