So I have a database with 10-12 tables with lots of rows (in millions). I have a Individual as the main parent table and rest our child tables.
I have designed a SSIS package that uses "ZS Export XML Task"
This task first loads all the tables data into memory and then in next step it creates XML file one by one and dumps data into it.
Files can be created based on size or no. of rows. I have selected split the file by 100,000 rows each.
I have a test database also which is just few rows and creates xml files instantly.
But my main database takes forever to load and never gets to the next step.
Can anyone suggest how can I first load 100,000 rows from sql tables, create xml file and dump the data into it and then load next 100,000 rows and create next file and so on......
Or is there any other better way to perform this task?