SQL Server 2016 Integration Services Tutorial – Part 2 Package Basics

Although the Import/Export Wizard (covered in Part 1 of this tutorial series) is a quick and easy way to move data between two data stores, the real power of SSIS comes with creating projects and packages in SQL Server Data Tools (SSDT).

In this series of tutorials, you'll see how to build and use SSIS packages. In the first tutorial, we looked at The SQL Server Import and Export Wizard and then briefly looked at adding the package created by the wizard to an SSIS project. In this second tutorial, we will walk through the process of creating a package from scratch. Although this tutorial was designed for SQL Server 2016, the majority of the steps will work with any version 2012 or higher.

In this SSIS Tutorial, you will learn:

  • Create an SSIS Project in SSDT
  • Configure a New Package
  • Work with Connection Managers

Downloads needed:

Exercise Files: Including the sample database and a completed project

Set up Required:

  • SQL 2016 Relational Engine (tutorial created on SP1 CU5), SSIS, and appropriate database permissions
  • SQL Server Data Tools for Visual Studio 2015
  • Attach a copy of the SSISSample database which is located in the FilesNeeded folder. 

Files created:

  • ISProject1.sln and supporting files

Understanding Projects

Starting with SQL Server 2012, Microsoft changed the paradigm surrounding SSIS. In previous versions, although you created a project to store the packages, each individual package was deployed on its own. You could pass values for parameters from one package to another, but you could not share the same parameter across packages.

Additionally, each package maintained its own connection managers. This package centric paradigm caused a lot of duplication of effort and made managing complex projects with many packages much harder to maintain post deployment.

Starting with SSIS 2012, the project deployment mode was introduced. With project level deployment, you have the ability to use a shared parameter across off of the packages in a project. You can also share connection managers across the project. Along with these changes came the requirement to deploy the entire project, rather than single packages.

Starting with SSIS 2016, in addition to deploying an entire project, you can also re-deploy a single package from within the project, or add a stand-alone package to an existing project.

Creating a Package

The Import and Export Wizard is easy to use, but it only taps a small part of the functionality of SSIS. To really appreciate the full power of SSIS, you'll need to use SQL Server Data Tools (SSDT) to build an SSIS package. A package is a collection of SSIS objects including:

  • Connection Managers, which define the connections to your data and/or servers.
  • A Control flow, which includes tasks and containers that execute when the package runs. You can organize tasks in sequences and in loops.
  • Precedence constraints, in the control flow to determine when and if a task or container is executed.
  • Data flows, which include the sources and destinations that extract and load data, the transformations that modify and extend data, and the pipelines that link sources, transformations, and destinations.
  • Event handlers, which are workflows that run in response to the events raised by a package, task, or container.

You'll see how to build each of these components of a package this series of tutorials, but first, let's launch SSDT and create a new SSIS package.

Try It!

To create a new SSIS project and package, follow these steps:

  1. Launch SQL Server Data Tools.
  2. Select File >? New ?> Project.
  3. On the left under the Installed ?> Templates > ?Business Intelligence, select Integration Services.
  4. In the center area, select the Integration Services Project template.
  5. For the Location field, select a convenient location that you will remember.
  6. Name the new project ISProject1, and then click OK. A package, project, and solution are all created and visible in the Solution Explorer. The program automatically opens the Package Designer for the empty package that was created as shown in Figure 1.




  7. Figure 1 : Empty SSIS package

  8. If Solution Explorer does not automatically open, click View >? Solution Explorer.

Working with Connection Managers

SSIS uses connection managers to integrate different data sources into your packages. SSIS includes a wide variety of different connection manager types that allow you to move data from place to place. The majority of the tasks in SSIS require a connection manager to work.

Table 1 lists some of the connection manager types that ship with the product. Additional connection manager types are available for download through Microsoft and several partners. Additionally, you can develop your own custom connection manager types.

Table 1 : Available Connection Managers

Connection Manager

Handles

ADO

Connecting to ADO objects such as a Recordset.

ADO.NET

Connecting to data sources through an ADO.NET provider.

CACHE

Connecting to a cache either in memory or in a file

DQS

Connecting to Data Quality Services servers and databases.

MSOLAP100

Connecting to an Analysis Services database or cube.

EXCEL

