This recipe is an overview of SQL Server Data Tools (SSDT), where a user will spend most of his/her time while developing and maintaining SSIS projects.
This version is based on Visual Studio 2010, and the whole structure that supports the process of developing such projects has been significantly improved. Working with SSDT is not only easier for advanced users who require more flexibility, but also for beginners who can enjoy some new and interesting user interfaces to help them take their first steps with SSDT. Previous versions of SSIS used Business Intelligence Development Studio (BIDS) as their development environment.
Open SQL Server Data Tools (SSDT) through the shortcut placed under Microsoft SQL Server 2012 or Open Microsoft Visual Studio 2010 under the Microsoft Visual Studio 2010 Start menu folders.
Once SSDT is open, a start page will be seen by default. The Start Page window contains useful information about the SSDT environment such as recently opened projects, links to create or open an existing project, and is also a useful area with several resources and the latest news to help stay up to date about several Microsoft platforms such as Windows, Web, Cloud, and so on.
Now that SSDT is already opened, let's create a new SSIS project from the Start Page window in order to understand the basic steps as well as the remaining windows placed in the SSIS project example.
Click on New Project… and a Windows dialog will appear.
Under Installed Templates, expand Business Intelligence and click on Integration Services. In the center pane, select Integration Services Project.
Name the project as
R02_Getting Started with SSDT. Name the solution asCh01_Getting Start with SQL Server Integration ServicesinC:\SSISand click on OK. An empty SSIS project will be created using the Project Deployment Model approach (default) with an empty package included.In the Solution Explorer pane , right-click on the SSIS Package folder, and choose Add Existing Package.
In the Add Copy of Existing Package dialog box, set Package location to File System and choose the package path from the file that you saved in the previous recipe from this address:
C:\SSIS\Ch01\Ch01R01_ImportExportWizard.dtsx.
The new package will be added under the SSIS
Packagesfolder, double-click on the package name in Solution Explorer to open it in Package Designer.
Double-click on Preparation SQL Task 1 and the Execute SQL Task Editor dialog will open. Verify the SQL Statement property with a click on the ellipsis button in front of SQL Statement, and then close the editor.
Double-click on Data Flow Task 1 , and you will be redirected to the Data Flow tab, there are three source or destination combinations in Data Flow.
Double-click on the Source-Department component and the OLE DB Source Editor will open, verify the table name there.
Double-click on Destination-Department, and in the OLE DB Destination Editor , verify the connection and table name.
The next recipe will explain the process of creating a new SSIS Package in more detail, and for that reason this recipe will focus on how we could get more value from SSDT to make the development and maintenance easier and faster.
Now that the SSDT is open with an empty package, let's describe some of the windows that you should be familiar with, as shown in the next screenshot:
By default, SSDT creates a new and empty SSIS Package named package.dtsx. A package
is a collection of SSIS objects including connection managers, tasks and components.
Control Flow is the most important tab; it's where a developer "explains" to SSIS what the package will do. The remaining tabs such as Data Flow (see recipe), Parameters (see Chapter 11, Event Handling and Logging), Event Handlers (see Chapter 10, Debugging, Troubleshooting, and Migrating Packages to 2012), the Package Explorer and Progress bar (available just at runtime) are also important and will be described in later recipes.
The Solution Explorer section contains projects and their files.
Each project consists of Project Parameters, Connection Managers, SSIS Packages, and the Miscellaneous folder.
Project Parameters are parameters which are public for all packages in the project. We will discuss parameters in later chapters.
The Connection Managers folder in the Solution Explorer consists of shared connection managers which are shared between all packages in a project.
All SSIS Packages will be listed under the SSIS Packages folder.
The Miscellaneous folder can consist of any other files that are relevant to projects and packages, files such as documentation files, screenshots, and so on.
Properties panel ( 3 )
In this panel, it's possible to read and edit the properties of each selected object in the Design area or Solution Explorer.
SSIS Toolbox ( 4 )
In the SSIS Package, there are tasks and components which will be available depending on the tab selected in the Package design area. When the tab selected is Control Flow, the SSIS Toolbox will be grouped into four areas. The groups of tasks are the Favorites, Common, Containers, and Other Tasks. With these tasks, it's possible to control and inform SSIS about what should be done during execution. An interesting tip is that you can add tasks to the Favorites area anytime you like by right-clicking on each task and selecting Move to Favorites.
Note that the SSIS Toolbox is completely different on the Data Flow tab; we will talk about it in later recipes.
Connection Managers ( 5 )
Connection Managers are connections from the SSIS Package's components to source or destination data providers. There are different types of connection managers, some of them which are much in use are OLE DB Connection manager, Flat File Connection Manager and so on.
Each connection manager can be used in one or more components in the SSIS Package to work with underlying data provider. Some data providers require the installation of special drivers to have connections to their data source.
Each connection manager which is relevant to the current package will be listed in the Connection Manager's pane. Some connections are bold, these are referenced from a shared project's connection manager.
We will discuss more about connections in the next recipes.
Variables Pane ( 6 )
Each task in SSIS Package can send information to other tasks and it is possible by resorting to Variables. Package variables, their data types, their scope, and other properties exist in this pane, which will be described in greater detail in later chapters.