CODEWITHSUNDEEP
sql-serverexcelvb.netssis
AlanPear
AlanPear

Reputation: 727

Will SSIS fill my needs?

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.

Upvotes: 1

Views: 213

Answers (1)

Nick.Mc
Nick.Mc

Reputation: 19184

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

Upvotes: 3

Related Questions