Is it possible to run a SQL script from SharePoint?

I have been trying to accomplish something that I am not even sure is possible. I am trying to automate the process of importing a Excel sheet (Excel 2008) to a SQL 2012 database. I created a custom web part in SharePoint to include a browse feature, text box for path, and a submit button for users to select an excel file from their hard drive. This excel file is be imported into a temp table, be manipulated in the table, and then inserted into another table.

I assume that the SharePoint credential should be assigned to a svc account or active directory that grants the user access to the SQL server to allow the inserting data into the temp DB.

I made the web part, but I am not even sure if I am on the right track because I am having issues with the SQL import script.
Who is Participating?
colly92002Connect With a Mentor Commented:
If I understand you correctly then you can do all the things you want by coding a custom webpart  using Visual Studio 2010 or later.  If you think you could write (for example) a simple console app that could open up an Excel file and read it into a database, then provided you have the correct dev environment its not too much of a step to turn that into a web part.

First I would take a look at this basic tutorial:

If you can follow these steps and build and deploy a basic webpart, then it shouldn't be too much of a jump to add the code to manipulate the Excel file and read into a database (which is all standard .Net).
Rainer JeschorCommented:
first of all: which SharePoint version and edition?

That depends a little bit on your code.
If you run your code inside a SPSecurity.RunWithElevatedPrivileges method, the code gets executed with the account with whom the IIS application pool is running therefore you have just one user to configure.

How do you manipulate the Excel file - Excel COM or Office XML?

Perhaps you could share a little bit more info on your exact solution approach as well as already existing code.

Thanks and HTH
JMR77Author Commented:
Sorry for the delay, I thought I had it covered and I was wrong. Ended up trashing previous code and starting from square one.

I am using a SharePoint 2010 Enterprise edition, but access to the central administration is limited.

I guess the goal is having a user click a button on a web part to open up a file explorer window and browse for an excel file and click submit. Then this webpart would run a import script to copy the data from the excel file into a database in a external (not the SharePoint SQL server instance) SQL database. I may not need all the data and one of the fields would be created depending on the sys date.

I am a .NET developer with a bit of SQL experiance but I am a SharePoint noob. While researching I thought I might be able to create the interface using web parts, and I see there is a library of these already, but they are fairly vague and obtuse. So am I wasting my time going about in this way or am I over complicating things?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.