SSIS hangs on the lookup update

pat dev 1 Reputation point
2021-03-25T17:45:52.947+00:00

Hello all,

I am trying to improt flat file to sql:

problem:

csv source datatype dt_str data format : "1/2/2019 12:00:00 AM"

the data type in database is datatime2(7) = 2019-01-02 00:00:00.0000000

I tried setting the flat file source to data tie dt_dbtimestamp2 with scale 7

casting to varchar50 in lookup is hanging the package. inert goes fine.

Any suggestion and help pleasee!!

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
1,856 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,571 questions
No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Monalv-MSFT 5,686 Reputation points
    2021-03-26T02:51:55.88+00:00

    Hi @pat dev ,

    We can use Derived Column Transformation to convert the datatype from dt_str to dt_dbtimestamp2.

    Expression: (DT_DBTIMESTAMP2,7)((DT_DBDATE)(TOKEN(Date," ",1)))

    Please refer to the following pictures:
    81763-df.png
    81648-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.