January 21, 2013 at 11:02 am
My company has a very archaic system that doesn't believe in dates. All dates in the system are stored as 3 integer columns: Year, Month, Day. I'm sure you will be shocked to hear that non-date data has crept into these columns. (e.g. Month = 13) I need to extract all these dates through SSIS, and I want to know the best approach. I wrote the following script, but I feel like there should be an easier way. Is this the best way? If not, what approach should I use? If so, how do I turn this into a custom component?
My code takes
4 input columns: Year, Month, Day, and Default Date
3 control variables: Approximate and DateFormat
and results in
2 output columns: CleanDate, Modified
The logic is:
1. If Year, Month, Day can be parsed into a date, return the CleanDate and Modified = false
2. If Approximate = Exact, return the Default Date and Modified = true
3. If Approximate = Close, try to find a close date to the input columns, return the new date and Modified = true
4. If a close date couldn't be found, return the default date and Modified = true
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
DateTime? cleanDate;
bool modified = false;
cleanDate = ParseDate(Row.Year, Row.Month, Row.Day, Variables.DateFormat, Variables.RevisionDateApproximate, Row.DefaultDate, out modified);
if (cleanDate.HasValue)
{
Row.CleanDate = cleanDate.Value;
}
else
{
Row.CleanDate_IsNull = true;
}
Row.Modified = modified;
}
enum HandleErrors
{ Exact=0, Closest }
private DateTime? ParseDate(int year, int month, int day, string format, int approximate, DateTime? defaultDate, out bool modified)
{
DateTime dDate;
DateTime? value;
modified = true;
if (defaultDate.HasValue)
{
value = defaultDate;
}
else
{
value = null;
}
if (!(ValidateFixYear(year) == null) && TryParse(year.ToString() + "-" + month.ToString() + "-" + day.ToString(), format, out dDate))
{
value = dDate;
modified = false;
}
else if (approximate == (int)HandleErrors.Closest && !(ValidateFixYear(year) == null) && modified)
{
year = ValidateFixYear(year).Value;
month = ValidateFixMonth(month);
if (TryParse(year.ToString() + "-" + month.ToString() + "-" + day.ToString(), format, out dDate))
{
value = dDate;
}
else if (day < 1 && TryParse(year.ToString() + "-" + month.ToString() + "-01", format, out dDate))
{
value = dDate;
}
else
{
while (day >= 28)
{
if (TryParse(year.ToString() + "-" + month.ToString() + "-" + day.ToString(), format, out dDate))
{
value = dDate;
break;
}
day = day - 1;
}
}
}
return value;
}
private bool TryParse(string s, string format, out DateTime d)
{
return DateTime.TryParseExact(s, format, null, System.Globalization.DateTimeStyles.None, out d);
}
private int? ValidateFixYear(int year)
{
if (year < 1900 || year > 2999)
{
return null;
}
else
{
return year;
}
}
private int ValidateFixMonth(int month)
{
if (month < 1)
{
return 1;
}
else if (month > 12)
{
return 12;
}
else
{
return month;
}
}
January 21, 2013 at 11:43 am
You're on the right track, I think.
But what I would do is create a try ... catch block and attempt to do the convert to date in the 'try' bit. You can probably do this in a single line of code.
If that fails, processing goes to the 'catch' bit and you can handle the 'unsuccessful' case there.
Hope that helps, but if you remain puzzled, post back and I'll try to find a few minutes to mock it up for you.
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