February 8, 2019 at 3:33 pm
I have approx 500 tables needed to be loaded to destination with similar table structure. Using SSIS i can able to load one table in the DFT to load. Wondering if i can add multiple tables in one DFT ? Please advise?
February 11, 2019 at 8:42 pm
Any thoughts using SSIS to load multiple tables?
February 12, 2019 at 7:23 am
Admingod - Friday, February 8, 2019 3:33 PMI have approx 500 tables needed to be loaded to destination with similar table structure. Using SSIS i can able to load one table in the DFT to load. Wondering if i can add multiple tables in one DFT ? Please advise?
After realizing that DFT is Data Flow Task (this is a forum not an SMS chat), I can say that you can load multiple tables in the same data flow task. Be aware, that they'll be loading simultaneously, so you need to plan based on your disk capabilities to avoid problems. The Import/Export wizard uses 5 tables per task, I would advise that you start the same way. Also, group tables by similar loads to keep a good flow.
I suggest that you take a look at BIML to create the load in a more efficient way. There's a stairway on this site.
February 12, 2019 at 7:51 am
Completely agree that loading 500 tables in a single Data Flow task would unlikely be a good idea. Your data is going to have some kind of relationship definitions, so it would be impossible to load a table with a foreign key before (or at the same time) as the table it is referencing; not unless you build the foreign key constraint after the ETL process. Plus the fact that, like Luis said, that's going to be a big ask of your Server's resources.
There's nothing wrong with having a few tables loading at the same time, in my view, but you still want to do it sequentially overall. You'll likely want to identify your "lookup tables" first, and then your base data data (if it was a online ordering system, then probably things like your client and product/department information maybe), and then build from there (going to your orders, and then the items ordered, etc).
You probably want to break it up in part as well, as then you can separate the transactions out. Complete one "layer", commit those transactions, and then start the next. The last thing you want is to be loading 500 tables "simultaneously" for the last row in the 500th table to fail; causing the entire thing to roll back (that would be just a huge ask of the server as the initial load would be). If you're doing it in parts, then only some of the work you've done will be rolled back and you can address the error, and start from the last step (not the beginning).
Take your time to understand the data, identify the constraints/relationships and begin at the "start", and work your way to the end in steps.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply