How to do incremental loading of fact table WITHOUT USING OLE DB command

Question

How to do incremental loading of fact table WITHOUT USING OLE DB command

Sudarsan Pandiyan 21

Hi experts,

I have 3 fact table and 8 dim tables.

i have created and load my dim tables. now i want to load fact table(insert dim table key ) and do a incremental load in fact in future.

please help

Olaf Helper 47,436 Reputation points

2020-12-28T08:08:14.363+00:00

Without knowing table structure and your data, no one can give you a solution; so please provide much more information.

4 answers

Your answer

Olaf Helper 47,436 Reputation points

2020-12-28T08:08:14.363+00:00

Without knowing table structure and your data, no one can give you a solution; so please provide much more information.

Answer 1

Vaibhav Chaudhari 38,916 Volunteer Moderator

OLEDB command does the row by row operation and not in bulk so you might face performance issues

Instead, use stored procedure and run them to populate Fact tables

Please don't forget to Accept Answer and Up-vote if the response helped -- Vaibhav

Sudarsan Pandiyan 21 Reputation points

2020-12-26T09:36:13.59+00:00

Hi Vaibhav,

Thanks for your reply. do you have sample sp or can you share the link

Answer 2

Monalv-MSFT 5,926

Hi @Sudarsan Pandiyan ,

Could you please share the version of SQL Server?
We can use the following methods to do incremental loading of table:
a. Using Lookup Transformation , Conditional Split Transformation and Union All Transformation;
b. Using CDC Control task

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.
Hot issues in November--What can I do if my transaction log is full?
Hot issues in November--How to convert Profiler trace into a SQL Server table?

San 121 Reputation points

2020-12-29T03:58:50.597+00:00

Hi @Monalv-MSFT ,

I am using Sql server ver 15.0 and SSDT 2019
Monalv-MSFT 5,926 Reputation points

2020-12-29T09:24:43.577+00:00
Hi @San ,

If we want to use SSIS to do incremental loading of table, please refer to the following links:

Incremental Load: Change Data Capture in SSIS

SSIS Incremental Load with Datetime Columns

Best Regards,
Mona
San 121 Reputation points

2021-01-02T06:04:37.28+00:00

@Monalv-MSFT ,

Thanks for the link. I am tried by using second option (SSIS Incremental Load with Datetime Columns). I am getting an error.
San 121 Reputation points

2021-01-10T13:30:50.5+00:00
@Monalv-MSFT ,

Thanks for sharing the share. I try to use SSIS Incremental Load with Datetime Columns, but for updated and deleted record they are using OLE DB command which i don't want to use.

my requirement is

extract the record from src fact to stg fact (using SSIS)

i want to write a store proc if it exist in fact table then update it else insert it (Tsql)

i want to call that store proc in ssis for loading into fact sales.

CREATE procedure sp_insupt
AS
BEGIN
MERGE INTO dim_item AS TARGET
USING stg_item AS SOURCE
ON (TARGET.ID = SOURCE._ID)
--When records are matched, update the records if there is any change
WHEN MATCHED AND TARGET.LEVEL_CODE <> SOURCE.LEVEL_CODE
THEN UPDATE SET TARGET.LEVEL_CODE = SOURCE.LEVEL_CODE
--When no records are matched, insert the incoming records from source table to target table
WHEN NOT MATCHED BY TARGET
THEN INSERT ( _ID, LEVEL_CODE )
VALUES (SOURCE.ID, ,SOURCE.LEVEL_CODE)
END

Answer 3

Tom Phillips 17,771

Although you can technically do insert/update/delete in SSIS, I would recommend you do it via TSQL in a stored proc.

You can simply "stage" your data into a table, and run a stored proc which "merges" the data into the target tables. It is much simpler.

San 121 Reputation points

2020-12-29T04:00:25.383+00:00

Hi @Tom Phillips ,

Do you have any sample code. or please share the link where I can refer
San 121 Reputation points

2021-01-10T13:32:19.27+00:00
@Tom Phillips ,

Mate due to sensivity of the data . I am unable to share it. Just give me the approach. I try to use SSIS Incremental Load with Datetime Columns,

my requirement is

extract the record from src fact to stg fact (using SSIS)

i want to write a store proc if it exist in fact table then update it else insert it (Tsql)

i want to call that store proc in ssis for loading into fact sales.

Here is store proc getting some error

CREATE procedure sp_insupt
AS
BEGIN
MERGE INTO dim_item AS TARGET
USING stg_item AS SOURCE
ON (TARGET.ID = SOURCE._ID)
--When records are matched, update the records if there is any change
WHEN MATCHED AND TARGET.LEVEL_CODE <> SOURCE.LEVEL_CODE
THEN UPDATE SET TARGET.LEVEL_CODE = SOURCE.LEVEL_CODE
--When no records are matched, insert the incoming records from source table to target table
WHEN NOT MATCHED BY TARGET
THEN INSERT ( _ID, LEVEL_CODE )
VALUES (SOURCE.ID, ,SOURCE.LEVEL_CODE)
END

Answer 4

Deleted

This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Comments have been turned off. Learn more

Share via

How to do incremental loading of fact table WITHOUT USING OLE DB command

4 answers

Your answer