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?
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.

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?
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).

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
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

From novice to tech pro — start learning today.