I'm wondering what the community suggests for extracting data from an OData API to SQL 2008 R2. I need to create a nightly job that imports the data to SQL. Should I create a simple console app that iterates through the OData API and imports to SQL? Or can I create some type of SQL Server BI app? Or is there a better way to do this?
This is going to be sooo slow. OData is not an API for bulk operations. It is designed for clients to access individual entities and navigate relations between them, at most paginate across some filtered lists.
Extracting a entire dump via OData is not going to make anybody happy. The OData API owner will have to investigate who is doing all these nightly crawls over his API and discover it is you and likely cut you off. You on the other hand will discover that OData is not an efficient bulk transport format and marshaling HTTP encoded entities back and forth is not exactly the best way to spend your bandwidth. And crawling the entire database every time, as opposed to just discovering the deltagrams from last crawl, is only going to work until the database reaches that critical size S at which the update takes longer than the frequency you're pooling!
Besides, if is not your data, it is extremely likely that the use terms for the OData API explicitly prevent such bulk crawls.
Get a dump of the data, archive it, and copy it using FTP.