How Do I Find What Is Populating A Table?

I constantly run into this problem. I am working in a data warehouse and I cannot find out what is populating a table. Typically the table is being populated on a daily basis from

Solution 1:

There is no way to tell, after data has been inserted into a data, where the data came from without having some sort of logging.

SSIS has logging, you can use triggers on the tables, change data capture, audit columns, etc. are the many ways to do this.

Solution 2:

Frequently, if you know when the row was added, that can help you figure out what process is adding it. Add a new "InsertedDatetime" column to your warehouse table and give it a default value of getdate(). If you know that the rows always come in at 11:15 AM, you can use that to narrow your search.

That will probably be enough information, but if that doesn't help you track down the process, then you can add additional columns that contain everything from a source IP address to a calling object name.

As a last resort, you could rename your table and create a view named the same and then use an Instead Of Insert trigger on it that just holds open the connection so you can examine the currently executing processes to figure out where it's coming from.

I bet you can figure it out from the time alone though.