Connecting to an Excel worksheet.

FILE

Connecting to a file or folder.

FLATFILE

Connecting to delimited or fixed width flat files.

FTP

Connecting to an FTP data source.

HTTP

Connecting to an HTTP data source.

MSMQ

Connecting to a Microsoft Message Queue.

MULTIFILE

Connecting to a set of files, such as all text files on a particular hard drive.

MULTIFLATFILE

Connecting to a set of flat files.

ODBC

Connecting to an ODBC data source.

OLEDB

Connecting to an OLE DB data source.

SMOSever

Connecting to a server via SMO (Server Management Objects.)

SMTP

Connecting to a Simple Mail Transfer Protocol server.

SQLMobile

Connecting to a SQL Server Mobile database.

WMI

Connecting to Windows Management Instrumentation data.


Starting with SQL 2012, you can define both project level and package specific connection managers. If more than one package in a project will utilize a particular connection manager, it is beneficial to create it as a project level connection manager to be shared among the different projects. To create a project level connection manager, right-click the Connection Managers folder in Solution Explorer, select the appropriate connection manager type, and then provide the required information. Each Connection Manager has its own custom configuration dialog box with specific options that you need to configure.

To create a connection manager for a single project, you right-click anywhere in the Connection Managers area at the bottom of the Package Designer and then choose the appropriate connection manager type from the shortcut menu as shown in Figure 2. If the type of connection manager you want to create in not available on this menu, click New Connection, and then proceed as you would with a project level connection manager.



Figure 2 : Connection Manager Shortcut Menu

Try It!

To add some connection managers to your project and package, follow these steps:

