Please see attached image. I'm struggling to get my head around the best way to do this. I'm building a solution that will import flat files, split the data among staging tables (we want local copy of info) then carries on inserting data into multiple tables in salesforce. The only way i can think of doing this and having integrity between tables etc. is doing row by row (saving to rows table first, then multicast to others) which obviously has a performance impact. Can someone please suggest a better method, or shall i carry on with this? Thanks!
The fastest method would be to dump the contents of flat file to a staging table as is. Then do any cleansing or business rules validation to remove spurios rows. Then finally have a procedure where you'll start inserting into your required tables data from the staging tables starting with parent table and then moving to child tables. You can use OUTPUT Clause to capture the generated autoid values (if any) from parent tables and then put it into temp table for each insertion. then in further steps (inserts to child table) you can use the temp table to fetch and insert id values to satisfy foreign key references.
So your package will reduce to a single data flow task with flat file source and oldeb destination with any transformation tasks you may want in between. Then have execute sql task to do final insert logic. In between if required ass a execute sql task to do the validations. This would be much faster as you implement logic set based inside sql procedures compared to row by row processing inside data flow.
Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs
thanks for your fast response, ok bearing in mind, once the data is in my SQL staging tables i then have to move it into Salesforce for which i have Cozyroc SSIS components to do this. Although i do have an ODBC driver to insert into Salesforce with T-SQL it will be more complicated than just using the SSIS components?
A basic example of your method would be much appreciated
- Edited by jhowe1 Tuesday, November 26, 2013 1:52 PM