SQL Server: Linked Servers

SQL Server Linked Servers feature allows you to query data from other databases (even non SQL Server databases) from within SQL Server.

While I believe large data transfer jobs are best handled using an ETL solution like SSIS, sometimes all you want is a just a couple hundred rows of data from another database and setting up an ETL jobs might be overkill.

In this example, I am going to set up a link to a Teradata Data Warehouse.  This will allow me to query data from the warehouse and use it in my SQL Server environment.

(Note that you can connect to any database, such as another SQL Server or Oracle. I am just using Teradata as an example)

First go into SSMS and connect to your SQL Server

Now, in Object Explorer go to Server Objects -> Linked Servers


Right click on Linked Servers and select New Linked Server…


For a Teradata connection, I am choosing Microsoft OLE DB Provider for ODBC Drivers

(on a side note, you will need to have the ODBC driver for Teradata installed for this to work)


Linked Server = just whatever name you want to give your linked server – I chose EDW (short for Enterprise Data Warehouse)

Product Name = again free text, I just wrote Teradata to let people know what kind of product you are connecting too.

Data Souce: This is the server name you are trying to connect to


Next, click Security tab.

I want to use my own credentials to connect to Teradata so I clicked Be Made Using This Security Context: ( the green arrow)

Type in your Username and password. Click Ok


Now go to create a new query in SSMS (SQL Server Management Studio)

We will be using a function called OPENQUERY. The syntax is as follows




We can use the data returned from Teradata in SQL Server by simply putting it in a table or a temp table as shown below


We can also pass variables to our Teradata query. My preferred method is simply using a stored procedure.


       @USER VARCHAR(8) = NULL

