SSIS takes ages to transfer data from one table to another.

Question

Hello,

I have an SSIS package with a series of Execute SQL Tasks and Data Flow Tasks. Everything works perfectly and very quickly except the last data flow task where SSIS takes ages (actually it never finishes). The Data Flow task contains an OLE DB source and an OLE DB destination which use the same data base as connection (both tables are in the same database). The two tables (source and destination) have the same structure and contain exactly the same data and data types. Actually they are the same tables with a different name. The only difference is that the source table does not have any PK - FK constraints. On the other hand the destination table has a PK consisting of four variables and each of these foir variables are FK to four other tables. The tables have 7 variables (four are int type, one is decimal(18,2) and the last two are decimal(15,10). The source table has about 20 million rows.  The message that i get at the SSIS output is somethign like virtual memorey is too low, allocated 1 MB to buffer etc. Unfortunately i did not copy the exact message, so currently i am running the package again to copy the full message.

ANy ideas on how to make the last data flow task and hence the whole package run quikly?

Thanks in advance,

Andreas

Answer 1

Sounds like you are trying to commit all 20 million rows at once.  On your OLEDB Destination try setting your Rows Per Batch to something reasonable like 100,000 records

---

Chuck

Answer 2

For loading 20 millions rows you should follow:

1. drop all foreign keys on the destination table (probably verification of FK takes 95% of the time)

2. load all data (try to do it using minimal logging)

3. recreate FK

But you mention that source and destination tables share the same structure, so there is a better way to do it than SSIS:

0. load source table (no FK, PK optionally, if you are loading sorted data using minimally logged load, set sort hint for destination)

1. Create PK all FK on the source table

2. Open transaction

3. TRUNCATE destination table

4. use ALTER TABLE SWITCH so move memory pages from source table to destination table

5. commit

truncate and alter table switch is only metadata transaction, so it should take milliseconds.

Answer 3

Hello,

Thnaks for your answer. I set the rows per batch to 100000 but no difference. Then i set it to 50000 but again no change. The initial value was blank.

Regards,

Andreas

Answer 4

Are you using loops in your query?

---

ESHANI. Please click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

Answer 5

You do use the Fast Load option in the OLE DB Destination, do you?
Aside from the rows per batch, also set the maximum commit size to a smaller number and disable the Check Constraint checkbox.

---

MCTS, MCITP - Please mark posts as answered where appropriate.

Answer 6

Hello,

Thnaks for your answer. I dropped the pk kai fk, then i transferred the data and finally i created the pk and fk. The whole process was very quick.

Thnaks,

Andreas

Answer 7

Hello,

I don;t use any loops.

Regards,

Andreas