I've got a transform package created by my dev team. It runs in under 2 minutes in Test, but on Prod it takes hours. I did a test and ran the package from a separate SSIS instance, just pointed to Prod environment, and it completed in under 2 minutes. So the issue is running this package from the local DB engine ssis environment. I don't see any blocking, but I do see a little bit of CX Packet waits. I've tried adjusting MDOP settings and also buffer sizing. Any ideas of the cause? or ways to troubleshoot?
have you looked at the environment/state of your databases/servers involved in this package
disk space: DATA and LOGS
state of database (are they filling up, are they full)
are you doing backups on your databases
what is the size of your databases involved
could one of your databases failing and trying to recover?
etc
Well when running the Prod package from a different SSIS instance, it performs fine, so I don't think it is an issue with the Prod databases.