Thanks John,

This package wouldn't use any configurations and everything saved with in the package. Interestingly it works fine when I login as Agent service account then give right click and execute package all good but only problem when running as SQL Agent Job.

Error message keep varying and not the same for every failure as shown below..

failed with the following error: "String or binary data would be truncated

DSN contains an architecture mismatch between the Driver and Application