3

I have read a lot about linked servers being bad when there are multiple hops across servers and distributed joins.

What if I am just using a linked server to import data into SQL Server from an Excel spreadsheet?

What if that's all I am doing and the rest is being all done in SQL Server itself on a single server?

I want to avoid SSIS owing to headaches with multiple transactions and chaining them to each other (which is failing for some reason).

  • Why not investigate that "some reason" instead? SSIS is the suggested way to import/export data to/from SQL Server. Linked Servers have security/stability and performance issues. They're actually good for transfering small amounts of data and perform distributed transactions in T-SQL. – spaghettidba Sep 1 '15 at 14:04
  • Thank you, that is true, I am looking into that too. I have described in a bit more detail below as a comment to an answer. – Ravi Sep 1 '15 at 14:31
4

You can use the Import wizard to load your data. The Import/Export wizard actually uses SSIS to do the work. But if your goal is to avoid learning SSIS, then just do not save the package.

Simple way to import data into SQL Server

That MSSQLtips article walks you through setting up the import and then run the data into your SQL Server destination.

If you want to reuse the package (which is probably a good idea) there is a save option that allows you to save the SSIS package that was internally generated and use it again and again.

However, if the Linked Server approach is the way you want to go, there is a tutorial here:

Excel Import to SQL Server using Linked Servers

This is somewhat more complex to set up, but should work OK.

UPDATE: based on your comment about coordinating two transactions you might find it simpler to import the data into two staging tables. (You would most likely truncate those tables before each load.) The table Stage01 receives one load and the table Stage02 receives the other load.

Then, now that both data sets are in their own tables, you can join between Stage01 and Stage02 data sets to insert the resolved data into your operational database tables. This would likely simplify the processing of the two data sets.

  • Thank you very much for your useful answers. I am just not able to get two transactions to relate to each other in SSIS. One completes and the other dependent transaction does not. I wanted there to be dependency between both transaction containers. So I was thinking of setting everything up using SQL job agent rather than rely on SSIS solely for this. – Ravi Sep 1 '15 at 14:30
  • A SQL Agent job is a good mechanism for ensuring that a repeating task happens on a schedule. A suggestion added to my answer. – RLF Sep 1 '15 at 16:01
  • Thank you very much for this. I will go with that. Will avoid SSIS and schedule it using SQL Job Agent. – Ravi Sep 1 '15 at 23:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.