Current recordset does not support updating access 2016

26 Jan

In this example we are simply going to use a Execute SQL Task in SSIS and place the logic that was in the stored procedure directly in the task, so now the logic is embedded in the package.

Nothing fancy going on here, simply truncate table and insert into table select * from…

But wait, nothing stays this simple or never changes right? Now we have a user that decides that they would like to include a new column in v_Employee that provides them the full name of the employee combining the employee’s last name and first name together.

Not a problem and makes complete sense to have this setup in the source to reference and centralize this logic.

For simplicity we are not interested in tracking any sort of changes over time and just want a current view of the Employees.

All we want to do is take the information from one table and completely refresh a data warehouse table that we will use to deliver information to our end-users. Simply delete the data from our destination data warehouse table, select data from the employee source table, and insert the source contents into the destination.

If either the source or target have additional columns added that is okay.

Some additional postings and articles that you might want to look at are below that further expand on the reason to leverage ETL or help explain reasoning and purposes: In working on a business intelligence project a common situation you will find yourself in is having to come up with a cumulative value (running value) calculation.

The other item that I want to include and point out in this example is the use of ‘SELECT * FROM’ and its impact.

In all of these examples we will be leveraging a database view for our source and this is simply used to add an additional layer of abstraction and reference point instead of using the underlying table directly. In the data warehouse we need to load Employee information to reference.

Okay, the stage is set and we are ready to test everything out.

We run the package with the different options: Everything looks great.