SSIS – Only Inserting Rows That Do Not Exist

I have seen an overwhelming trend that suggests that today’s TSQL/BI developers are very interested in using Microsoft’s BI (Business Intelligence) product, SQL Server Business Intelligence Development Studio aka BIDS.  For those of you who may not know, BIDS is installed when SQL Server is installed.  BIDS is nothing more than a Visual Studios add-in that uses the .Net Framework.   BIDS allows you to do Analysis Services (Cube Design and management),  Reporting Services, and Integration Services.  The BI platform that I will be focusing on today is SSIS (SQL Server Integration Services).  SSIS is used for ETL, which is a acronym for  Extract- Transform-Load.  Essentially SSIS is used to migrate data from many different sources,  including MS Access, Excel, Text Files, Oracle, SQL, AS400 etc, while allowing data transformations between source and destination.  The problem that most import processes have is flexibility.  Some bulk import APIs do not have an easy method to import new data, or data that does not currently exist in a table.  In these cases, the import process has to import all source data, then another command has to be issued to filter and insert the data.  A great example of this is BCP or the Bulk Insert command.  SSIS gives developers an easy and efficient method to insert new data.  This is where I will be focusing my efforts today, in a future post, I will demonstrate how to do an UPSERT (Update existing and Insert new) operation using SSIS.  Okay let’s start by creating a sample table.

--Switch DB context to Tempdb

USE [tempdb]

GO

SET NOCOUNT ON;

GO

--Drop table if exists

IF EXISTS(SELECT 1 FROM sys.tables WHERE NAME = 'SSIS_Import')

BEGIN

    DROP TABLE dbo.SSIS_Import;

END

GO

--Create table

CREATE TABLE dbo.SSIS_Import(

SomeID INT PRIMARY KEY,

SomeInt INT,

SomeChar CHAR(1)

);

GO

--Insert some test data

INSERT INTO dbo.SSIS_Import ([SomeID],[SomeInt],[SomeChar]) VALUES (1,1,'A');

INSERT INTO dbo.SSIS_Import ([SomeID],[SomeInt],[SomeChar]) VALUES (2,5,'B');

INSERT INTO dbo.SSIS_Import ([SomeID],[SomeInt],[SomeChar]) VALUES (3,8,'C');

INSERT INTO dbo.SSIS_Import ([SomeID],[SomeInt],[SomeChar]) VALUES (4,2,'D');

GO

Now that our table exists, I am going to shift gears and start creating the SSIS package. Before creating the package, you should create a Pipe delimited text file on the C: drive, called SSIS_Import.txt.  Below is a sample of my file.

1|100|U

2|200|V

5|300|W

8|400|X

9|500|Y

10|600|Z

Okay now we can start creating our SSIS package.  You should now be looking at a blank canvas, the first objective is to drag a Data Flow Task onto the canvas.  Your canvas should look like below.

Note: I have renamed my data flow task to represent what it is doing.

I will now configure the Data Flow Task.  Double-click the data flow task to open the data flow task designer canvas. Drag a “Flat File Source” to the canvas.  Double-click the Flat File Source and create a new connection.  Your connection should be configured as shown below.

You will see an error message at the bottom on the window, stating the columns are not defined.  All you need to do is click the Advanced tab and make sure the data types and length are correct.  Column 0 and column 1 should be of type “four byte signed integer” and column 2 is a string with a length of 1.   In addition to configuring the data types, you should rename the columns.  You can change the name of a column by typing a new name in the Name property field.  The names should align with the table, so the mappings should look like this:

• Column 0 –> SomeId
• Column 1 –> SomeInt
• Column 2 –> SomeChar

After all configurations, your configuration should look like below.

Once the Flat File Source is configured, click “Ok” and “Ok” again.  The next step is to drag a “Lookup” transformation to the canvas and connect the Source connection to the Lookup transformation via the green precedence constraint.  Double-Click the Lookup transformation to configure it.  The first thing I have to do is change how errors are handled.  By default, if a single value is not found in the source, the entire Lookup component fails.  We will need to make sure non matching rows are redirected to the error output, as shown below.

Next, I will configure the connection.  Click the connection tab and the “New” button again.  The OLE DB connection should point to the database where the SSIS_Import table is.  Your screen should look like below.

Next we have to configure the key columns.  For the sample table the key column is SomeID, which maps to the Column 0, or SomeID if you renamed the column in your flat file connection manager, as shown below.

Note: To create the relationship you will have to drag the source column to the destination column.

Once you have finished configuration of this component, click “Ok” to save the changes.  Your canvas should be look like the screenshot below.

Next drag a OLE DB Destination to the canvas.  Make sure you drag the red arrow (precedence constraint) to the OLE DB Destination, which should cause a dialog box to popup. When it does just click “Ok.”  The destination controls where the source data will be inserted.  Double-click the OLE  DB Destination and make the destination point to the SSIS_Import table, as shown below.

Now click the mappings tab to map the source columns to the destination columns.  I have renamed my source columns, so SSIS will automatically map the columns for me. Click “Ok”, once you have mapped all the columns.

That is it!!! The final canvas will look like the screenshot below. 

All that is left is testing.  Click the debug button (looks like a “Play” button). If you have correctly configured all the steps,  the components will light up green.  If any turn red, something is not configured properly. Once all components light up green, run a simple select statement over the table to make sure it did what is supposed to do.

There you have it a method that takes less than 5 minutes to configure and will migrate new data only.  I hope that you have enjoyed reading this post and can make use of this in your environment. Stay tuned…… my next step is to Update pre-existing rows, using SSIS.  This is commonly known as an UPSERT.  I will show you a few ways to accomplish this task and the performance considerations, for each method.

Until next time… Happy Coding.