SQL Server Data Collector Woes

This post is to list the idiosyncrasies I came across while setting up Data Collector.

Cache directory
Issue: As the name implies this is the location where the cache collects the data. When clicking on the ellipsis it opens up the drive and folder structure of the server where the management data warehouse resides. Guess what? If you specify a folder on that server you will get the following error: The system cannot find the file specified.
Solution: The folder needs to exist on the server against which the data collection is executed.
Tip: This folder seems to be used even if you set the data collection to be non-cached.

Permissions
Issue: Login failed for user error.
Solution: Make sure the user set up to run the SQL Agent Service has access to the database server where the management data warehouse resides.

QueryActivityUpload
Issue: The error "Arithmetic overflow error converting expression to data type int" during Query Statistics processing.
Solution: As per this Connect page explains Cumulative Update #5 for SQL Server 2008 SP1 will fix this problem. However, if you cannot wait to get and apply that update there is a manual workaround explained on that same page. The workaround involves modifying the QueryActivityUpload SSIS package. I went by that route and the modifying the package does work. I just want to add that There are 2 places where the fix needs to be applied: OLE DB Source in the DFT - Create Interesting Queries Last Snapshot and the ODS - Get current snapshot of dm_exec_query_stats in the DFT - Create Interesting Queries Upload Bath dataflow tasks.