August 9, 2009 at 7:55 pm
I'm looking to enhance my ecommerce website admin section where my customers can bulk import their products using an xls/csv file IMPORT.
The columns in the file would be static so the customer would need to follow the given file specifications, however i was wondering if i can also validate the contents of the rows and columns before actually attempting to insert or update them into the Products table.
I guess i'm trying to narrow the human error factor and possibly report back to them any mistakes in the file for re-editing before re-importing.
I definately want to reject the rows that contain invalid data.
Perhaps a log file of errors encountered.
Has anyone done this level of SSIS packaging before. My guess is that i will need to write some file system scripts doing some sort of validation checks in the code and writing output to the log file.
Any ideas would be appreciated.
August 10, 2009 at 12:00 am
You can validate file level attributes through script task or system file task. Data validation might be very time consuming through script task.
I use following approach for similar kind of issue:
1. create a temp intermediate table with same schema as of your Product table.
2. for incorrect or corrupt data, create an error log file or table. Table can be better as you can do some processing on error rows and give precise error information to your users.
3. You can automatically fix few repeating human errors and move corrected records to intermediate table.
4. As a last step you can update your products table with correct data.
I hope this helps.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply