SSIS Custom Transformation Component

We have a data migration routine that is currently a set of stored procedures that pulls data from multiple databases and consolidates it in one database for reporting purposes.  My newest task… move this into SSIS.
 
One of the issues that we have with the existing databases is that dates are stored in multiple formats throughout.
 
Currently this is addressed using a udf that converts all the dates to a fixed format during consolidation.  So each date is wrapped in this function.
 
E.g.
INSERT INTO DestinationTable
  SELECT
    Field1,
    Field2,
    udf_cleandate(Table.Field) as CleanedDate
    etc…
 
Ideally I would have liked to standardise dates throughout the systems, but this is not an option.
 
After much searching I was finally pointed in the direction of creating a Custom Transformation Component. 
 
Two hours later and a few lines of C# and…
 
My Custom Transformation Component works!
 
None of this will mean much to most of my buddies…  but to summarise… today is a good day!
 
 

Be First to Comment

Leave a Reply