As mentioned earlier, an SSIS package is defined by tasks, connections, containers, and precedence constraints. A package can contain one or more tasks (also called executables).
SSIS has added several new tasks and enhanced others, and we will briefly review some of these tasks in this section. Different SSIS tasks will appear in the Toolbox window depending on the current design surface.
Control Flow Containers and Tasks
Figure shows the Toolbox when you’re working in the Control Flow design surface. Let’s look at the available containers and tasks.
Toolbox for the Control Flow design surface
Containers provide a way to logically group tasks together for readability or dependency purposes. SSIS provides three Control Flow containers: the For Loop container, the Foreach Loop container, and the Sequence container.
The Sequence container can contain one or more tasks, or other containers, allowing you to logically group tasks together. Because these tasks are contained within a Sequence container, they can be managed as a unit. This task provides visual benefits as well—by selecting the upper-right corner of a container, you can expand or collapse the detail of the tasks within, further enhancing readability of the package, as shown in Figures.
An expanded Sequence container
A collapsed Sequence container
Using a Sequence container also allows you to do the following:
For Loop and Foreach Loop Containers
Two containers allow for repeating control flow within a package:
Let’s look at an example that uses the Foreach Loop container. Suppose that we wish to have a nightly process that evaluates error logs generated by SQL Server (under the C:Program Files Microsoft SQL ServerMSSQLLOG directory). In our hypothetical scenario, an external application will evaluate each error file in that directory. After the external process is finished, it will delete the row from the table. Our process is responsible for letting the application know which files need to be processed. In this exercise, we will explore this directory (in real time), finding each file within it and adding the filenames to a table of files pending processing.
Adding a variable
Foreach Loop Editor showing collection properties
Foreach Loop container
You will use this package in the upcoming Scripting task exercise.
Analysis Services Tasks
If you use Analysis Services, you’ll want to be aware of three tasks:
Data Flow Task
A Data Flow task represents an instance of the SSIS data flow engine executable. This executable is responsible for calling the data sources, data destinations, and any transformations that have been added to the Data Flow task.
As you saw in our first example in this chapter, when you add the Data Flow task to the Control Flow design surface and double-click it, you are directed to the Data Flow design surface. A Data Flow task can contain one or more Data Flow tasks (which we’ll review in the “Data Flow Designer Tasks” section later in this chapter). You can also have more than one Data Flow task on the Control Flow design surface.
As noted earlier, the Data Flow tasks describe how data moves from a data source (source adapter), and how the data is modified and mapped (transformation) to a destination (destination adapter). In other words, you take data from somewhere, change it in some way, and then store the results somewhere else.
This mapping of source to transformation to destination is called a graph. A graph is a set of connected Data Flow components. One graph represents, for example, the data population of one table from another table. Unlike DTS 2000’s Transform task, a single Data Flow task can also update multiple tables (which is to say, multiple graphs).
Data Profiling Task
As SQL Server is used more and more for the largest enterprise data warehousing projects in the world, it becomes increasingly important for the core product to provide the majority of dataquality functionality required for these implementations. The new Data Profiling task helps to deliver this functionality. It provides support for eight different profiles: five that profile at the column level and three that provide profiling between columns.
The Data Profiling task provides the following data profiles for individual columns:
The Data Profiling task generates an XML file that contains the results. This file can then be further parsed and used within a package, or the stand-alone Data Profile Viewer, available within the SQL Server program group, can be used to view the profile output externally from the package.
Execute Package Tasks
SSIS includes two separate tasks for executing SSIS packages: the Execute Package task and the Execute DTS 2000 Package task. Unfortunately, the upgrade path for packages created in SQL Server 2000 is less than perfect. Although best efforts to upgrade are attempted by the upgrade wizard, certain features have changed too significantly in SSIS to provide a smooth upgrade path from prior versions. This is not as bad as it may seem—many very complicated DTS packages are now far simpler to create within SSIS. However, to address compatibility issues, SSIS includes the Execute DTS 2000 Package task.
The Execute DTS 2000 Package task will allow the execution of a DTS 2000 package from within an SSIS package. This way, you can continue to use your older packages until you have an opportunity to rewrite them in the new version. The Execute Package task is used to execute other SSIS packages as part of the workflow. Both tasks allow you to encapsulate and reuse existing packages, rather than creating task or data flow redundancy within your package.
Bulk Insert Task
The Bulk Insert task imports text files into SQL Server tables. Although you cannot validate, scrub, or transform data using this task, it is usually the fastest method for importing data from a text file into SQL Server. Consider using the Bulk Insert task when data file import (to SQL Server) performance is important. We will demonstrate this task in the next exercise.
Execute SQL Task
The Execute SQL task allows you to run SQL statements or call stored procedures against a SQL-compliant connection. For example, you can use this task to create a table, and then populate it with data based on a query.
In the following example, we’ll use both the Execute SQL and Bulk Insert tasks. We’ll demonstrate deleting data from a staging table and populating data from a text file into a SQL Server table using the Bulk Insert task.
The following exercise uses the BookSeller database and BookSales.txt file from earlier examples in this chapter.
Execute SQL and Import BookSales tasks
Execute Process Task
The Execute Process task allows you to execute aWin32 executable or a batch file within an SSIS package. For example, you could use this task to call a third-party application that performs business functions unrelated to SSIS task functionality. Be careful not to call executables that require user feedback; otherwise, your package execution may hang.
File System Task
The File System task allows you to perform several different file-based operations, all without needing to create custom code (for example, in DTS 2000, if you wanted to rename a file you would be required to either use xp_cmdshell or write an ActiveX script). The File System task allows you to copy a folder from one location to another, copy a file from one location to another, create or delete a folder in a specified location, delete all files and folders in a folder, delete a file in a specified location, move a file or directory from one location to another, rename a file in a specified location, and change file or directory attributes.
The FTP task allows you to both send and receive files using FTP. With the FTP task, you can send files, receive files, create or remove a local directory, create or remove a remote directory, delete local files, and delete remote files—all without writing a bit of code.
Maintenance Plan Tasks
SSIS includes a set of tasks designed to assist in the maintenance of your SQL Server database environment. These tasks are found under the Maintenance Plan Tasks group in the Toolbox. as shownin Figure. They provide a flexible and enhanced alternative to manually creating plans within SQL Server Agent.
Maintenance Plan tasks in the Toolbox
Maintenance Plan tasks allow you to back up databases, check database integrity, defragment (using ALTER INDEX, REORGANIZE) or rebuild indexes (using ALTER INDEX, REBUILD), execute SQL Server Agent jobs and T-SQL statements, clean up database history (backup and restore history, SQL Server Agent Job history, and database maintenance plan history), notify operators, shrink databases, and update table statistics.
Message Queue Task
The Message Queue task is used to send or receive messages between SSIS packages or custom application queues using Microsoft Message Queuing (MSMQ) functionality. These messages allow asynchronous communication between systems and applications, meaning that messages can be delivered to a queue, where they will await pickup and processing from the MSMQ service on the server or workstation. Once the message is received, the receiving SSIS package can process and act upon the received message. Messages can be sent in text, file, or variable format.
Send Mail Task
The Send Mail task relies on a valid SMTP server to send e-mail messages. If you have a valid SMTP server on your network, you will be able to use this task to send e-mail notifications on package failures
or successes, specific task events, or reporting of SQL Server data (for example, reporting on the status of database file sizes). This task lets you define an e-mail’s To line, From line, CC line, BCC line, Subject line, and priority level, as well as include file attachments. The e-mail body (called the message source) can be directly entered text, or it can be based on the value of a variable or an external file.
SSIS includes two separate scripting tasks: the ActiveX Script task and the Script task. The ActiveX Script task is a carryover from DTS 2000. It allows you to write scripts using the VBScript or Jscript language to define logic within packages, write functions, access data using ActiveX Data Objects, perform computations, and set variables.
The ActiveX Script component will be removed from the next version of SQL Server, so you should remove it from your packages as soon as possible. The Script task should be used in its place. Unlike the ActiveX Script task, the Script task allows you to write your scripts with the full-featured Visual Basic .NET or C# programming languages. Scripts are written in the VSTA environment, which includes IntelliSense, debugging, Object Explorer, and integrated help modules. Using the .NET Framework and COM objects offers obvious performance and functionality benefits.
In the following example, you will use the Script task to combine multiple files in a directory into a single file (so that it can be imported by the hypothetical application). You will reuse part of the project and package you created for the earlier example demonstrating the use of the Foreach Loop container. As you recall, this package looped through each error file in the C:Program Files Microsoft SQL ServerMSSQLLOG directory. For each file it found, a row was inserted into the PendingFileProcess table.
With the Script task, you can now perform actions that are not natively available within the SSIS environment. In this example, you have been asked to loop through each error file and merge them into a new, single, separate file. This way, the external (hypothetical) application can instead load all errors from a single file.
Script Task Editor dialog box
This will allow you to access file-streaming functionality.
Within the Public Sub Main() procedure, add the following code:
This code is commented so you can better understand what it does. In a nutshell, the script grabs the value of the FileName variable (which will be populated by the Foreach Loop container task). This is the file that will be combined into the single C:ApressMergedErrorLog.txt file.
If the package succeeded, both the Foreach Loop container and the Script task will turn green, as shown in Figure. You should also find a populated C:ApressMergedErrorLog.txt file containing the contents of all error logs from the error log directory.
Foreach Loop container and script example after execution
Web Service Task
The Web Service task executes a web method using an HTTP connection. Within the task, you designate
the HTTP connection and the Web Services Description Language (WSDL) file (which specifies the location of the service and the functionality of the web service).
Web Service task input parameters can be specified through strings within the task or, preferably, by using variables. Similarily, the results from aWeb Service task can be written to a file or to a variable.
WMI is the programmatic interface provided by Microsoft to allow users to monitor and control Windows system resources. SSIS includes two tasks that tie into the WMI interface: the WMI Data Reader task and the WMI Event Watcher task.
The WMI Data Reader task allows you to execute a WMI Query Language (WQL) query against a specific system (for example, retrieving the application event log file). The WMI Event Watcher task allows you to execute aWQL query that watches for a specific event to happen (for example, being notified when logical disk space falls beneath a certain threshold). The WMI Event Watcher task can also be used to monitor for the existence of a file or files within a specific directory. This is a common requirement to make nightly batch-loading a little more flexible.
The XML task allows you to perform operations against XML documents. Some of the operations include the ability to compare XML documents (writing the differences to an XML diffgram document), merge two XML documents into one document, query and evaluate an XML document using the XPath language, and transform an XML document based on an XSLT style sheet.
Data Flow Designer Tasks
Before using the Data Flow design surface, you must first add a Data Flow task to the Control Flow design surface. Once you have added the Data Flow task, you can begin adding items (from the Toolbox) to the Data Flow design surface.
Data Flow components fall into three categories:
Source adapters define the source of the data; the specific table, query, or view when applicable; and the specific columns that need to be extracted. In SSIS, you extract data using the data source adapters listed in Table.
SSIS Source Adapters
Destination adapters define the endpoint of extracted data, receiving it either directly from a data source adapter or from a transformation. In SSIS, you can extract data using the data destination adapters shown in Table .
SSIS Destination Adapters
SSIS provides several new and enhanced transformation tasks, shown in Table, that are used to modify data as it moves from the source data adapter to the destination data adapter.
Export Column Transformation Example
For this example, imagine you’ve been asked by a hypothetical third-party catalog provider to generate thumbnail image GIFs for all of the products in the AdventureWorks application, for use in the third party’s online sales application.
For the SQL command text, enter the following query:
Export Column Transformation Editor dialog box
You should see that 101 rows were processed by the Export Column task.
Export Column package after execution
The files should have been generated to your C:Apress directory. You can then use the FTP task to send the files to the client.
Row Sampling Transformation Example
For this example, your company’s HR department would like you to generate a text file containing a random sampling of 20 employees who will be asked to participate in an employee survey. You’ll use the AdventureWorks table HumanResources.Employee for the SQL Server source data, and you’ll use the Row Sampling transformation to export the sampling to a text file.
After debugging the package, check the contents of the C:ApressEmployeeSampling.txt file for the 20 sampled employees. Also, if you debug the package again, you’ll notice that you get a different set of employees exported to the file.
SQL Server 2008 Related Interview Questions
|SQL Server 2000 Interview Questions||MSBI Interview Questions|
|SQL Server 2008 Interview Questions||SQL Server 2005 Interview Questions|
|SSIS(SQL Server Integration Services) Interview Questions||SSRS(SQL Server Reporting Services) Interview Questions|
|Microsoft Entity Framework Interview Questions||LINQ Interview Questions|
|SQL Server Analysis Services (SSAS) Interview Questions||Sql Server Dba Interview Questions|
SQL Server 2008 Related Practice Tests
|SQL Server 2000 Practice Tests||MSBI Practice Tests|
|SQL Server 2008 Practice Tests||SQL Server 2005 Practice Tests|
|SSIS(SQL Server Integration Services) Practice Tests||SSRS(SQL Server Reporting Services) Practice Tests|
|Microsoft Entity Framework Practice Tests||LINQ Practice Tests|
Sql Server 2008 Tutorial
Sql Server 2008 Overview
Sql Server Installation And Configuration
Sql Server Encryption
Automation And Monitoring
Integrated Full-text Search
New Datatypes In Sql Server 2008
T-sql Enhancements For Developers
T-sql Enhancements For Dbas
Sql Server And Xml
Sql Server Xml And Xquery Support
Linq To Sql
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.