SSIS: Slowly Changing Dimensions (SCD Type 2)

In SQL Server Data Tools (SSDT) there is an item to be found in the toolbox that can create a whole workflow of a table historization with the help of a wizard.

Before we start, a small reminder of the different types of Slowly Changing Dimensions (SCD). I will only cover the first 3 here:

SCD Type 0: You retain the original values as they are by time of insertion

SCD Type 1: Values are overwritten. There is no history here. You simply always store the most recent value

SCD Type 2: Each time the value changes, you add an additional row in the table. So basically you have multiple entries for one natural key but multiple surrogate keys

Now, let’s move to SSDT and open a new SSIS project.

In the Data Flow tab we will need a OLE DB Source and this be connected to the item Slowly Changing Dimensions

Now click through the wizard and define a target server and table and what will be the business key

In the next step we need to define the Change Type. The wizard offers three types – I will map them to the SCD types I described above:
Fixed Attribute -> SCD Type 0
Changing Attribute -> SCD Type 1
Historical Attribute -> SCD Type 2
Since I want to historize the data, I will select Historical attribute

In the next part, we will therefor also need a start and end date:

 

I defined 2 variables in my package, whereas I will use the End variable here.

Unselect the Enable button on the Inferred Dimension members and go to the next page.

Click on finish and have a look at the new output

All we need to do now is add an OLE DB Destination item where the values will be inserted.

You will need to map the values in the OLE DB Destination item 

Alright, we’re almost there. Let’s update some values on the current table.

 

update stg.Artist

set Artist = '3 Deep'

where artistid = 9

update stg.Artist

set artist = '4 Low'

where artistid = 10

Now we need to run the SSIS package we just created.

Output:

As you can see, SourceArtistID (the natural key – AristID in the source table) 9 & 10 can now be found with 2 inserts each showing start and end dates.

In addition, we also have 3 new entries with a correct timestamp by using the start parameter.

SSIS: Automated Job Scheduling of SSIS Packages with SQL Server Agent

In my last post I described how to deploy and execute a SSIS package from SQL Server Management Studio. In this post I will reuse the same package, but will execute it automated by creating a Job schedule via SQL Server Agent.
This is useful for tasks that need to be run daily or recurringly. A typical example is the daily load of a Data Warehouse. These loads run usually after midnight on a daily basis. So running it manally every night is neither handy nor healthy :)

First step is to create a new Job in SSMS. Go to the SQL Server Agent folder, right click and select New Folder

In the General tab you define the name of the Job and then browse to the Steps tab.
Click on new and fill in the fields as described below:

Now we need to define a time and how often the Job should be executed.
There is a drop down for selection in the Schedule Type region. I will go for Recurring since I want to schedule it on a daily basis. This will be defined in the Frequency region under the Occurs drop down. You can select between Daily/Weekly/Monthly.

After the package was executed, you can check if it was succesful or not by clicking on View Histoy.

You need to ensure that the SQL Server Agent account which is set, has the rights to schedule the package. Otherwise you may end with a not succesful schedule.

SSIS: Create SSIS Catalog and deploy SSIS Package

This post will focus on how to deploy your dtsx package to SSMS and how you can execute the project from there.

First of all you need to ensure you have already created the SSISDB catalog.

Go to the object explorer and see if you can find a folder under Integration Services Catalogs. If not, simply right click on the mentioned folder and click on Create Catalog.

After having created the SSISDB catalog right click on it and select Create Folder and give it an appropriate name. Open the toggle and you should find 2 more folders (Projects & Enviornments) assigned to it.

Now you can either right click on Projects and import the dtsx file from SSMS or deploy the project from Visual Studio (SQL Server Data Tools). I will go for the latter.

OK, so go to your SSIS project in SSDT and right click in the solution explorer on the top folder and click Deploy.

Select server and for the path, the SSISDB folder we just created.

Click through and once you reached the Review part simply click the Deploy button.

Now should you see your dtsx package in SSMS under the Projects folder.

The package is now ready to be executed on demand. All you need to do is right click on the dtsx file and click Execute

Please check the video above to get a more precise picture.  

SSIS: MERGE JOIN FOR INSERT AND UPDATE (UPSERT)

Today I would like to present a SSIS package that will handle the insert and update task within the target table.

Let's start from the beginning...
First of all we can go right to the Data Flow tab.

We will need 2 OLE DB Source items from the SSIS Toolbox. One will be used to capture the source table, one for the destination table. In a second step, we will need to sort the primary key before we join the 2 tables.
The first steps can actually be replaced by an OLE DB Command item. Actually later is even recommeded since the Sort item is quite expensive. Since I have only a very small dataset and due to demonstration reasons, I will go for this option now.
Next item we need is Merge Join. Since I know the full set is captured in source and the destination table is missing the delta load, I will select Left Outer Join here. If it's vice versa, you will need to go for an Full Outer Join.
In the next step, we need a Conditional Split item from the toolbox. Here we will distinguish between records that are missing in the target table, and records that have been updated in source, but not yet in the target table.

The new records can be simply loaded into the destination table by using the OLE DB Destination item.
For the updated rows, we will need a Stored Procedure that will map the new rows and update them in target.

In a final step we will use an OLE DB Command to call the procedure and handover the parameters.

Now we will have the full set in both tables. Please check the video above to get a more precise picture.

T-SQL: Running Totals

I'm demonstrating here 2 different approaches how to query running totals. The first one is based on a self join and aggregates the values whereas the second query uses ROWS UNBOUNDED PRECEDING.

Please leave feedback if this was helpful. Thanks!

T-SQL: MERGE STATEMENT

The Merge Statement can be very handy within an ETL process. Please see the video to understand the syntax and options it delivers.


Please leave feedback if this was helpful. Thanks!

SSAS: Build a Cube and access data in Excel

This video demonstrates how to build a cube in Microsoft SSAS and later on access the cube data from Excel.


Please leave feedback if this was helpful. Thanks!