We help IT Professionals succeed at work.

When I run the package, I get an error 'Data conversion failed while converting column birth_date_dc(derived column) to column birth_date_db (database)

dorsun
dorsun asked
on
Medium Priority
2,285 Views
Last Modified: 2013-11-10
Hi tigin44,

Your solution worked in the derived column. Later I had to use data conversion to convert to database type.
The derived column type is string Unicode String [DT_WSTR]
In data conversion I want to convert to DBTIMESTAMP. Since the data type of the column in the database is of datetime.
When I run the package, I get an error 'Data conversion failed while converting column birth_date_dc(derived column) to column birth_date_db (database)

Please help me out in this,

Thanks
Comment
Watch Question

ValentinoVBI Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
in your Derived Column Transformation, try converting your column to "database timestamp [DTT_DBTIMESTAMP] instead of unicode string

Author

Commented:
Hi,

I have changed the datatype to DT_DBTIMESTAMP in the Derived column transformation for the date column (which is <add as new column>). The source file does not contain any valid data for this column(it only contains 'NULL' in the field).
When I run the package, it throws an error '[Derived Column [5478]] Error: An error occurred while attempting to perform data conversion. The source type could not be converted to the destination type.'

Please suggest what needs to be done.

Thanks a lot!
ValentinoVBI Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
I had a look at your related question, I believe the answer to this question was already provided by HoggZilla.  In the expression you should test for NULL values and use a NULL value of type DBTIMESTAMP for the nulls.

Something like:
TRIM(your_column) == "" ? (DT_DBTIMESTAMP)NULL(DT_DBTIMESTAMP) : your_column

Author

Commented:
Hi,
I have tried like above -
TRIM(birth_date) == "" ? (DT_DBTIMESTAMP)NULL(DT_DBTIMESTAMP) : birth_date

But it shows an error when I move out of the row itself saying:

The data types "DT_DBTIMESTAMP" and "DT_WSTR" are incompatible for the conditional operator. The operand types cannot be implicitly cast into compatible types for the conditional operation. To perform this operation, one or both operands need to be explicitly cast with a cast operator
Attempt to set the result type of conditional operation "TRIM(birth_date) == "" ? (DT_DBTIMESTAMP)NULL(DT_DBTIMESTAMP) : birth_date" failed with error code 0xC004709F.

Can you please help me out?

Thanks.
ValentinoVBI Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
This means that your field, birth_date, is of type WSTR (unicode string) and thus needs to get converted as well:

TRIM(birth_date) == "" ? (DT_DBTIMESTAMP)NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)birth_date


Author

Commented:
I changed as per ur suggestion.
I still get an erro on package execution...

[Derived Column [5478]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "component "Derived Column" (5478)" failed because error code 0xC0049063 occurred, and the error row disposition on "output column "birth_date_dc" (7742)" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.

Please help! This is urgent.
Thanks
BI Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Commented:
That's because your input column does not contain a date format that's supported by a default conversion.  Here's how to convert your MMDDYYYY format to a datetime in SQL Server:

TRIM(birth_date) == "" ? (DT_DBTIMESTAMP)NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)(DT_STR,10,1252)(SUBSTRING(birth_date,5,4) + "/" + SUBSTRING(birth_date,1,2) + "/" + SUBSTRING(birth_date,3,2))

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Commented:
Converting to a date type from strings is always fraught with issues. I've always thought that SSIS missed a trick by not having a check valid date function, is ISDATE(<expression>). Anyhow, you can simulate one. Create a script component in your data flow (choose the data transformation option) and create an extra output field, say MyDateOut. Click on 'Design Script' button, and the shell is already created for you. Simply script in VB.Net to check whether the input expression is valid. If it is, return a datetime type, or valid date string, if not, a NULL.

Something like this;

Public Class ScriptMain
    Inherits UserComponent

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)


        If Row.avmvalndate_IsNull Or Not IsDate(Row.avmvalndate) Then
            Row.avmvalndateout = Nothing
        Else
            Row.avmvalndateout = Row.avmvalndate
        End If
End Sub
End Class

Commented:
sorry, it should read;

If the extra output field is MyDate;

Public Class ScriptMain
    Inherits UserComponent

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)


        If Row.mydateout_IsNull Or Not IsDate(Row.mydate) Then
            Row.avmvalndateout = Nothing
        Else
            Row.avmvalndateout = Row.mydate
        End If
End Sub
End Class

Commented:
I am making a mess of this. please ignore previous post!

Instead of MyDateOut in my first post please assume you have created an input field called avmvalndate and output field called avmvalndateout;

Apologies.

Author

Commented:
never mind sanjaykp!
Thanks for your inputs

Author

Commented:
Thanks Valentino, your solution worked!
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.