I am working with SQL Server 2012 (64 bit) and I have approximately 350 text files that are updated and ftp’d to a network drive daily. I would like to access the most current data on a daily basis. The files are views (essentially tables) FTP’d from a vendor database daily. The daily files will change significantly enough that I need the tables to reflect the newest available information. At this point I am not entirely sure which files are priority since I am in the early stages of development but I don’t want to spend time bringing new data in as I find out I need it. For instance, I will be auditing the Vendor database to figure out why an interface or process is not working correctly. In that case, I don’t know what data might be relevant I will be researching various possibilities to identity a root cause.
In past, I have used a linked server SQL server express (32 bit) with some success. I know I can import all the data daily and delete all the data at the end of the day and repeat daily. If I am pulling 2 gigs of data daily, I am not sure this is an advisable solution. I am concerned about performance issues (upload time, fragmentation, etc.). I am not convinced a Linked Server is the best solution but I am perplexed as to why it won’t work. When I test the connection the connection succeeds. However, when I navigate down the hierarchy to Tables or Views I receive an error (attached).
Here are a few links of pages I found somewhat helpful but have not yet resolved my issues: