How to Create Your First SSIS Package

dataops-icon-dark-bg

Published: May 22, 2020

A SQL Server Integration Services (SSIS) package includes the necessary components, such as the connection manager, tasks, control flow, data flow, parameters, event handlers, and variables, to execute a specific ETL task. For an introduction to key SSIS functionality, check out the SSIS Basics guide.

To demonstrate how to get started with SQL Server Integration Services (SSIS), we’re going to walk through the steps to create and test a simple SSIS package. In this example, we will create a package that truncates a staging table and loads that same table with the data found in a flat file.

 

Create the SSIS Project

 

First, we need to create the SSIS project in which the package will reside. In SSDT, select File, New, Project to open the New Project window shown in Figure 1. Choose the appropriate SSIS project. Set the name of the project to something meaningful (in this case, Load Staging Tables).

After clicking OK, you’ll see the newly created project in the Solution Explorer window and will probably notice in the SSIS Packages folder that a default package (with the name Package.dtsx) was created automatically. Right-click and rename this package to something meaningful, such as Load Orders Staging.dtsx. (Don’t forget to leave the .dtsx extension in place when renaming.)

create-project

Figure 1: Create a New SSIS Project

Add the Truncate Table Task

 

Since we are loading a volatile staging table in this package, we first need to truncate that table. For this operation, we’ll select Execute SQL Task from the SSIS Toolbox and drag it to the control flow surface. Right-click and select Rename to give this task a descriptive name: SQL – Truncate Orders Staging.

 

 

execute-control-flowFigure 2: Execute SQL Task Added to Control Flow

You might notice that the newly added task shows an error indicator. If you hover your mouse over that task, you’ll see a brief message that there is no connection manager specified. To resolve the error, let’s go ahead and create a connection manager for this task. Open the Execute SQL Task Editor, shown in Figure 3, by double-clicking this task.
execute-task-editor

Figure 3: The Execute SQL Task Editor

 

Do you want to accelerate ETL processes and eliminate many tedious SSIS programming tasks?

 

SentryOne Test icon

Task Factory

Dozens of high-performing SSIS components that help you save time managing ETL tasks.

Learn More

 

Create a New Connection Manager

 

 

 

To create a new connection manager for this task, use the drop-down list next to the Connection item and choose New Connection. This will open another window to allow you to configure a new OLE DB connection manager. Click New, shown in Figure 4, to create this new connection.

Doing so opens another window, shown in Figure 5, with the new connection manager properties. Set the server name and database name for this connection, and, if necessary, change the authentication type (Windows Authentication is always preferred, if available).

connection-managerFigure 4: Create New Connection Manager

Add the Data Flow Task

 

 

Next, find the Data Flow task in the SSIS Toolbox and drag it onto the control flow surface. Right-click and rename the task to DFT – Load Orders Staging.

To ensure that this data flow is run after the completion of the truncation step, we need to use a precedence constraint to establish the order of operations. To do so, click the previously defined SQL task and note the green arrow coming out of it. With your mouse, grab the end of that arrow and drag it over to the Data Flow task, as shown in Figure 7.

Now, double-click the new Data Flow task to jump to the Data Flow tab. Using the SSIS Toolbox, navigate to the Flat File Source (found under the Other Sources folder) and drag it to the empty canvas of the data flow. Rename this source to FFSRC – Orders File, as shown in Figure 8.

 

data-flow

Figure 7: The Data Flow and Precedence Constraint

 

 

 

 

To configure this cleanup operation, double-click the derived column transformation. The Derived Column Transformation Editor that appears looks a bit like the Expression Editor we reviewed earlier, but this one has a grid where you can add one or more transformations.

In this list of transformations, you can opt to add a new column or modify the value of an existing column. For our purposes, we’re going to trim out the whitespace on the existing CustomerID column. To do this, use the dropdown menu under Derived Column and select Replace ‘CustomerID’. In the Expression box, enter TRIM(CustomerID) to remove leading and trailing whitespace from that value, as shown in Figure 12.

derived

Figure 12: Derived Column Transformation Editor

 

As you can probably guess based on the prior steps, the error indicator on this destination means we have some properties that still need to be defined. Open the OLE DB Destination Editor by double-clicking the destination.

In the OLE DB Destination Editor window, shown in Figure 14, you’ll see a list of available OLE DB connections, as well as settings for the table to write to in this data flow. Because there is only one OLE DB connection in this package (the same one we configured when we set up the Execute SQL Task to truncate the staging table), that connection will automatically be selected. The only other required item on this tab is to select the output table from the dropdown list.

ole db destination

Figure 14: OLE DB Destination Editor

Conclusion

Hopefully you’re ready to take what you have learned from this guide and begin working with simple SSIS packages. The following are a few ways you can continue to expand your SSIS knowledge:

  • Download a free copy of SSDT to start creating packages of your own. You can also download SQL Server Developer Edition, which includes the SSIS runtime.
  • Practice, practice, practice! There are numerous data sets available on the internet, and Google even has a search engine that lets you search available data sets. Working through different load types and dealing with issues found in real-world data (e.g., data type issues, dirty data) will accelerate your proficiency with SSIS.
  • Read a book on SSIS. I recommend this book written by the Microsoft team that built SSIS. Although, the book was published in 2012, most of the content is still applicable to the latest versions of SSIS.
  • Dig into specific SSIS topics. There are lots of learning resources online that make it relatively easy to brush up on specific functions in SSIS. I’d humbly suggest starting with the list of SSIS topics I have cataloged on my blog, including posts that I have been compiling since I first started using SSIS. If you’re more into video learning, there are hundreds of SSIS videos available on YouTube.
  • Ask questions. There are experts who routinely answer SSIS (and other data-related) questions on the MSDN forum. This is one of the busiest and most reliable forums for getting expert advice to specific questions.

 

 

Download the in-depth SSIS Basics guide, which covers not only how to create your first SSIS package but also key SSIS functionality.

 

image (43)

Download the PDF

About the Author

yjPWypEd_400x400

Tim Mitchell is a Business Intelligence architect, author, and trainer. He has been building data solutions for over 15 years, specializing in data warehousing, ETL/SSIS, and reporting. He holds a Bachelor’s Degree in Computer Science from Texas A&M at Commerce, and is recognized as a Microsoft Data Platform MVP. Tim is the founder and principal data architect at Tyleris Data Solutions, a consulting firm focused on delivering on-premises and cloud-based solutions on the Microsoft Data Platform.

As an active member of the community, Tim has spoken at international, regional, and local venues, including the PASS Summit, SQLBits, SQL Connections, SQLSaturday events, and various user groups and webcasts. Tim is coauthor of the book “SSIS Design Patterns,” and is a contributing author on the charity book project “MVP Deep Dives 2.” He is a member of the Boulder BI Brain Trust, a Melissa Data MVP, and is active in the North Texas SQL Server Users Group in the Dallas area.

You can visit his website and blog at TimMitchell.net or follow him on Twitter.