I have an SSIS package that takes data from a table in Access and puts it into a fact table in SS2005. Very little data manipulation is done. It processes approximately 1.5 million rows when it runs weekly. The process is run in an SSIS package that is called by a parent package, and all of that (including the use of the config files and accessing the parent variables) is working fine.
The issue is there is one field in the Access table that must be put into a different SS2005 fact table.
When I run the data flow task that loads the first fact table, it completes in less than two minutes. However, if I either (a) put a multicast step in the dataflow task to redirect a copy of the key data and remaining field to the second fact table, or (b) copy that step in the package to have it perform the same tasks with the different target (and using just the key and the remaining field), the execution time suddenly jumps to 30 minutes. In the case of (b), it remains true whether the copied step remains in the package or is executed in its own package, and also remains true if the package is loading against a table that starts out empty or with data already in it.
Has anyone ever bumped into a situation like this?
This could be because of a number of things, but I think the most likely are probably that the slow table is heavily indexed or has foreign keys being enforced. Also, make sure you're using fastload.|||[banging head against desk]
Fast load ... that was it. Doggone it. Thanks for indulging a goof.
