Rows affected during import?

[elhaix]

Hi Gang,

I've got an SSIS package that performs a data import. What I'd like is to know how to setup an import package to output a "rows affected" type of report, listing new, updated and deleted records, instead of performing a secondary (programmatic) import, auditing each row.

I'm executing this package through a .Net app, so also getting the report back into the app could be good, although maybe a CSV or Excel report would do just as well.


Thanks.

 

[RiverGuy]

Use the RowCount transformation in the DataFlow task.

 

[MDXer]

You will need to use the rowcount task as river guy pointed out but also design your dataflow in a manner to support the tracking.

You will want to multicast your data out into New, Update and Delete outputs and place the appropriate rowcount in the dataflow.

Do not use the oledb commands to do either your delete or updates. Dump these records to a table and then fire a stored procedure to do the update or delete based upon the output of these flows.

Build an audit system that will write your info to a table that you can use as the source for your report.

 

[elhaix]

Thanks for the info.

MDxer, re: multicasting data, are you saying ti perform new, update, and delete functions separately? The data is coming in as a flat file, so I'll compare the existing data with newly-imported to determine changes.

As I'm still new to SSIS, is there an online tutorial/ref for this example?


Thanks.

 

[MDXer]

The way I have handeled this is with a flag. Make the comparison in a conditional split function that outputs the data based upon the results. I have done this as part of a DW ETL so I have had to apply surrogate key values to and process new and updates through the same transforms with the only difference being how the data enters the destination. So I would add a derived column to my new and and update outputs and set an IsNewFlag. Prior to my destination I would the split the New and updated values again. Deletes can be handled at the first split.

I appologize for saying multicast I was meaning to say COnditional split.

 

[elhaix]

Ok, so earlier you said:
Do not use the oledb commands to do either your delete or updates. Dump these records to a table and then fire a stored procedure to do the update or delete based upon the output of these flows.


I've got an initial import table. I can then do a secondary data import from the initial to the final, and that's where I do the conditional split?