CODEWITHSUNDEEP
c#ssistype-conversion
DavidStein
DavidStein

Reputation: 3179

SSIS Script Component Transformation C# Code Date Conversion

I'm creating SSIS packages to import large Fixed Width text documents and need to transform various fields. One such field represents dates and is 8 characters wide. The format is "yyyyMMdd" and like this: 

SomeDateField
20130415
20110122
19891023

However, some fields have only blank spaces, and some will have all zeroes 00000000. Those that cannot be converted to a date should return a NULL value. I'm not allowed to substitute a date like 1/1/1900.

After futilely trying to convert this to a date field with expressions, I turned to C# Script Transformation.

My Input Column is called sSoldDate and is ReadOnly. I've configured an Output called SoldDate of DT-DBDATE data type.

The code I came up with that works is: 

DateTime varDate;
        bool isParsed;

        isParsed = DateTime.TryParseExact(Row.sSoldDate, "yyyyMMdd", CultureInfo.InvariantCulture, DateTimeStyles.None, out varDate);
        if (isParsed)
        {
            Row.SoldDate = varDate;
        }
        else
        {
            Row.SoldDate_IsNull = true;
        }

I then follow up with the other date fields like this one in the text import by repeating that code in the same Script Component and substituting the other field names. I don't have to repeat the variable declarations however.

Even though this works, I am going to have to repeat this process A LOT, and wanted to make sure this was the most expedient way of performing this kind of check and conversion. Is there a more concise way of achieving the same thing?

Is there a more performant way?

Is there some simple way to encapsulate the logic in a custom method or function that would make this process easier?

Upvotes: 2

Views: 2437

Answers (1)

Hector Sosa Jr
Hector Sosa Jr

Reputation: 4250

David,

One thing I've done in the past is to create (or have somebody create it for you) a .NET assembly with the code that you use repeatedly. You should be able to reference this assembly in any of the scripts you write.

I've used this for SQL Server 2008\2008 R2 BIDS. I haven't tried with 2012 BIDS yet. Let me know if you need more info. I might write a blog about this.

Upvotes: 1

Related Questions