declare @query varchar(8000) = 'Select * from OPENQUERY(EDW, ''SELECT USER_NM, LAST_NM, FIRST_NM FROM EMPLOYEE_TABLE WHERE USER_NM =  ''''' + @USER + ''''''')' 


exec [LINKED_SERVER_QUERY] @USER = 'blarson'

SSIS Lesson 3 – Derived Column

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

       [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 *


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

       [STUDENT_NM] [varchar](50) NULL,
       [STUDENT_AGE] [int] NULL,
       [STUDENT_TEAM] [varchar](50) NULL

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

SSIS: Lesson 2 Import data from CSV into database

SSIS: Lesson 2

Import data from CSV into database

In the first lesson we exported data from a table in our SQL Server database to a CSV file. Today we are going to do just the opposite, we are going to bring data from a CSV file into our database.

I like to try to provide close to real world scenarios when I make my tutorials. Obviously in the simpler intro lessons, that can be difficult. That being said, for this lesson, imagine you manage a database for a school. Every week 9 students are selected to be on either the Red Team (Hall Safety Monitors) or the Blue Team (Lunch Room Aids). Every Friday, the Vice Principal Smith picks the student names at random and puts them into a CSV file that is emailed out to all the teachers. You point out that if you could just get the names into your database, you could put the information on the school’s main Intranet page, so the teachers don’t need to download and open the same CSV file 300 times a week.

So our task is to upload the new weekly CSV file into the database every Friday at the end of the school day.

Here is this week’s CSV file:


First, before heading into SSIS, we need to create a destination table in our database for the CSV file. Here is the file we want to import into our database, you’ll note it has three columns, two columns are strings and one is an integer:


So go into SSMS (SQL Server Management Studio) and run the following query to create the table:

 STUDENT_NM varchar(50),
 STUDENT_TEAM varchar(50))

This code will create an empty table with 3 columns named STUDENT_NM, STUDENT_AGE, STUDENT_TEAM.

Now you can minimize SSMS and open up Visual Studios Data Tools

Once open, go into your Training Project and create a new SSIS Package. I’m naming this one Lesson_2


Now grab an Execute SQL Task from the SSIS Toolbox and drag it to the Design window.

Notice the red arrow at the bottom, there is already a Connection set up for the Sandbox database. This is because when we made this connection in Lesson_1, we made it by clicking on the Connection Managers in the Solution Explorer window.



This creates a (Project) connection – one that can be seen in every package. If you only want your connection to exist inside a single package (this becomes important as the number of packages you have grows, otherwise you’d have thousands of connections in Solution Explorer) – you can right click anywhere inside the Connection Managers box at bottom of the design window.


But this particular package will work fine just using the existing connection, we can move on without creating a new one.

Double Click on your Execute SQL Task icon in the design window.

In the window that pops up, you can rename your Task, provide a description of the task if you would like. This does come in handy when you have to go back to old package. It makes it easier to understand what is going on.

Down at the green arrow in the picture, you’ll have to selection a Connection from the drop down. Luckily were currently only have one to choose from.


Next go to SQL Statement and click on the little ‘…’ box that appears in the right corner.


In the pop up window, this is where you enter your SQL Query. In this case were going to delete all data from table in this box. The reason for this step is so that last week’s students won’t still be in the table on Monday morning. We only want the current students in our database.


Now click Okay on this window and click OKAY again to get back to the Design Window

Next drag over a Data Flow Task. Connect the Create Table task by dragging the arrow over from the Create Table task. Note the direction of the arrow, this is the order in which tasks will run.


Now open Data Flow Task. In the SSIS Toolbox, go to Other Sources and drag a Flat File Source over to the Design Window.

Double click on your new Flat File Source box and a new window will open. Click on New… next to Flat File Connection Manager


On the new window, click Browse


Browse to your CSV File. I created a folder called SSIS Training to make it easy to store all my files.


If you click on Columns in the left window, you can see what the CSV file contains.


Finally, before existing this window, Click back on General and make sure Column names in the first data row is checked.


Now go back to the Design window and drag an OLE DB Destination box onto the design window. Connect the blue arrow from the Flat File Source to the OLE DB Destination


Now double click on OLE DB Destination

Select STUDENT_TEAM from Name of table or the view


Now Click on Mappings


Everything should line up nicely since we named the table columns to match the CSV file.


Now click okay to go back to the Design Window, Click on Control Flow in the upper left to get back to the main page of the package


Right click the package and click Execute Package


If all goes well, you should end up with two green checks


If this happens, check to make sure your CSV file is not currently open, that can cause errors. If so, close the file and try executing the package again


Finally, check the table in your SQL Server to see if it populated


If you want to see how it would work the next week, go into the CSV file and change the data. When you run the package again, the names in the SQL table will change too.


SQL: Check if table exists

Copy, paste and run this code in your SQL Server Management Studio to build the database and table we are using in this exercise:

create database sandbox;

use sandbox;
CREATE TABLE employee_id (
        emp_nm varchar(30) not null,
        emp_id varchar(8),
        b_emp_id varchar(8),
        PRIMARY KEY(emp_nm) );        

To check if a table exists in SQL Server, you can use the INFORMATION_SCHEMA.TABLES table.

Running the following code, produces the results below:


Select *


You can use this table with an IF THEN clause do determine how your query responds whether or not a table exists.

           WHERE TABLE_NAME = 'employee_id')
  PRINT 'Yes'


           WHERE TABLE_NAME = 'employee_ids')
  PRINT 'Yes'


  PRINT 'No'


One of the more common uses I find for this when I need to create a table in a script. I want to make sure a table with same name doesn’t already exist, or my query will fail. So I write a query like the one below.

           WHERE TABLE_NAME = 'employee_id')
  drop table employee_id


  print 'Table does not exist'
 CREATE TABLE employee_id (
        emp_nm nvarchar(30) not null,
             emp_id nvarchar(8),
             b_emp_id nvarchar(8)
             PRIMARY KEY(emp_nm) );

INSERT INTO employee_id
       (emp_nm, emp_id)
       ('Bob', 'A1234567'),
       ('Lisa', 'A1234568')

INSERT INTO employee_id
       (emp_nm, b_emp_id)
       ('Priyanka', 'B1234567');

SQL: Coalesce

Coalesce is a simple but useful SQL function I use quite often. It returns the first non-null value in a list of values.

A common real world application for this function is when you are trying to join data from multiple sources. If you work for a company known for acquiring other companies, your HR tables are bound to be full of all kinds of inconsistencies.

In this example below, you will see a snip-it of an HR table created by merging tables from two companies. The first two employees have an emp_id and the last one has a b_emp_id. The last one, Priyanka, is an employee from a newly acquired company, so her emp_id is from the HR table of the newly acquired company.


So if you just want to merge the 2 emp_id columns into one, you can use the coalesce() function.

The syntax for coalesce is: coalesce (col1,col2,….)  as alias

Coalesce takes the first non-null. In the example below, we check emp_id first, if it has a value, we use that. If emp_id is null, we look for the value in b_emp_id.

select emp_nm,
coalesce (emp_id, b_emp_id) as emp_id
from employee_id


If you want to try this for yourself, here is the code to build the table out for you.

CREATE TABLE employee_id (
        emp_nm nvarchar(30) not null,
             emp_id nvarchar(8),
             b_emp_id nvarchar(8)
             PRIMARY KEY(emp_nm) );
INSERT INTO employee_id
       (emp_nm, emp_id)
       ('Bob', 'A1234567'),
       ('Lisa', 'A1234568')
INSERT INTO employee_id
       (emp_nm, b_emp_id)
       ('Priyanka', 'B1234567');


SSIS Tutorial: Introduction

SSIS: Introduction

SQL Server Integration Services is the ETL tool for the Microsoft SQL Server platform. SSIS allows you to take data from various sources (from Excel files, to text files, to other databases, etc), and bring it all together.

If you are new to concept of ETL, SSIS is great place to start. Click here to learn about ETL

If you are well versed in another ETL platform, SSIS is a relatively easy system to get up to speed on.

SSIS comes as part of SQL Server Data Tools, which you should be able to install with your SQL Server installation software. You will need SQL Server Standard, Developer or above editions to run SQL Server Data Tools. SQL Server Express does not support Data Tools.

For some unknown reason, once it is installed, you will not find a program called SSIS. Maybe the engineers at Microsoft think this is funny, but in order to run SSIS you will need to look for the following program instead.


I’m using 2015, but for most of what I am doing here, any version should be compatible.

When you launch data tools, you will notice it run in Visual Studios


To start an SSIS job, you will either need to open an existing project, or create a new one. In this example, I will create a new one.

File ->New -> Project  (or Ctrl+Shift+N)

Inside the Business Intelligence Templates, select Integration Services. I always just select Integrations Service Project, I’m not a big fan of the Wizard


Next step: Name your project


Now you are in SSIS. Here are the 3 main windows you will be starting with.

From right to left:

SSIS Toolbox


Package Designer


Solution Explorer



Inside solutions, packages are the collections of jobs or scripts found inside a project. It is inside the package that you will build out your ETL job.

For our first lesson, we are just going to build a simple package. Your new solution should have opened with a new package when it opened. If it is, right click on the green arrow to rename it, if not, right click on the red arrow to create a new package.



I renamed my package First_Package, you can name your package whatever you choose.

This first package will simply just display a pop up message. In the SSIS Toolbox, go to Script Task and drag it into the package designer window.



Double click on the Script Task Box in the Design window

Note in my example, I have C# set as the scripting language. The other default option is Visual Basic. If you are more comfortable with that, feel free to use it. I prefer C# mainly because I spent more time working with it.

You don’t need to know any C# for this tutorial. This is literally a single line of code assignment. I will cover more C# in the future.

Click Edit Script… to continue


Note, this step can take a minute or so for the script editor to appear. Don’t panic if your computer appears locked up.

Once the script editor opens, don’t panic by all the code you see. Luckily Microsoft has done most of the ground work for us. We only need to scroll down until you see:

public void Main()

Now place your cursor below //TODO: Add your code here

The code you need to type for this script is:

MessageBox.Show(“This is my first SSIS Package”);


Now I know you will be looking for a save button. But again, our friends at Microsoft might have been drinking when they coded this. Instead, just click the upper right X to close out the whole window –

I know – why would they do it that way? how much effort would a save and close button have cost them? I don’t know. It just is what it is. Just click the X and move on with your life.


Now click the OK button – again I guess Save was too much to type


Now right click on your package (green arrow) and click Execute Package


Your message will pop up in a Message box window


Click OK on the messagebox and Click the red square to end the package execution.


Congrats, you have just built and executed your first SSIS Package.