Query Excel, CSV, Access Table via a Linked Server in SQL Server 2012 (64 bit)

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:
http://www.users.drew.edu/skass/sql/TextDriver.htm
http://sqlwithmanoj.wordpress.com/2012/07/10/querying-excel-2010-from-sql-server-in-64-bit-environment/
http://akawn.com/blog/2012/01/
http://www.microsoft.com/en-us/download/confirmation.aspx?id=13255
PipeError109.PNG
LVL 1
HRISTeamAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Have you considered creating SSIS packages, either through the Import wizard or manually?
Also, SQL has the MERGE Statement that allows the developer to dictate which data is inserted, updated, or deleted all in one statement, so that only new rows would be inserted.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HRISTeamAuthor Commented:
The merge statement seems like a solid approach. The only major drawback I see is that it will be much more coding intensive to identify changes as opposed to overwriting a linked file from Access which is then linked to SQL Server.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
I agree that putting together a MERGE, let alone up to 350 of them, will take longer than coding the equivalent DELETE and Insert.  

The big benefits are..
The execution time is greatly reduced, as it does not INSERT every row.
The auditing of the rows is intact, so if a row has been the same since March 1st, the created_dt or last_updated_dt rows will remain March 1st, as the row was never touched.  ESPECIALLY if a typical troubleshooting path is 'I want to see all the rows that changed YESTERDAY.
Easier handling of deletion if rows are in source but not in target, and target but not in source.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

HRISTeamAuthor Commented:
The only issue with Merge or Drop and Reload is that I would have to do a good deal of rework. I already have the Access database updating the information on a daily basis so I really don't want to start a long process if there is a solution to getting what I have already created available.
0
HRISTeamAuthor Commented:
That is too much rework. Any additional ideas?
0
HRISTeamAuthor Commented:
I spent over week messing with this issue with no real help troubleshooting my Linked Server issue so I made some basic documentation in the event someone else has the same problem.
LinkedServer.docx
0
HRISTeamAuthor Commented:
I know I can use SSIS packages but that is not what I wanted to do.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.