SQL Server Integration Services 2005
Business Intelligence SQLServer2005
Integrate
Integrate data from any data source. Build, manage and deploy scalable integration solutions or populate your data warehouse and build a holistic view of your business.
Report
Report on enterprise-wide data. Create, manage, and deliver server-based reports with interactive views that provide valuable insights into here business is heading.
Analyze
Analyze results. Provide a consolidated view across all business dimensions as the foundation for all relational, multidimensional, and predictive analysis, enabling deep insight into the key drivers impacting businesses today.
SSIS Means
SQL Server Integration Services (SSIS) is a component of Microsoft SQL Server 2005. It replaces Data Transformation Services, which has been a feature of SQL Server since Version 7.0./8.0 Unlike DTS, which was included in all versions, SSIS is only available in the "Standard" and "Enterprise" editions.
Integration Services provides a platform to build data integration and workflow applications. The primary use for SSIS is data warehousing, as the product features a fast and flexible tool for data extraction, transformation, and loading (ETL). The tool may also be used to automate maintenance of SQL Server databases, update multidimensional cube data, and perform other functions.
Introduction
SSIS has gone through a complete redesign and rewrite from Data Transformation Services (DTS) in SQL Server 2000. SSIS adopts Microsoft Visual Studio® as its development environment, but continues to leverage the standard management tool for SQL Server 2005. The new development environment is called the Business Intelligence (BI) Development Studio, while the administration environment is aptly named SQL Server Management Studio. The separation of the two environments allows developers and database administrators (DBAs) to focus on specific tasks of development or administration.
Wizards for SSIS
Microsoft has updated the Import/Export Wizard from SQL Server 2000 that was used to help DBA and developers automate the repetitive tasks of moving and copying data from one location to another.
The Business Intelligence Development Studio
In the Business Intelligence Development Studio, you develop SSIS packages in an Integration Services project within a solution. The solution is a container that enables you to simultaneously work with multiple Visual Studio projects using a set of tools for developing enterprise-level, tightly integrated BI solutions, including SSIS solutions.
Visual Studio 2005
BI Development Studio is built around Visual Studio 2005, which helps you design, build, test, deploy, and extend SSIS packages in an integrated development environment. BI Development Studio also supports using the .NET Framework with the other Microsoft development tools (Microsoft Visual Basic®. NET, C#, C++, J#). Because of its integration with Visual Studio 2005, BI Development Studio comes with integrated development features including a robust debugger, integrated source code control for multi-developer environments, and integrated help.
The SSIS Package Designer
The SSIS Package Designer is built into the BI Development Studio and is the main surface for package development. The Designer contains a set of graphical tools that make data movement, workflow, and complex data transformations available with minimal or no coding. The Designer has several windows that are used for control flow, data flow, connection creation, and variable creation.
Control Flow
The workflow of a package is built using the Control Flow designer. The designer is a drawing surface that lets you graphically define how tasks interact with each other and the order in which they get executed.
Data Flow
The Data Flow designer manages all of the data movement and transformations between a source and target. In order to include data flow within your package, you must manually add a Data Flow task to the Control Flow designer or let SSIS do it for you when you open the Data Flow designer window. Although the Data Flow task is a logical container of data movement and transformation steps, if one of these movement or transformation steps fail, the entire Data Flow task fails.
Connection Managers
A tab at the bottom of the Control Flow design window contains a list of data connections that both control flow and data flow tasks can use. These connections can be referenced as either source or target in any of the data flow operations, and can connect to relational or Analysis Services databases, flat files, or other data sources.
Variables
One of the optional design-time windows can display a list of variables. Variables are used throughout the package to pass values between tasks, and to dynamically control how the package executes at run time.
Executing a Package
- If wish to execute a package, you can click on the play icon on the toolbar, or press F5, or click Start and then Debug on the menu.
- To get back to design mode, you can click the Stop icon on the debugging toolbar, or press Shift+F5, or select Debug and then Stop Debugging on the menu.
The Data Source Elements
Data sources contain the information that SSIS will need to connect to an OLE DBcompliant system like SQL Server, Oracle, DB2, or Microsoft Access to name just a few. You can also create a connection to less traditional data sources like Analysis Services, XML-based sources, or the Microsoft Directory Services. Your data sources can be shared by multiple packages in your Business Intelligence Development Studio project or be used once in a single package.
Data Source View
A data source view (DSV) is a logical view of your data source or data sources. Simply put, it is a collection of database objects (tables, views, and stored procedures) that are logically grouped together and can be shared across your project. Data source views can be reused in Analysis Services and Report Builder. Data source views closely resemble SQL Server relational views and can present a business logical view to your data model.
Control Flow Task Properties
- Disable – If set to true, then the task is disabled and will not execute.
- DelayValidation – If set to true, SSIS will not validate any of the properties set in the task until run time
- Description – The description of what the instance of the task does. The default name for this is <task name>. This does not have to be unique and should accurately describe what the task does for people that may be
- ExecValueVariable – Contains the name of the custom variable that will store the output of the task’s execution
- Fail Package on Failure – If set to true, the entire package will fail if the individual task fails
- Fail Parent on Failure – If set to true, the task’s parent will fail if the individual task reports an error
- ID – A unique ID that is associated with an instance of a task
Control Flow Task Properties
- IsolationLevel – Specifies the isolation level of the transaction if transactions are enabled in the TransactionMode property
- LoggingMode – Specifies the type of logging that will be performed for this task
- Name – The name associated to the task. The default name is <task name>.
- TransactionOption – Specifies the transaction attribute for the task.
Control Flow Tasks
Data Mining Query Task
The Data Mining Query task allows you to run predictive queries against your Analysis Services data mining models. You will probably want to use this task to output the results of a query to a table that will be used later in the controller flow.
Execute Package Task
The Execute Package execute a package from within the parent package. One of these is that you now have a new ExecuteOutofProcess property that, if set to true, will execute the package in its own process and memory space. The default value of this property is true.
Execute Process Task
It executes a batch file or executable in the package. For example, you can now specify input variables that will be passed into the process or output variables that will contain the output of the execution. There is also an error output variable that will contain any errors encountered during execution of the process. This allows you handle errors with greater flexibility.
Execute SQL Task
The Execute Task can execute relational queries, DDL, or DML against a connection (not just SQL Server connections). In SSIS you can store the query in a flat file or as a variable in addition to directly inputting the query. This is useful when you want to create an install process from outside DDL files, or if you want to create what amounts to a reusable function library of SQL files outside the SSIS process.
File System Task
This task can handle directory operations like creating, renaming, or deleting a directory. It can also manage file operations like moving, copying, or deleting files.
File Transfer Protocol Task
The File Transfer Protocol task allows you to perform file operations using FTP. In the SQL Server 2005 version of the task, you can send, receive, and create directories locally and remotely all through FTP.
Message Queue Task
A powerful application of the Message Queue task is when you use it to send messages from package to package to parallelize your package operations.
Script Task
One of the primary reasons to migrate to SSIS from SQL Server 2000 in many ETL developer’s eyes will be the Script task (known as the ActiveX Script task in SQL Server 2000 DTS). The new Script task has a rich design in Visual Studio including ntelliSense and the color coding of your script.
Send Mail Task
The Send Mail task allows you to send an e-mail message to a user or distribution list.
SQL Server Analysis Services Execute DDL Task
The new SQL Server Analysis Services Execute DDL task allows you to execute DDL to create, modify, delete, or process Analysis Server objects. You can execute DDL code that is stored inside the task itself, as a variable, or in a file outside the scope of the package.
SQL Server Analysis Services Processing Task
The SQL Server Analysis Services Processing task is a descendant of the Analysis Services Processing task in SQL Server 2000 but with much more functionality.
Web Service Task
Once you execute the method, you can write the results from the Web service to a file or to a variable. This would be useful for trading information with third-party applications. For example, you can execute a Web service method to retrieve a list of updated products at Amazon.
WMI Data Reader Task
Windows Management Instrumentation (WMI) is one of the best-kept secrets in
Windows. WMI allows you to manage Microsoft Windows® servers and workstations
through a scripting interface. The WMI Data Reader task allows you to
interface with this environment by writing WMI Query Language (WQL) queries against the server or workstation.
WMI Event Watcher Task
The WMI Event Watcher task empowers SSIS to wait for and respond to certain WMI events that occur in the operating system. For example, you can set the
task to respond to a given error when it is written to the application event log.
XML Task
The XML task is a new comprehensive task that can perform many different functions with your XML files. It allows SSIS to dynamically modify, merge, or create XML files at run time.
Foreach Loop Container
The most exciting new container is the Foreach Loop container. This container might
save traditional DTS developers the most amount of time. The Foreach Loop container allows the SSIS developer to loop through a collection of files, rows in a Microsoft ActiveX® Data Objects (ADO) recordset, or variables and perform a series of tasks in the container.
DataFlow Task
The Data Flow designer manages all of the data movement and transformations between a source and target.
The Data Flow Elements
Sources
A source is where you specify the location of your source data in the data pump. Sources will generally point to Connection Manager in SSIS. By pointing to the Connection Manager, you can reuse connections throughout your package, because you need only change the connection in one place.
Componenents: DataReaderSource, ExcelSource, FlatFileSource, OLEDB Source, Raw File Source, XML Source.
Data Transformations
SSIS includes a number of new objects that transform data in various ways including cleansing, converting, distributing, and merging data as well as transformations that accelerate development of common business intelligence tasks.
Components: Aggregate, Audit, Character Map, Conditional Split, Copy Column, Data Conversion, Data Miniing Query, Derived Column, Export Column, Fuzzy Grouping, Fuzzy Lookup, Import Column, Lookup, Merge, Merge Join, Multicase, OlE DB Command, Percentage Sampling, Pivot, Row Count, Row Sampling, Script Component, Slowly Changing Dimension, Sort, Term Extraction, Term Lookup, Union All, Unpivot.
Destinations
Inside the data flow, destinations accept the data from the data sources. The flexible architecture can send the data to nearly any OLE DB-compliant data source or to a flat file. Almost every destination shares the same properties in the primary two screens. Typically, in the Connection Manager tab of the destination, you’ll set the source to a connection that has already been established in the Connection Manager.
Components: Data Mining Model Training, DataReader Destination, Dimension Processing, Excel Destination, Flat File Destination, OLE DB Destination, Partition Processing, Raw File Destination, Recordset Destination, SQL Server Destination, SQ Server Mobile Destination.
The Event Handling Elements
The Event Handling Elements
- OnError This event is raised when an error occurs.
- OnExecStatusChanged This event is raised when an object’s execution status has changed from True to False or vice versa.
- OnInformation This event is raised when an object has information to report.
- OnPostExecute This event is raised immediately after an object completes execution.
- OnPostValidate This event is raised immediately after an object is validated in design mode.
- OnPreExecute This event is raised just before an object starts executing.
- OnPreValidate This event is raised when its validation process begins.
- OnProgress This event is raised when measurable progress has been made towards completion.
- OnQueryCancel This event is raised by an object to determine whether it should stop running.
- OnTaskFailed This event is raised if a task fails. OnVariableValueChanged This event is raised if the variables
The Error Handling
SSIS can manage errors in many different ways using several new features. It can elegantly handle or ignore errors based on the type of error or based on when or where the error occurs. SSIS allows a developer to deal effectively with both procedural and data errors.
Logging Transactions
SQL Server Integration Services includes log providers that you can use to implement logging in packages, containers, and tasks. With logging, you can capture run-time information about a package, helping you audit and troubleshoot a package every time it is run. For example, a log can capture the name of the operator who ran the package and the time the package began and finished.
