Post reply

SSIS Hangs on Attempted Derived Column Data Conversion

  • February 2, 2009 at 10:36 am

    #128438

    Hi All,

    I have a Derived Column transform inside a Data Flow that is used to convert data to a database timestamp data type. My source is a flat file with a column of data that represents a date of birth as YYYYMMDD (ex: 19820131).

    I have a derived column that converts this into a format acceptible to load into SQL server by doing the following:

    SUBSTRING(DOB,5,2) + "/" + SUBSTRING(DOB,7,2) + "/" + SUBSTRING(DOB,1,4)

    I save it as a new column DOB_Clean with a data type database timestamp.

    This works fine for the most part. However, occasionally bad data comes in that column, such as text. Yet instead of erroring out like i'd expect with a data conversion error, SQL Server just "hangs" on the derived column transform and appears to be in progress (remains yellow). I never get an error, which is causing issues as I am not notified there is an issue, everything looks like it's ok, etc. But at the same time, the package never progresses.

    Has anyone run into a similar issue or have a suggestion on how this can be resolved? Is there perhaps a better way to do the conversion to a database timestamp type than what I'm doing above? Any help would be greatly appreciated.

    Thanks!

  • February 2, 2009 at 3:48 pm

    #936786

    Sorry I won't be able to give you a reason why this occurs, but I got around this problem by simply putting the data into the desired format and having the output DT_STR - not converting the datatype in the derived transform. I added a data conversion transform and then converted the datatype to the desired type in that transform. Add error handling tables to redirect bad rows from your derived and conversion transforms.

    You also might want to check for NULL values in the data before trying any derivations or conversions. There is also a checkbox in the OLE DB Source that will have SSIS "handle" nulls throughout the flow.

    Hope some part of this drivel helps you out!

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply