1

I need to have users import Excel/CSV files to my database.

Currently, I have a VB.net application that will let me import CSV files only to our database. Rather than scaling this application to keep fitting my needs, and deploying it to users to import data, I'm considering switching to SSIS.

How do I deploy packages so that my users are able to use them to import Excel/CSV files? I know SSIS is not intended to be a front end, so should I not use it for my needs? Is it only used for SQL Developers to import data?

Also, my users have no experience with SQL or using a database. They are used to putting their excel files on Sharepoint or pass them around via email. I just introduced them to SSRS which works wonderfully as a reporting service but I need a simple and reliable import process.

AlanPear
  • 737
  • 1
  • 11
  • 32

1 Answers1

3

Probably not for a few reasons:

  • You'd have to deploy the SSIS runtime for the package to run - this is not something that is usually done. You'd probably have to pay a licence cost

  • SSIS stores metadata (i.e. the type and number of columns in the source and target). If this metadata changes then the package will usually fail

  • SSIS is a server tool. It 's not really built for user feedback

Excel as a source is difficult for two reasons:

  1. It has no validation. Users can put anything they want in it, including invalid or missing values

  2. Excel drivers work out metadata by inspecting rows on the fly and this is sometimes incorrect (I'm sure you've already encountered this in your program)

A custom built solution requires more maintenance but has a lot more flexibility, and you probably need this flexibility given that you have excel sources.

If your excel files are guaranteed to be clean every time, and all of your users use a single SQL Server (with a single licensed install of SSIS) then it might be practical.

Added to reflect discussion below:

In this case you have consistent data files coming from elsewhere that need to be automatically uploaded into the database. SSIS can help in this case with the following proven pattern:

  1. User (or process) saves the file is saved to a specific shared folder
  2. A package, scheduled to run every (say) one minute in SQL Agent, imports all files in that folder
  3. If the import is successful, the file is moved to a 'successful' folder
  4. If the import is unsuccessful, the file is moved to a 'failed' folder

This way, a thick client app doesn't need to be deployed to everyone. Instead any user can drop the file (if they have share access), and it will be automatically pulled in

Users can also confirm that the file was successful by checking the folder

Here's an example of a package that imports all files in a folder and moves them when complete:

SSIS - How to loop through files in folder and get path+file names and finally execute stored Procedure with parameter as Path + Filename

Nick.Mc
  • 18,304
  • 6
  • 61
  • 91
  • Currently, my excel files are pretty consistent. But occasionally the small changes will occur. Right now I use the excel column and map to table columns with same name. If the don't match the import fails. Should I move forward using my in house application? Is there any other sort of method to get users to import excel data? – AlanPear Jan 07 '18 at 23:53
  • You should probably identify any issues you have with the current process. Is your main concern that you have a custom app for something that should be easier to do? I wouldn't be too worried. As an example of something to compare to, there's an import option in SSMS (which is free) that goes through a wizard and lets you import data but I never use it. Perhaps you should try it just to compare (actually I think you need to install SSDT to use it - too hard!!!) – Nick.Mc Jan 07 '18 at 23:57
  • I've used that. My issue is that it doesn't fire triggers. My code t least gives me the flexibility to do that. As mentioned in my question I was only able to successfully import CSV data. I'm not much of a programmer an am still learning. I was seeing ssis could save me the trouble of scaling the app. – AlanPear Jan 08 '18 at 00:00
  • Are you saying you need to extend your existing app to import excel as well as CSV? Anything that imports excel uses the Jet or ACE driver (whether it's an app or SSIS) and that's the main problem. However in a custom app you have more flexibility and options. If the delivery mechanism is sharepoint then maybe SSIS might be handy as it can (with some degree of effort) get files off sharepoint and import them. Normally though, you don't import arbitrary files into matching tables. Normally you have a specific table that repeatedly has data merged into it – Nick.Mc Jan 08 '18 at 00:10
  • Yes. I do use the ace and jet engine for that. My problem is posted here: https://stackoverflow.com/questions/47398344/allowing-vb-net-app-to-convert-excel-files-to-datatable. I also considered the idea od using sharepoint to import my data. I posted another question regarding that here: https://stackoverflow.com/questions/48139839/ssis-excel-csv-from-sharepoint-to-sql-server. – AlanPear Jan 08 '18 at 00:22
  • As another point of comparison: to access sharepoint documents from SSIS, you need to use a custom script task. Now: you could either write these in SSIS, a tool that's not really built for it, or you can write these inside a custom application, a tool that _is_ built for running custom code. When SSIS capabilities run out, you need to write .Net code, and SSIS is not really the best place for it. I think really you're on the dividing line here. It seems like you've already started learning a technology. If you were to use SSIS, you'd have to go and start learning that. – Nick.Mc Jan 08 '18 at 01:18
  • The thing I'm curious about is: are these people loading into _new_ tables every time? If so how are they using them? If they're loading into exsiting tables then you must have some way of merging the data (i.e. not throwing data away when you import new data). Because you can't build an SSIS package for a table that doesn't exist. – Nick.Mc Jan 08 '18 at 01:19
  • My application has a drop down list for what tables they are importing to. The primary one they take excel files sent from our customers, and import it into the table. I was told its always in the same format and never changes. The other tables are for raw output from hardware configurations. We have a little more flexibility with those since they are generated in house. But no, they are not creating new tables on import. – AlanPear Jan 08 '18 at 01:23
  • You could certainly build an SSIS package that grabs a file from sharepoint and tries to import it into a fixed table based on filename or something. But if anything goes wrong you'd have to email it to the end user, as there is no (native) interactive component. It's easier for SSIS to import from a file share than sharepoint. So I suggest you drop your client files into a fileshare then SSIS regularly checks and imports it from there - SSIS _is_ good at doing that. I regularly build packages that do that. – Nick.Mc Jan 08 '18 at 01:37
  • In fact that is a much more suitable data import pattern. I suggest that would work better than having to deploy a client app to everyone who might want to import it. The way it usually works is you have a 'waiting' folder which is checked every five minutes (implemented in a SQL Agent job) by the package. If it works the file gets moved to an 'imported' folder. If it fails it gets moved to a 'failed' folder, and an email is sent (somewhere) – Nick.Mc Jan 08 '18 at 01:42
  • Here's an example of a package that imports any and all files in a folder then moves imported files to another folder https://stackoverflow.com/questions/38151342/ssis-how-to-loop-through-files-in-folder-and-get-pathfile-names-and-finally-e – Nick.Mc Jan 08 '18 at 01:45
  • This seems like what i've been looking for. Does it look folders plus subfolders? Can I make it so the package deploys anytime someone uploads a file? – AlanPear Jan 08 '18 at 02:35
  • You deploy the package once. You schedule to run every minute. If it finds any files it processes them. You can get it to search subfolders it’s just a tick box. – Nick.Mc Jan 08 '18 at 02:37