SSIS Script Component Transformation C# Code Date Conversion

Question

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?

You could encapsulate the logic into a function. It would help ensure you have a consistent method across packages (as you copy paste it) there will be some overhead of calling the method. Parsing logic looks about what I had used when I ran into a similar situation (hooray for mainframe data). The only thing that might save you a smidge is if promote varDate to a member variable. Then the engine would only have to create it once and just reuse it per row. — billinkc
– billinkc, Commented Apr 19, 2013 at 21:18

Hector Sosa Jr · Accepted Answer · 2013-04-21 16:53:33Z

1

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.

answered Apr 21, 2013 at 16:53

Hector Sosa Jr

4,27030 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

DavidStein Over a year ago

I'd love to make this a .Net assembly. However,I'm not sure how to handle the Null portion. How can I dynamically return the Row.FIELDNAME_IsNull = true; with an assembly?

Hector Sosa Jr Over a year ago

I'll need to open BIDS and test it out.

Collectives™ on Stack Overflow

SSIS Script Component Transformation C# Code Date Conversion

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related