SSIS Lesson 3 – Derived Column

In this lesson, we are going to take the first basic step towards building a data warehouse. We are going to create a historical table from the table we built in Lesson 2. If you didn’t do Lesson 2, you can find the SQL needed to build the table at the bottom of this lesson.

If you remember the table we built in Lesson 2, we had a list of students and the work teams they were assigned to. In lesson 2, this list was designed to change every week, and we only wanted to know what the current week data was.  However, now Principal Skinner has asked if we can keep a listing of all the weeks so he can go back and see how many times someone was assigned to one of the work teams.

The table below is our starting table.

What we want to do is copy the table’s contents into a new historical table and mark it somehow so we know what week each kid was on each team. We are going to do this by creating a new column called INSERT_DT which will record the date the data was added to the historical table.

First things first, let’s build our historical table in SQL Server

CREATE TABLE [dbo].[STUDENT_TEAM_HIST](
       [STUDENT_NM] [varchar](50) NULL,
       [STUDENT_AGE] [int] NULL,
       [STUDENT_TEAM] [varchar](50) NULL,
       [INSERT_DT] [datetime] NULL
)

Now go into SSIS and go to your Training Project

Create a new Package, I called mine Lesson_3

Drag a Data Flow object onto your design window and click on it

Now drag an OLE DB Source box over and click on it

Select dbo.Student_Name from the drop down and click OK

Now we are going to use a new object – Derived Column, drag it over and connect the Blue arrow from the bottom of the OLE DB Source box to it

Click on Derived Column: Purple Arrow (far left) – Name your new column, I named mine INSERT_DT

Green Arrow (Middle) : in the Expression column, type GETDATE()

Yellow Arrow (far right) : once you tab out of the Expression column, you will see your Data Type changed itself to be a timestamp

Red Arrow (Bottom) : hit Okay

What we just did was create a new column called INSERT_DT which will use the GETDATE() method to get the current date and time. Next we are going to use this column we just created.

Grab and OLE DB Destination and drag it over. Connect it with the outbound arrow from the Derived Column box

Select the new dbo.STUDENT_TEAM_HIST table we created and next click on Mappings

You will see you now have four columns feeding into your new destination table. If you go back and look at your source table, you will see you still only have 3 columns there. The fourth column is the one we just derived.

Hit okay, now we are ready to run the package

Right click on Package and hit Execute Package

It should run. Notice the little comments telling you how many rows moved.

Go to SQL Server and check your new table. You will see the INSERT_DT

Okay, now to see the historical data in action

Let’s change Micah’s team from Red to Blue in our STUDENT_TEAM table

update [dbo].[STUDENT_TEAM]
set STUDENT_TEAM = 'Blue'
where STUDENT_NM = 'Micah'

select *
from dbo.STUDENT_TEAM

Go back to SSIS, hit the red box at the top to end debugging mode

Right click and execute the package again

Now run your query again. Note the two different teams Micah was on are listed. Along with the timestamps so you can tell when he was on which team.

SQL Query to build table from lesson 2

CREATE TABLE [dbo].[STUDENT_TEAM](
       [STUDENT_NM] [varchar](50) NULL,
       [STUDENT_AGE] [int] NULL,
       [STUDENT_TEAM] [varchar](50) NULL
)

insert into dbo.STUDENT_TEAM
       (STUDENT_NM, STUDENT_AGE, STUDENT_TEAM)
VALUES
       ('Bob', 15, 'Red'),
       ('Claire', 17, 'Blue'),
       ('Chris', 17, 'Blue'),
       ('Candice', 14, 'Red'),
       ('Holly', 16, 'Blue'),
       ('Micah',15,'Red'),
       ('Stephanie', 16, 'Blue'),
       ('Joshua', 14, 'Red'),
       ('Sherrie', 18, 'Blue')