convert a text field(dt_str) to a time7 datatype in sql 2014

Micki Arruda 1 Reputation point
2021-04-14T17:50:43.9+00:00

In a SSIS package, how do I convert an import a text field(dt_str) to a time7 datatype in sql 2014. In other data flows, a script component was added and then in the script editor we would write code like this example

row.dfas = convertdatestring(row.column9)

this data flow was created years ago. Editing it is okay, but now I am creating another data flow from scratch in VS 2017 with SSDT and that code does not work. the only command close is convert, and it does not tell me how to write the rest.
the dt_str coming is in in a number format of 6 characters. It needs to go into SQL 2014 as time. It is supposedly in 24 hr time format

Any help is appreciated

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,460 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Monalv-MSFT 5,896 Reputation points
    2021-04-15T01:54:14.33+00:00

    Hi @Micki Arruda ,

    We can use Derived Column Transformation to convert the datatype from DT_STR to DBTIME2 in ssis package.

    Please refer to the following expression and pictures:

    (DT_DBTIME2,7)(SUBSTRING(Date,1,2) + ":" + SUBSTRING(Date,3,2) + ":" + SUBSTRING(Date,5,2))

    87976-df.png
    87977-derivedcolumntransformation.png

    Best regards,
    Mona

    ----------

    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.
  2. Micki Arruda 1 Reputation point
    2021-04-27T17:23:19.847+00:00

    So I tried this and it does not work.
    (TMLMOD1 == "") ? NULL(DT_DBTIME2,7) : (DT_DBTIME2,7)(SUBSTRING(TMLMOD1,1,2) + ":" + SUBSTRING(TMLMOD1,3,2) + ":" + SUBSTRING(TMLMOD1,5,2))

    I get the error below. I made sure one row had a null value in that field. Nulls on source and destination have been checked and unchecked to see if it would work, but it doesn't. as soon as I enter data in that field, it works again.

    [Derived Column [2]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Derived Column" failed because error code 0xC0049063 occurred, and the error row disposition on "Derived Column.Outputs[Derived Column Output].Columns[TMLMOD]" 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.

    0 comments