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

[Jcferreira]

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é

 

[TheBugSlayer]

It looks like you could simply insert the result of a UNION ALL query between from table A and table B into table C in an Execute SQL Task.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[jjefferson]

Not sure if TheBugSlayer was implying this as well, but here we would create a stored procedure to merge the A and B data to C and run that from the Execute SQL Task, rather than coding the query directly into the task. Should you find you need to make changes to that later, it is easier to modify the stored procedure than it is to pull the package up in Visual Studio or BIDS to edit the query. Just another option.

But he is right: the Execute SQL Task is the way you want to go next.