Can we use SSIS to parse data from application before we store in db...?

Question

Exploring the feasibility of something here... We have a UI page in the mvc3 web-application that the admin will use to upload a 'csv' file. Is it possible to have an SSIS package that will be called by the application..that will validate/transform all these data and store into db if its all valid or throw back error details to application if there are invalid data?

Just trying to leverage the capabilities of SSIS to do the parsing of file and transformation of data. If this is not possible, is there a better way?

Please provide your thought..much appreciated!

update: yes, need to execute ssis on the fly.. and give back a message on the UI page ..telling the 'uploader' if his upload failed or not based on message that SSIS would give back to the application.

Answer 1

You could use SSIS to parse a text file. You could also use a sledgehammer to drive finishing nails. Better bet would be something like FileHelpers to keep you out of the text-parsing mud of parsing the text file yourself.

---

Given the edits and debate, I'll elaborate a bit. Mainly that there are a few big problems I see calling SSIS directly from your MVC controller:

a) Massive external dependency to ship around. Your dev, CI and staging environments will need full blown SQL server with SSIS enabled and probably a slew of other configuration depending on implementation details.

b) Speed -- SSIS is a big job engine, takes a while to spin up before it even starts working.

c) Messaging -- if the point of the operation is letting a user know what is wrong, SSIS is the wrong tool. Unless you want to write a tool to translate SSIS import errors into readable English. Then you should probably stop whatever it is you are doing and sell that tool rather than solving this problem.

On the flip side, most of the .NET based file parsing stuff and C# give you plenty of error handling capabilities that give you a much better shot of getting an understandable error message to a user quickly.

Finally, if you are looking at large files here, you really need to think about some sort of queuing and messaging system. In that context, SSIS could make a bit more sense though I still think the error messages border on useless.

Answer 2

Certainly an SSIS package can do that, that sort of thing is its purpose. It's easier of course if you have an experienced SSIS developer as SSIS can be quite daunting. You can create logging inthe package and exception tables to throw out the bad records and then after the process is complete, check the logs/exception tables to return any bad records to the user who kicked off the process. We have a database that stores meta data about each time an SSISI pacakage is run and you might need that too in order to be able to determin if the error record relate to the file that user uploaded or someone else's being run at about the same time.