July 5, 2010 at 11:10 pm
I'm looking to create a more advanced IMPORT process from Excel to SQL Tables.
I want to enhance the validation checking of each column/row of excel data before i use my Split Task to INSERT validated rows or reject and log invalid rows of data.
I am thinking a Script Task is probably the only way i can accomplish this to validate each row within a loop using code validations on each column of data.
My column data types in each row of data will vary across:
- Date Fields (formatted dd/mm/yyyy)
- Integer checking
- Currency checking
- Text String only
- plus maybe some more advanced Regular Expressions (eg. email addresses)
Can anyone help me or point me in the direction of some sample code or suggest a better solution.
July 6, 2010 at 1:39 am
Excel is such a pain to import from that a lot of this validation will probably be done for you, though not in the way you want. Invalid data will come through as null or will cause run-time errors before you get a chance to trap them. If you can use CSV files instead, do it!
You may find that a Script Component, not a Script Task, would give you what you need. Perform the validation there, on a row-by-row basis, as part of your data flow, setting an appropriate flag or flags against each row. Then you can split based on the flags.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 6, 2010 at 1:55 am
The BCL(Base class library) bundled with .NET architecture is one of your way out.
For example
You may employ the overloaded method of ToString() function to parse the format of your Date string
Also, overload of TryParse() to validate your Integer data
The best according to me would be create Regular expression...:w00t::w00t:;-)
Parse your input data on Row by Row basis.
Raunak J
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply