Chapter 5

Exploring SQL Server Data Tools

SQL Server Data Tools (SSDT) is a Visual Studio 2010 tool that helps you create, debug, and execute SSIS packages. When you’re a business intelligence developer, it can also help you create reports in SQL Server Reporting Services (SSRS) or design cubes in SQL Server Analysis Services (SSAS). You’ll be using SSDT extensively throughout this book, so it’s important that in this lesson, you learn everything you need to know to make your life easier in this critical environment.

You can open SSDT through the SQL Server 2012 program group. Depending on your PC, SSDT may take some time to open.

“C:Program Files (x86)Microsoft Visual Studio 10.0Common7IDE
devenv.exe” -NOSPLASH

The Solution Explorer

Once you create your project from Lesson 4, you’re ready to begin exploration of the environment. The most important pane, the Solution Explorer, is on the right. The Solution Explorer is where you can find all of your created SQL Server Integration Services (SSIS) packages, shared connection managers, and parameters. As discussed in Lesson 4, a solution is a container that holds a series of projects. Each project holds a myriad of objects for whatever type of project you’re working in. For SSIS, it holds your packages, shared parameters, and shared connections (the latter two are available only in the project deployment model, which is discussed in this lesson shortly in the “Deployment Models” section). Once you create a solution, you can store many projects inside of it. For example, you might have a solution that has your VB.NET application and all the SSIS packages that support that application. In this case, you would probably have two projects: one for VB and another for SSIS.

Figure 5-1

After creating a new project, your Solution Explorer window contains a series of empty folders and a single package in the Packages folder. Figure 5-1 shows you a partially filled Solution Explorer. In this screenshot, you see a solution named Enterprise Data Warehouse with one project, an Integration Services project called Datawarehouse Load. Inside the project, you’ll find the single default package, Package.dtsx.

If you don’t see the solution name in your Solution Explorer, it’s because solutions are hidden when you have only a single project. In this scenario, the solution won’t appear by default. To always show the solution, you can select Tools > Options to open the Visual Studio options pane. Under Projects and Solutions, check Always show solution, as shown in Figure 5-2.

Figure 5-2

If you look into the directory that contains your solution and project files, you can see all the files that are represented in the Solution Explorer window. Some of the base files you might see will have the following extensions:

• .dtsx—An SSIS package
• .ds—A shared data source file
• .sln—A solution file that contains one or more projects
• .dtproj—An SSIS project file
• .params—A shared parameter file
• .conmgr—A shared connection manager

If you copy any file that does not match the .params, .conmgr, or .dtsx extension, it is placed in the Miscellaneous folder. This folder is used to hold any files such as Word documents that describe the installation of the package or requirements documents. Anything you’d like can go into that folder, and it can all potentially be checked into a source control system like SourceSafe with the code.

Deployment Models

In SQL Server 2012, you have two models for developing and deploying packages: package and project deployment models.

• The package deployment model used to be the only deployment model that existed in SQL Server 2005 and 2008 and was where you could deploy only a package at a time to the server. It also had ways of configuring the packages to change properties like connections with XML files or tables.
• With the new project deployment model, you can only deploy the entire project of packages, and packages can be configured by the database administrator (DBA) through parameters.

You can switch back and forth between these models, but the new project deployment model is much more robust with features. You can switch back and forth between the models by right-clicking the project in the Solution Explorer and selecting Convert to Project (or Package) Deployment Model. You learn much more about this functionality in Lessons 52 and 53.

The Properties Window

Figure 5-3

The Properties window (shown in Figure 5-3) is where you can customize almost any item that you have selected. For example, if you select a task in the design pane, you receive a list of properties to configure, such as the task’s name and what query it’s going to use. The view varies widely based on what item you have selected. Figure 5-3 shows the properties of the Execute SQL Task. You can also click the white background of the Control Flow tab to see the package properties in the Properties window. Sometimes, you can see some more advanced properties in the Properties pane than what the task’s editor user interface provides you.

The Toolbox

Figure 5-4

The Toolbox contains all the items that you can use in the particular tab’s design pane at any given point in time. For example, the Control Flow tab has a list of tasks and containers (a partial list is shown in Figure 5-4). This list may grow based on what custom tasks are installed. The list is completely different when you’re in a different tab, such as the Data Flow tab. Many of the core tasks you see in Figure 5-4 are covered in Section 2 of this book in much more detail.

The Toolbox is organized into sections such as Common, Containers, and Other Tasks. These tabs can be collapsed and expanded for usability. As you use the Toolbox, you may want to customize your view by moving items to your favorites by right-clicking a given task or container and selecting Add to Favorites. Also, after you install a custom component, it automatically shows up in your Toolbox. When you select a component like a task from the Toolbox, notice that below the Toolbox pane, an interactive help section appears that enables you to see samples and a short description of the component.

The SSDT Design Environment

The SSDT environment contains two key tabs for designing packages: the Control Flow and Data Flow tabs. Each of these handles different parts of your packages. The Control Flow tab controls the execution of the package and the Data Flow tab handles the movement of data.

The Control Flow tab orchestrates the execution of your package, dictating that one task, such as an FTP Task, should execute ahead of another; for example, an Execute SQL Task. Inside the tab are tasks and containers you can drag over from the Toolbox onto the design pane. Each of those tasks has its own user interface that you can use to configure the task, and you can access it by double-clicking the component.

Each package has only a single Control Flow, but can have many Data Flows. The user interface for the Data Flow task is quite different. Its user interface is the Data Flow tab. In the Data Flow tab, you can configure one or more Data Flow tasks by dragging over sources, transforms, and destinations onto the design pane. Each Control Flow can have any number of Data Flow tasks, each of which results in a new item in the Data Flow tab’s drop-down list of tasks. The Data Flow is essentially where you’re going to configure the movement of your data from nearly any source to nearly any destination.

When you execute a package by right-clicking it in the Solution Explorer and selecting Execute Package, you enter debug mode. Notice a new tab called Progress immediately opens. The Progress tab is where you go to debug when a package has a problem. You can also go to the Output window below to see a textual view of the same Progress tab. Once you stop debug mode by clicking the Stop button or by going to Debug > Stop Debugging, the Progress tab changes to an Execution Results tab, which shows you the last run of a package. Each of those tabs shows you more than the Output window at the bottom, which shows you only critical issues.

One other handy thing you can do from within SSDT is open Server Explorer. Server Explorer enables you to create a connection to a SQL Server database that you can manage just as you would in Management Studio. You can do this by selecting Tools > Connect to Database. Type in the credentials for the database, and then you’re ready to run queries against the database, create stored procedures, or redesign tables, to name just a few things you can do.

Now that you’ve taken a look at the SSDT environment, Lesson 6 covers using the environment to create your first package.