Just getting started implementing tSQLt at a new shop.  Running into some challenges:

1. You can't fake tables that are referenced by schema-bound views.  These are used extensively here
2. You can't easily unit test code that makes database changes (e.g. a sliding window proc).  Or I just can't figure out how to do it in an automated fashion
3. Unit testing SSIS packages is no picnic.  For anything beyond a simple read some data, clean it, write it somewhere (faking inputs and outputs) it gets real complicated, real fast.  (not to mention that the unit test machinery needs to know how to execute a parameterized package, monitor it to completion, then check the results).
4. Unit testing .NET code that executes packages and procs and ad-hoc queries is a big challenge.

Even with these kinds of issues, the discipline of writing unit tests has already exposed edge cases where things go wrong