The first change that you notice as you create a new Integration Services project is that Business Intelligence Development Studio (BIDS) is now a Microsoft Visual Studio 2010 shell called SQL Server Data Tools (SSDT). The Visual Studio environment alone introduces some slight user-interface changes from the previous version of BIDS.These enhancements to the interface help you to learn about the package-development process if you are new to Integration Services, and they enable you to develop packages more easily if you already have experience with Integration Services. If you are already an Integration Services veteran, you will also notice the enhanced appearance of tasks and data flow components with rounded edges and new icons.
Add New Project Dialog Box
To start working with Integration Services in SSDT, you create a new project by following the same
steps you use to perform the same task in earlier releases of Integration Services. From the File menu,
point to New, and then select Project.
General Interface Changes
After creating a new package, several changes are visible in the package-designer interface
SSIS Toolbox You now work with the SSIS Toolbox to add tasks and data flow components to a package, rather than with the Visual Studio toolbox that you used in earlier versions of Integration Services. You learn more about this new toolbox in the “SSIS Toolbox” section of this chapter.
Parameters The package designer includes a new tab to open the Parameters window for a package. Parameters allow you to specify run-time values for package, container, and task properties or for variables, as you learn in the ”Parameters” section of this chapter.
Variables button This new button on the package designer toolbar provides quick access to the Variables window. You can also continue to open the window from the SSIS menu or by right-clicking the package designer and selecting the Variables command.
SSIS Toolbox button This button is also new in the package-designer interface and allows you to open the SSIS Toolbox when it is not visible. As an alternative, you can open the SSIS Toolbox from the SSIS menu or by right-clicking the package designer and selecting the SSIS Toolbox command.
Getting Started This new window displays below the Solution Explorer window and provides access to links to videos and samples you can use to learn how to work with Integration Services. This window includes the Always Show In New Project check box, which you can clear if you prefer not to view the window after creating a new project. You learn more about using this window in the next section, “Getting Started Window.”
Zoom control Both the control flow and data flow design surface now include a zoom control in the lower-right corner of the workspace. You can zoom in or out to a maximum size of 500 percent of the normal view or to a minimum size of 10 percent, respectively. As part of the zoom control, a button allows you to resize the view of the design surface to fit the window.
Shared Connection Managers
If you look carefully at the Solution Explorer window, you will notice that the Data Sources and Data Source Views folders are missing, and have been replaced by a new file and a new folder. The new file is Project.params
Undo and Redo
A minor feature, but one you will likely appreciate greatly, is the newly added ability to use Undo and Redo while developing packages in SSDT. You can now make edits in either the control flow or data flow designer surface, and you can use Undo to reverse a change or Redo to restore a change you had just reversed. This capability also works in the Variables window, and on the Event Handlers and Parameters tabs. You can also use Undo and Redo when working with project parameters.To use Undo and Redo, click the respective buttons in the standard toolbar. You can also use Ctrl+Z and Ctrl+Y, respectively. Yet another option is to access these commands on the Edit menu.
Note The Undo and Redo actions will not work with changes you make to the SSIS Toolbox, nor will they work with shared connection managers.
Expression Task
Many of the developer experience enhancements in Integration Services affect both control flow and data flow, but there is one new feature that is exclusive to control flow. The Expression Task is a new item available in the SSIS Toolbox when the control flow tab is in focus. The purpose of this task is to make it easier to assign a dynamic value to a variable. Rather than use a Script Task to construct a variable value at runtime, you can now add an Expression Task to the workflow and use the SQL Server Integration Services Expression Language.When you edit the task, the Expression Builder opens. You start by referencing the variable and including the equals sign (=) as an assignment operator. Then provide a valid expression that resolves to a single value with the correct data type for the selected variable. Figure 6-6 illustrates an example of a variable assignment in an Expression Task.
Note The Expression Builder is an interface commonly used with other tasks and data flow components. Notice in Figure 6-6 that the list on the left side of the dialog box includes both variables and parameters. In addition, system variables are now accessible from a separate folder rather than listed together with user variables.
Execute Package Task
The Execute Package Task has been updated to include a new property, Reference Type,which appears on the Package page of the Execute Package Task Editor. You use this property to specify the location of the package to execute. If you select External Reference, you configure the path to the child package just as you do in earlier versions of Integration Services. If you instead select Project Reference, you then choose the child package from the drop-down list. In addition, the Execute Package Task Editor has a new page for parameter bindings, as shown in Figure 6-7. You use this page to map a parameter from the child package to a parameter value or variable value in the parent package.
Source and Destination Assistants
The Source Assistant and Destination Assistant are two new items available by default in the Favorites folder of the SSIS Toolbox when working with the data flow designer. These assistants help you easily create a source or a destination and its corresponding connection manager
ODBC Source and Destination
The ODBC Source and ODBC Destination components, shown in Figure 6-8, are new to Integration
Services in this release and are based on technology licensed by Attunity to Microsoft. Configuration of these components is similar to that of OLE DB sources and destinations. The ODBC Source supports Table Name and SQL Command as data-access modes, whereas data-access modes for the ODBC Destination are Table Name – Batch and Table Name – Row By Row.
Embedded qualifiers Another challenge with the Flat File source in previous versions of Integration Services was the use of a qualifier character inside a string encapsulated within qualifiers. For example, consider a flat file that contains the names of businesses. If a single quote is used as a text qualifier but also appears within the string as a literal value, the common practice is to use another single quote as an escape character, as shown here.
ID,BusinessName
404,'Margie''s Travel'
406, 'Kickstand Sellers'
In the first data row in this example, previous versions of Integration Services would fail to interpret the second apostrophe in the BusinessName string as an escape character, and instead would process it as the closing text qualifier for the column. As a result, processing of the flat file returned an error because the next character in the row is not a column delimiter.This problem is now resolved in the current version of Integration Services with no additional configuration required for the Flat File source.
DQS Cleansing Transformation
The DQS Cleansing transformation is a new data flow component you use in conjunction with Data Quality Services (DQS). Its purpose is to help you improve the quality of data by using rules that are established for the applicable knowledge domain. You can create rules to test data for common misspellings in a text field or to ensure that the column length conforms to a standard specification.
To configure the transformation, you select a data-quality-field schema that contains the rules to apply and then select the input columns in the data flow to evaluate. In addition, you configure error handling. However, before you can use the DQS Cleansing transformation, you must first install and configure DQS on a server and create a knowledge base that stores information used to detect data anomalies and to correct invalid data, which deserves a dedicated chapter. We explain not only how DQS works and how to get started with DQS, but also how to use the DQS Cleansing transformation in Chapter 7, “Data Quality Services.”
Collapsible Grouping
Sometimes the data flow contains too many components to see at one time in the package designer,
depending on your screen size and resolution. Now you can consolidate data flow components into groups and expand or collapse the groups. A group in the data flow is similar in concept to a sequence container in the control flow, although you cannot use the group to configure a common property for all components that it contains, nor can you use it to set boundaries for a transaction or to set scope for a variable.
To create a group, follow these steps:
1. On the data flow design surface, use your mouse to draw a box around the components that you want to combine as a group. If you prefer, you can click each component while pressing the Ctrl key.
2. Right-click one of the selected components, and select Group. A group containing the components
displays in the package designer, as shown here:
3. Click the arrow at the top right of the Group label to collapse the group.
Data Viewer
The only data viewer now available in Integration Services is the grid view. The histogram, scatter plot,
and chart views have been removed.
To use the data viewer, follow these steps:
1. Right-click the path, and select Enable Data Viewer. All columns in the pipeline are automatically included.
2. If instead you want to display a subset of columns, right-click the new Data Viewer icon (a magnifying glass) on the data flow design surface, and select Edit.
3. In the Data Flow Path Editor, select Data Viewer in the list on the left.
4. Move columns from the Displayed Columns list to the Unused Columns list as applicable (shown next), and click OK.
Variables
A common problem for developers when adding a variable to a package has been the scope assignment.
If you inadvertently select a task in the control flow designer and then add a new variable in the Variables window, the variable is created within the scope of that task and cannot be changed. In these cases, you were required to delete the variable, clear the task selection on the design surface, and then add the variable again within the scope of the package. Integration Services now creates new variables with scope set to the package by default. To change the variable scope, follow these steps:
1. In the Variables window, select the variable to change and then click the Move Variable button
in the Variables toolbar (the second button from the left), as shown here:
2. In the Select New Scope dialog box, select the executable to have scope—the package, an
event handler, container, or task—as shown here, and click OK:
Prior to the current version of Integration Services, if an expression result had a data type of DT_WSTR or DT_STR, any characters above a 4000-character limit would be truncated. Furthermore, if an expression contained an intermediate step that evaluated a result exceeding this 4000-character limit, the intermediate result would similarly be truncated. This limitation is now removed.
The SQL Server Integration Services Expression Language now has four new functions:
LEFT You can now more easily return the leftmost portion of a string rather than use the SUBSTRING function:
LEFT(character_expression,number)
REPLACENULL You can use this function to replace NULL values in the first argument with the expression specified in the second expression:
REPLACENULL(expression, expression)
TOKEN This function allows you to return a substring by using delimiters to separate a string into tokens and then specifying which occurrence to return:
TOKEN(character_expression, delimiter_string, occurrence)
TOKENCOUNT This function uses delimiters to separate a string into tokens and then returns the count of tokens found within the string:
TOKENCOUNT(character_expression, delimiter_string)
Supported Deployment Models
The latest version of Integration Services supports two deployment models:
Package deployment model The package deployment model is the deployment model used in previous versions of Integration Services, in which the unit of deployment is an individual package stored as a DTSX file. A package can be deployed to the file system or to the MSDB database in a SQL Server database instance. Although packages can be deployed as a group and dependencies can exist between packages, there is no unifying object in Integration Services that identifies a set of related packages deployed using the package model. To modify properties of package tasks at runtime, which is important when running a package in different environments such as development or production, you use configurations saved as DTSCONFIG files on the file system. You use either the DTExec or the DTExecUI utilities to execute a package on the Integration Services server, providing arguments on the command line or in the graphical interface when you want to override package property values at run time manually or by using configurations.
Project deployment model With this deployment model, the unit of deployment is a project, stored as an ISPAC file, which in turn is a collection of packages and parameters. You deploy the project to the Integration Services catalog, which we describe in a separate section of this chapter. Instead of configurations, you use parameters (as described later in the “Parameters” section) to assign values to package properties at runtime. Before executing a package, you create an execution object in the catalog and, optionally, assign parameter values or environment references to the execution object. When ready, you start the execution object by using a graphical interface in SQL Server Management Studio by executing a stored procedure or by running managed code.
For More
Microsoft_Press_eBook_Introducing_Microsoft_SQL_Server_2012
No comments:
Post a Comment