March 4, 2010 at 4:33 pm
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 nutshell..my 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.
Thanks,
Bill
March 5, 2010 at 1:31 am
I would do it the way you suggest - but as part of the package. Step 1 - Script Task to validate spreadsheet format.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply