Generic tool to import data to SQL ?

is there a way to replicate / "copy" the SQL server import feature of SQL server management studio? this way you can let an end user without SQL server management studio import data to database?

i.e. I need to customise my application with a user friendly form, where user picks data source e.g. Excel, SQL, Access and can import data to database?

so users can import data from things like
-SQL Server
-Flat files
-Microsoft Office Access
-Microsoft Office Excel
without field names etc. having to match exactly
rwallacejAsked:
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.

Walter RitzelSenior Software EngineerCommented:
So, are you asking if exists such a tool? Or are you asking if you should build this feature in your application?
Well, such tool exists and you can find them easily on Google. Now, if you want to build it, I believe that you'll face some challenges, as this is not a trivial app, as it requires works with many different file formats and databases.

My advice would be: find a good tool and let your users use it.
Or better than this (if load target is always the same, just the source changes): create a scheduled job that will allow your users just drop a file in a folder and it will load into the database for you.
0
pcelbaCommented:
You should rather create custom solution.

Usable SQL Server data should follow strict rules which is impossible to achieve when you allow users to import whatever data they find on the harddrive.

Unformatted data are useless on SQL Server. It is better to preserve them in original files on the disk.

Once you ensure the correct data format and validity then the SQL Server processing is much easier and faster and you don't need extra code in your applications which checks all the values.

The custom solution can use ODBC data access and you may write it in almost any language of your choice.  Simply write a small .NET application which opens the input data source, then it can read the data, check data, refuse/convert data, and then export them to SQL Server in given format via another ODBC connection.
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
rwallacejAuthor Commented:
how to best build this feature into my application
I have application with back end database etc.
I want add-in to application to allow import data to it, without having to use SQL server management studio, so it's more user friendly
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Walter RitzelSenior Software EngineerCommented:
Custom solution is not always the best solution.
I would build a custom solution if it is part of the business of the application allow users to import data. Then you need to define 2 things:
- the format of the source: csv file, excel file, access database: which one of those?
- when this should happen?

After that, you need to code whatever is the solution, using most probably whatever you know from the language. Now, it it were me, I would not create this load routine inside the application, but as a separate job, to prevent that a too large load would impact the performance of the application.

If you are not familiar with those concepts, please let me know and I can try to explain.
0
rwallacejAuthor Commented:
end user wants to pick the format of the source: csv file, excel file, access database (they have different data sources and want to capture these different sources in one database)

(this is not main feature of application though, but to get data into SQL, the calculations etc. are all done from SQL data...the problem is getting data from multiple data sources into SQL, when the source data format is varied /not consistent )
0
Walter RitzelSenior Software EngineerCommented:
At least the target to load the data into is the same?
0
pcelbaCommented:
Applications processing SQL Server data use various data interfaces obviously. Such interfaces can work as a standalone applications or they can be included inside the application (e.g. new main menu option). Even when you create standalone interface the source code is shared with the application source obviously because SQL data are common and many parts are reusable.

The final implementation depends on you and on your customer requirements. Ask them.

Be ready for the future extensions... Once you finish input data interface users will ask for output data interfaces to export app data into Excel, PDF, etc. etc.
0
colly92002Commented:
Since "import" creates an SSIS (SQL Server Integration Services) package via a wizard, why not use SSIS?  THis is the Microsoft tool for building data integration packages.  This can build simple or sophisticated packaes to import, transform, and update data that can be scheduled to run when you need them.

It can be downloaded from here:
https://www.microsoft.com/en-gb/download/details.aspx?id=36843

If this is too complicated, and you really must use the wizard, give your users SQL Express management studio and they will get the wizard, the bit you need is DTSWizard.exe, located in (depending on version of SQL) : C:\Program Files\Microsoft SQL Server\100\DTS\Binn.
https://msdn.microsoft.com/en-us/library/ms140052%28v=sql.105%29.aspx

You should secure your databases.  If you don't trust your users to have direct access to them, they should not be doing this.
0
pcelbaCommented:
Also the Interface implementation may be very variable. From one simple task working on fixed data structure to a complex engine which allows to define data structures and appropriate checks and conversions before the final upload into SQL database.

Of course, you may define SSIS package to import data as the first attempt:
https://www.mssqltips.com/sqlservertutorial/203/simple-way-to-import-data-into-sql-server/
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
You can always treat sources as database tables, and then copy row by row. This can even work with CSV files. SSIS (and the import wizard) do nothing different - they open a datasource, check for structural data like column names and formats, and try to translate that into SQL Server terms. It is like creating INSERT statements populated with (bound parameter) data.
0
PortletPaulfreelancerCommented:
"I need to customise my application"
so, it is your application, and I would assume you only want customers to import data into a SPECIFIED STRUCTURE (or a small list of specified structures)

e.g. the ability to import payments for a fiscal year, the ability to import expenses for a fiscal year

      i.e. you are not looking for a "generic solution" that allows import of any data into any table


BUT:

You want to do this without caring about column names. Really?
What is the probability your clients have data in the correct field sequence for each row? (about 0%)

I think you would need AT LEAST a way to MAP the client columns into your columns.


From the absurdly little I know about your business needs: I would be looking for an off-the-shelf library you can leverage in your .Net code, and from that build a custom solution. Big assumptions include that such a library exists, I don't know if one does.
0
rwallacejAuthor Commented:
"AT LEAST a way to MAP the client columns into your columns."
YES: this is correct. agree little likelihood of data being in correct format already.


So I need to know if such library exists, or some "built in" code/features in .Net that would readily assist, without having to re-invent wheel
0
pcelbaCommented:
Yes, this tool exists. SSIS was mentioned in two answers already.
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.

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.