0

I am trying to find the easiest and simplest and quickest way to upload a sheet from Excel to a table in SQL Server 2012 automatically every morning as a job from a location on my folder to the table.

Improve this question
7
  • What have you tried so far? Do you have any part of the process working? Commented Sep 16, 2019 at 20:22
  • i have manually added it so far. just looking online i read ssis is the best solution but i dont have visual studios Commented Sep 16, 2019 at 20:25
  • SSIS runs on the SQL server. Do you have access to the SQL server? Have you tried to run this from SQL or just from Windows. You can schedule tasks in the server with the agent. Commented Sep 16, 2019 at 20:28
  • i have access to sql server and the the server and the table i am trying to dump this into. the schedule tasks does not work for excel sheets from what i read online? Commented Sep 16, 2019 at 20:36
  • There are several examples via Google. See this one here for example. Commented Sep 16, 2019 at 20:42

1 Answer 1

Reset to default
1

SSIS is the ETL tool you could use, but if it’s a very simple job you can just write a BCP command.

https://learn.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-2017

The way the schedule it is to add the task to the agent job on the server. A few things to bear in mind with ETL:

Will your file be named the same each day?

Do you need to retain archived versions of the file?

How do you do error handling if it’s absent or malformed?

Does the DDL need to change periodically to accomodate new date ranges (I.e. new day/month year) Will this pattern be reused in the future?

Do you need to test logically (duplicates/logical fallacies/referential integrity etc)?

Under whose account will the job run (hint, don’t use your own - get a service account)?

The more complex the answers are to these types of questions the more likely you’ll need a real ETL tool like SSIS

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

thanks! i found this guide online for ssis. red-gate.com/simple-talk/sql/ssis/… do you think this is sufficient for this job? also how will ssis handle name changes to the file and column name changes if they were to happen?
File names are usually done by patterns and wildcards. E.g. you might have a file called sales_20191123 which, tomorrow, will be called sales_20191124. Here you’d set up a mask - the directory and the first part of the path are static, the rest are wildcards
Column changes are more tricky, you can do it by column index position but personally I’d avoid that, if your input is that unstable I’d focus my dev efforts on hardening it up. But that’s situation dependent of course.
Finally, I wouldn’t bother with red gate initially - vanilla SSIS is almost certainly fine. That said the red gate tutorial looks fine.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.