Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Consolidate or Decompose Expand / Collapse
Author
Message
Posted Wednesday, September 12, 2012 7:04 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 2, 2014 8:40 AM
Points: 39, Visits: 296
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



Post #1358287
Posted Wednesday, September 12, 2012 9:34 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:53 PM
Points: 36,795, Visits: 31,257
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1358317
Posted Wednesday, September 12, 2012 10:08 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 2, 2014 8:40 AM
Points: 39, Visits: 296
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





Post #1358328
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse