Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

General Question - How best to validate an excel spreadsheet prior to running ssis pkg Expand / Collapse
Posted Thursday, March 4, 2010 4:33 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, October 6, 2015 12:33 PM
Points: 285, Visits: 349
I've been learning ssis on the job the hard way over the past several months. So far, so good. I have a process by which my users can import spreadsheet data into my database. In a C# winform app calls a stored procedure, the sp calls dtexec to run the ssis package for me.

So far this has been fine but it's weak on the validation of the spreadsheet prior to running the ssis pkg. Because my users receive their spreadsheets from other sources the layout of the spreadsheets is not always correct.

One of the things I do is capture the ssis output from each process and store it as part of my audit information, so luckily I can read thru the outputs. Needless to say the successful imports have nice short log files with success in them, and the errors have crazy long log files full of meta data errors that are difficult to sort thru.

So, in an effort to beef up my process, does anybody know of any processes by which I can validate my spreadsheets prior to importing them ? They are very simple, a handful of columns with the field names in the first row.

I would like to be able to at least validate the column names and sequence, and possibly the number of data rows in the spreadsheet.

I'm starting to think that I will have to open the spreadsheet in C# via ado and read the first row to do this. Other than that I'm pretty much clueless.

Does anybody have any experience with this sort of thing, or can anybody point me in the right direction ? I've never had the need to interrogate a spread sheet in this way and just need to get pointed in the right direction.

Post #877273
Posted Friday, March 5, 2010 1:31 AM



Group: General Forum Members
Last Login: Today @ 5:25 PM
Points: 7,977, Visits: 19,108
I would do it the way you suggest - but as part of the package. Step 1 - Script Task to validate spreadsheet format.

Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

If your posting includes some T-SQL code, please surround the code with IFCode formatting tags. It helps readability a lot.
Post #877459
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse