Join several records from diferent tables of a staging area in one table on the DW

  • Hi,

    I´m new using SSIS so I need help - thanks!

    I´ve too tables A and B, and I need to create a C table with information of one of the tables.

    Table A query

    SELECT [PRODUCT_CODE]

    ,[DESCRIPTION]

    ,[PRODUCT_GROUP_ID]

    ,[SEASON]

    FROM [DATA].[D_PRODUCT]

    Table B query

    SELECT [PRODUCT_GROUP_ID]

    ,[LONG_DESCRIPTION]

    FROM [DATA].[D_PRODUCT_GROUP]

    Table C query

    SELECT P.[PRODUCT_CODE], P.[DESCRIPTION], P.[PRODUCT_GROUP_ID], G.[DESCRIPTION] DESCPROD, P.[PRODUCT_GENDER], P.[SEASON]

    FROM [DATA].[D_PRODUCT] P, [DATA].[D_PRODUCT_GROUP] G

    WHERE P.[PRODUCT_GROUP_ID] = G.[PRODUCT_GROUP_ID]

    I need your help to learn how to implement this in SSIS?

    The information off the A table is in a Flat File A.

    The information off the B table is in a Flat File B.

    In SSIS:

    - I already read the A ff and put the data into a A table - in the satging area DB,

    - I already read the B ff and put the data into a B table - in the satging area DB,

    Now, what do I´ve to do to put the information I need in one C table in the DW DB?

    Thank you.

    José

  • You can either execute the SQL statement you provided in an Execute SQL Task, but put an INSERT INTO before it of course 🙂

    Or you could use the SQL statement in an OLE DB source in a dataflow and write the results to table C using an OLE DB Destination with fast load option.

    Another alternative that doesn't use the staging database is reading the two flat files A and B in a data flow, sorting them and do a MERGE JOIN on PRODUCT_GROUP_ID and writing the result to table C. The sort is necessary as it is a prerequisite for the MERGE JOIN. If your files are very large, I would choose for the staging area.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply