The OLE DB provider used by the OLE DB adapter cannot convert between types "DT_DBDATE" and "DT_I4" for "DateId".

AVELIU 26 Reputation points
2022-01-10T21:17:38.447+00:00

Hi,

I am trying to load fact table using SSIS. in the fact table I have DateId.

163669-image.png

DateId should get the data from the dimensional table d_date:

163682-image.png

I have created this package in SSIS to load the data in fact tabel but it is showing me this error: The OLE DB provider used by the OLE DB adapter cannot convert between types "DT_DBDATE" and "DT_I4" for "DateId".

163731-image.png

Please help me!

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,816 questions
SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,814 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,460 questions
Accepted answer
  1. ZoeHui-MSFT 33,301 Reputation points
    2022-01-11T06:20:14.543+00:00

    Hi @AVELIU ,

    You are using lookup to match the Date from the dimensional table d_date with the excel source.

    After the Lookup Match Output, you want to save the data to DateId.

    In this situation, you are saving the DT_DBDATE as DT_I4 which will thrown the error.

    You may add the Derived Column before the OLE DB destination to convert the DT_DBDATE to DT_I4.

    Expression: (YEAR(yourcolomnname) * 10000) + (MONTH(yourcolomnname) * 100) + DAY(yourcolomnname)

    163730-image.png

    And then insert the date you need to the DateId column with type int.

    Regards，

    Zoe

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    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 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Martin Cairney 2,241 Reputation points
    2022-01-10T23:00:15.04+00:00

    I suspect that you have an incorrect mapping at the point of load.

    Your Lookup Date should pass in a DT_DBDATE and get the corresponding DT_I4 from the id column in the d_date table. It is the id column that muist then be mapped to the DateID column in the Fact table. My guess is that you still have the date column in the mapping or that you haven't included the id column in the subsequent flow.

    0 comments