Note: If you did not complete the setup at the beginning of the tutorial, you will need to attach the SSISSample database mdf file provided in the files for this tutorial.

  1. If necessary, open the ISProject1 project that you created earlier in this tutorial.
  2. In Solution Explorer, right-click in the Connection Managers folder, and then select New Connection Manager.
  3. In the Add SSIS Connection Manager dialog box, select OLEDB, and then click Add.
  4. If you have previously created connections on this computer, the will appear under Data connections. Click New to create a new data connection.
  5. In the Connection Manager dialog box, select the Native OLE DB\SQL Server Native Client 11.0 provider.
  6. Type your test server name and provide the appropriate authentication information.
  7. Select the SSISSample database.
    Note: If you cannot see the drop-down arrow at the right of the Select or enter a database name box, as shown in Figure 3, make the dialog box bigger. This will cause the arrow to reappear so that you can select the SSISSample database.


  8. Figure 3 : Connection Manager

  9. Click OK.
  10. In the Configure OLE DB Connection Manager dialog box, verify that you new data connection to the SSISSample database is selected, and then click OK.
  11. Notice the new connection manager object's name. It typically follows the format of servername.databasename.conmgr. The file extension is how SSDT knows how to handle the xml file created to support this object. You can modify the first parts of the name, but do not change the extension.
  12. Verify that the Package Designer is open for the Package.dtsx package.
  13. Right-click in the Connection Managers area at the bottom of the designer, and then select New Flat File Connection.
  14. Enter FF_DepartmentList as the Connection Manager Name.
  15. To the right of the File name box, click Browse.
  16. In the Open dialog box, browse to your Documents folder, and then type Departments.txt as the File Name, and then click Open. This will return you to the Flat File Connection Manager Editor.
    Note: If Windows is hiding extensions, you may end up with a file named Departments.txt.txt.
  17. Verify that the Column Names in the first data row checkbox is selected. Review the other options. If your department information had commas in the data, you could add one double quote (") to the Text qualifier box to stop data commas from acting like delimiters when you open the data. Figure 4 shows the completed General page of the dialog box.




    Figure 4: Defining a Flat File Connection Manager

  18. Notice that the OK button is greyed out. You will not be able to click OK until the columns have been successfully defined.
  19. Click Columns on the left side. This will change the focus to the Columns page and create the column definition based on any data in the pipeline. Since you have not yet defined a data source to create a pipeline, you must manually create the column definitions.
  20. Click Advanced on the left side to move to the Advanced page of the dialog box.
  21. Click the New button.
  22. In the far right section, under Misc, change the Name of the new column to DepartmentName.
  23. Repeat this process to create a second column with a name of GroupName. The dialog box should look similar to Figure 5.




  24. Figure 5: Flat File Connection Manager

  25. Click OK.
  26. Right-click the FF_DepartmentList Connection Manager and select Copy.
  27. Right-click in the Connection Managers area and select Paste.
  28. If necessary, click on the new FF_DepartmentList 1 connection manager to select it.
  29. Use the Properties Window to change properties of the new connection. Change the Name property to FF_DepartmentListBackup. The Name property can be found in the Misc section as shown in Figure 6.




  30. Figure 6: Properties

  31. Change the file name portion of the ConnectionString property to DepartmentsBackup.txt.
  32. Save your whole project. If you continue with the tutorials, you will continue to use this project.

Figure 7 shows the SSIS package with the three Connection Managers defined. Notice that SSIS indicates the project connection managers.



Figure 7: An SSIS package with two Connection Managers

In this tutorial you created a new SSIS project and created several connection managers. In the next tutorial, you will learn about control flow tasks and precedence constraints.

In-Depth SSIS Training

For in-depth SSIS training, click here to view all of Accelebrate's SSIS training courses for you and your staff.



Contact Us:

Accelebrate’s training classes are available for private groups of 3 or more people at your site or online anywhere worldwide.

Don't settle for a "one size fits all" public class! Have Accelebrate deliver exactly the training you want, privately at your site or online, for less than the cost of a public class.

For pricing and to learn more, please contact us.

Contact Us Train For Us

Toll-free in US/Canada:
877 849 1850
International:
+1 678 648 3113

Toll-free in US/Canada:
866 566 1228
International:
+1 404 420 2491

925B Peachtree Street, NE
PMB 378
Atlanta, GA 30309-3918
USA

Subscribe to our Newsletter:

Never miss the latest news and information from Accelebrate:

Microsoft Gold Partner

Please see our complete list of
Microsoft Official Courses

Recent Training Locations

Alabama

Huntsville

Montgomery

Birmingham

Alaska

Anchorage

Arizona

Phoenix

Tucson

Arkansas

Fayetteville

Little Rock

California

San Francisco

Oakland

San Jose

Orange County

Los Angeles

Sacramento

San Diego

Colorado

Denver

Boulder

Colorado Springs

Connecticut

Hartford

DC

Washington

Florida

Fort Lauderdale

Miami

Jacksonville

Orlando

Saint Petersburg

Tampa

Georgia

Atlanta

Augusta

Savannah

Idaho

Boise

Illinois

Chicago

Indiana

Indianapolis

Iowa

Ceder Rapids

Des Moines

Kansas

Wichita

Kentucky

Lexington

Louisville

Louisiana

Baton Rouge

New Orleans

Maine

Portland

Maryland

Annapolis

Baltimore

Hagerstown

Frederick

Massachusetts

Springfield

Boston

Cambridge

Michigan

Ann Arbor

Detroit

Grand Rapids

Minnesota

Saint Paul

Minneapolis

Mississippi

Jackson

Missouri

Kansas City

St. Louis

Nebraska

Lincoln

Omaha

Nevada

Reno

Las Vegas

New Jersey

Princeton

New Mexico

Albuquerque

New York

Buffalo

Albany

White Plains

New York City

North Carolina

Charlotte

Durham

Raleigh

Ohio

Canton

Akron

Cincinnati

Cleveland

Columbus

Dayton

Oklahoma

Tulsa

Oklahoma City

Oregon

Portland

Pennsylvania

Pittsburgh

Philadelphia

Rhode Island

Providence

South Carolina

Columbia

Charleston

Spartanburg

Greenville

Tennessee

Memphis

Nashville

Knoxville

Texas

Dallas

El Paso

Houston

San Antonio

Austin

Utah

Salt Lake City

Virginia

Richmond

Alexandria

Arlington

Washington

Tacoma

Seattle

West Virginia

Charleston

Wisconsin

Madison

Milwaukee

Alberta

Edmonton

Calgary

British Columbia

Vancouver

Nova Scotia

Halifax

Ontario

Ottawa

Toronto

Quebec

Montreal

Puerto Rico

San Juan

© 2013-2019 Accelebrate, Inc. All Rights Reserved. All trademarks are owned by their respective owners.
This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.