SSRS 2016 Enhanced Report Items

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.
   
   
   



Figure 1 : Empty SSIS package

7. 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

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.
   



Figure 3 : Connection Manager

8. Click OK.
9. 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.
10. 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.
11. Verify that the Package Designer is open for the Package.dtsx package.
12. Right-click in the Connection Managers area at the bottom of the designer, and then select New Flat File Connection.
13. Enter FF_DepartmentList as the Connection Manager Name.
14. To the right of the File name box, click Browse.
15. 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.
16. 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

17. Notice that the OK button is greyed out. You will not be able to click OK until the columns have been successfully defined.
18. 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.
19. Click Advanced on the left side to move to the Advanced page of the dialog box.
20. Click the New button.
21. In the far right section, under Misc, change the Name of the new column to DepartmentName.
22. Repeat this process to create a second column with a name of GroupName. The dialog box should look similar to Figure 5.
    
    
    



Figure 5: Flat File Connection Manager

23. Click OK.
24. Right-click the FF_DepartmentList Connection Manager and select Copy.
25. Right-click in the Connection Managers area and select Paste.
26. If necessary, click on the new FF_DepartmentList 1 connection manager to select it.
27. 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.
    
    
    



Figure 6: Properties

28. Change the file name portion of the ConnectionString property to DepartmentsBackup.txt.
29. 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.