SSIS truncate tables and process cubes

What can happen if we run a package multiple times? Well, for each time we run it we will create a new set of records in the destination tables, adding to the already existing records.

This is not what we want. We want just one set of the data: the latest!

So, before we transfer the latest set of data to the destination tables, we must ensure that they are emptied first.

Let us move back to the Control Flow page of the SSIS package, where we will add an Execute SQL Task item.

Rename the item to Empty Sales Tables and then double click it to define it. The connection must point to the stage database because it is the tables in there we would like to empty.

Next, click the button in the SQLStatement field to enter the SQL Query that we would like to execute. The truncate statement removes the content of the table without removing the table itself.

Click OK twice to complete the definition of the Execute SQL Task item.

Connect the two Control Flow items so that the Empty Sales Tables item is executed before the Transfer Sales Tables item.

In this way tables are emptied before new data are transferred.

Last, but not least, the updated data that in this way have been transferred to the destination database, the Stage database, must of course also be reflected in the Multidimensional database, our Sales cube.

In other words, we will need to process the Multidimensional database every single time the data have been updated.

Go to the toolbox and select an Analysis Service Processing Task item.

This Processing Task also requires a Connection Manager to be able to know which multidimensional database needs to be processed.

So, we will need to add an Analysis Services Connection to our list of Connection Managers.

The new Connection Manager can easily be created as a connection to an Analysis Services project in this solution.

Once we have done that, we can double click the Analysis Services Processing Task to start defining it.

Add an Object to the object list. The check mark indicates that we want to process the full project.

Remember that we still have a problem with some missing primary keys in our Item dimension table.

These missing primary keys will cause the processing to stop in the Analysis Services Processing task – similar to what we have seen when manually processing the cube. In order to handle this, we must also here change the behaviour for dimension key errors. They should be ignored.

The completed Control Flow should now look something like this:

Press the F5 key to start debugging, i.e. to execute, the package. Notice that each item and each object in the SSIS package will have an animated yellow icon while they are being executed and later a green check mark when they are completed successfully.

In case of failure, the item or the object will be marked by a red “X”.

But we do not want to rely on this semi-automatic data migration method. Instead of executing the package manually every time, we should be able to schedule the package to be executed at fixed intervals.