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!
 
 
Advertisements
This entry was posted in Programming. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s