[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1871
  • Last Modified:

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)

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
0
dorsun
Asked:
dorsun
  • 5
  • 4
  • 3
1 Solution
 
ValentinoVBI ConsultantCommented:
in your Derived Column Transformation, try converting your column to "database timestamp [DTT_DBTIMESTAMP] instead of unicode string
0
 
dorsunAuthor 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!
0
 
ValentinoVBI ConsultantCommented:
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
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
dorsunAuthor 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.
0
 
ValentinoVBI ConsultantCommented:
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


0
 
dorsunAuthor 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
0
 
ValentinoVBI ConsultantCommented:
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))
0
 
sanjaykpCommented:
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
0
 
sanjaykpCommented:
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
0
 
sanjaykpCommented:
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.

0
 
dorsunAuthor Commented:
never mind sanjaykp!
Thanks for your inputs
0
 
dorsunAuthor Commented:
Thanks Valentino, your solution worked!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 5
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now