Using Integration Services with the Database Engine
The Microsoft SQL Server Database Engine is the core service for storing, processing, and securing data in either a relational (tabular) format or in XML documents. SQL Server Database Engine provides tools and features that you can use to build high-volume online transactional processing (OLTP) databases to manage changing data or data warehouses to organize large amounts of stable data for ease of analysis and retrieval.
For more information about the Database Engine, see Database Engine Concepts.
This topic focuses on the Integration Services objects that are especially designed to work with Database Engine. Integration Services also includes objects to connect and write data to a SQL Server Compact database.
For more information, see Using Integration Services with SQL Server Compact.
Connection Managers
A connection manager is a logical representation of a connection to a data source. There are a variety of connection managers that can connect to the Database Engine. Only the SMO connection manager is specific to the Database Engine. The other connection managers have a more universal application in packages and can connect to other types of data stores.
SMO Connection Manager
The SMO connection manager enables a package to connect to a SQL Management Object (SMO) server. The transfer tasks in Integration Services use an SMO connection manager. When you configure an SMO connection manager, you can specify the name of a server on which SQL Server is installed and select the authentication mode for connecting to the server.
For more information, see SMO Connection Manager.
Tasks
Tasks are control flow elements that define units of work that are performed in a package. You can use the following tasks to create, alter, and process objects in an SQL Server database.
The following tasks are designed to work specifically with the Database Engine.
The Bulk Insert task uses the Flat File Connection Manager and the OLE DB Connection Manager.
The maintenance plan tasks use the ADO.NET Connection Manager.
Bulk Insert Task
The Bulk Insert task copies data from text files into a SQL Server table or view. If you have data in text files, and the data requires no transformation, the Bulk Insert task is the quickest way to load data from text files into SQL Server. You can use a format file together with the Bulk Insert task to define the fields and data types, or you can set the bulk insert options in the task. The Bulk Insert task supports both XML and nonXML format files.
For more information about this task, see Bulk Insert Task.
The Bulk Insert task uses a File connection manager to connect to the source file and an OLE DB connection manager to connect to the Database Engine.
For more information, see Flat File Connection Manager and OLE DB Connection Manager.
Transfer Tasks
The transfer tasks move or copy SQL Server objects between instances of SQL Server and between SQL Server databases. You can use these tasks to transfer SQL Server objects such as databases, error messages, jobs, logins, user-defined stored procedures in the master database, and to transfer database-level objects such as tables, views, and schemas. These tasks work only with SQL Server; they can transfer between instances of SQL Server 2000, instances of SQL Server, or one of each.
Transfer Database Task
The Transfer Database task transfers a SQL Server database between two instances of SQL Server. In contrast to the other tasks that only transfer SQL Server objects by copying them, the Transfer Database task can either copy or move a database. This task can also be used to copy a database that is on the same server.
For more information about this task, see Transfer Database Task.
Transfer Error Messages Task
The Transfer Error Messages task transfers SQL Server user-defined error messages between instances of SQL Server. User-defined messages are messages with an identifier that is equal to or greater than 50000. Messages with an identifier less than 50000 are system error messages, and cannot be transferred by using the Transfer Error Messages task. The task can be configured to transfer only messages in selected languages as long as a us_english version of the message that uses code page 1033 exists on the destination server.
For more information about this task, see Transfer Error Messages Task.
Transfer Jobs Task
The Transfer Jobs task transfers one or more SQL Server Agent jobs between instances of SQL Server. You can also indicate whether the transferred jobs are enabled at the destination.
For more information about this task, see Transfer Jobs Task.
Transfer Logins Task
The Transfer Logins task transfers one or more logins between instances of SQL Server. You can also indicate whether the task copies the security identifiers (SIDs) associated with the logins. The SIDs are required if you transfer a database at the same time; otherwise, the transferred logins are not recognized by the destination database.
For more information about this task, see Transfer Logins Task.
Transfer Master Stored Procedures Task
The Transfer Master Stored Procedures task transfers one or more user-defined stored procedures between master databases on instances of SQL Server. To transfer a stored procedure from the master database, the owner of the procedure must be dbo. This task does not copy system stored procedures.
For more information about this task, see Transfer Master Stored Procedures Task.
Transfer SQL Server Objects Task
The Transfer SQL Server Objects task transfers one or more types of objects in a SQL Server database between instances of SQL Server. Depending on the version of SQL Server that is used as a source, different types of objects are available to copy. For example, only a SQL Server database includes schemas and user-defined aggregates. Depending on the objects transferred, you can select related objects to include in the transfer.
For more information about this task, see Transfer SQL Server Objects Task.
Maintenance Tasks
Integration Services includes a set of tasks that perform database maintenance functions. These tasks are typically used in database maintenance plans, but the tasks can also be included in SSIS packages. For more information, see Maintenance Plan Wizard and Maintenance Plans.
The maintenance tasks can be used with SQL Server 2000 and SQL Server databases and database objects.
Back Up Database Task
The Back Up Database task performs different types of SQL Server database backups. The task can back up a single database or multiple databases. If the task backs up only a single database, you can choose the backup component: the database, or its files and filegroups.
For more information about this task, see Back Up Database Task.
Check Database Integrity Task
The Check Database Integrity task examines the allocation and structural integrity of all the objects in the specified database. The task can check a single database or multiple databases, and you can choose whether to also check the database indexes.
For more information about this task, see Check Database Integrity Task.
Execute SQL Server Agent Job Task
The Execute SQL Server Agent Job task runs SQL Server Agent jobs. SQL Server Agent is a Microsoft Windows service that runs jobs that have been defined in an instance of SQL Server. You can create jobs that execute Transact-SQL statements and ActiveX scripts, perform Analysis Services and Replication maintenance tasks, or run packages. You can also configure a job to monitor Microsoft SQL Server and to raise alerts. SQL Server Agent jobs are typically used to automate tasks that you perform repeatedly.
For more information about this task, see Execute SQL Server Agent Job Task.
Execute T-SQL Statement Task
The Execute T-SQL Statement task runs Transact-SQL statements. This task is similar to the Execute SQL task. However, the Execute T-SQL Statement task supports only the Transact-SQL version of the SQL language and you cannot use this task to run statements on servers that use other dialects of the SQL language. If you must run parameterized queries, save the query results to variables, or use property expressions, you should use the Execute SQL task instead of the Execute T-SQL Statement task.
For more information about this task, see Execute T-SQL Statement Task and SSIS Execute SQL Task.
History Cleanup Task
The History Cleanup task deletes historical data related to backup and restore activities, SQL Server Agent jobs, and database maintenance plans. The task includes a property for specifying the oldest date of data retained in the history tables. The task deletes entries in the history tables in the SQL Server msdb database, such as the backupfile and backupfilegroup tables.
For more information about this task, see History Cleanup Task.
Maintenance Cleanup Task
The Maintenance Cleanup task removes files related to maintenance plans, including database backup files and reports created by maintenance plans. The task includes an option to remove a specific file or remove a group of files in a folder. Optionally you can specify the extension of the files to delete. You can specify the age of files to delete by using days, weeks, months, or years.
For more information about this task, see Maintenance Cleanup Task.
Notify Operator Task
The Notify Operator task sends notification messages to SQL Server Agent operators. A SQL Server Agent operator is an alias for a person or group that can receive electronic notifications. The task can notify one or more operators by using e-mail, pager, or net send. Each operator can be notified by different methods.
For more information about this task, see Notify Operator Task.
Rebuild Index Task
The Rebuild Index task rebuilds indexes in SQL Server database tables and views. The task can rebuild indexes in a single database or multiple databases. If the task rebuilds only the indexes in a single database, you can choose the views and tables whose indexes the task rebuilds.
For more information about this task, see Notify Operator Task.
Reorganize Index Task
The Reorganize Index task reorganizes indexes in SQL Server database tables and views. The task can reorganize indexes in a single database or multiple databases. If the task reorganizes only the indexes in a single database, you can choose the views or the tables whose indexes the task reorganizes. The Reorganize Index task also includes an option to compact large object data. Large object data is data with the image, text, ntext, varchar(max), nvarchar(max), varbinary(max), or xml data type.
For more information about this task, see Reorganize Index Task.
Shrink Database Task
The Shrink Database task reduces the size of SQL Server database data and log files. The task can shrink files for a single database or multiple databases. If the Shrink Database task shrinks multiple databases, the task runs multiple SHRINKDATABASE commands, one for each database. All instances of the SHRINKDATABASE command use the same argument values, except for the argument that provides the database name.
For more information about this task, see Shrink Database Task.
Update Statistics Task
The Update Statistics task updates information about the distribution of key values for one or more statistics groups (collections) in the specified table or indexed view. The task can update statistics for a single database or multiple databases. If the task updates only the statistics in a single database, you can choose the views or the tables whose statistics the task updates. You can configure the update to update all statistics, column statistics only, or index statistics only.
If the Update Statistics task updates statistics in multiple databases, the task runs multiple UPDATE STATISTICS statements, one for each table or view. All instances of UPDATE STATISTICS use the same clause, but different values for the argument that provides the table or view name.
For more information about this task, see Update Statistics Task.
Data Flow Destinations
Destinations are the data flow components that load the output of a data flow into different types of data sources or create an in-memory dataset. Only the SQL Server destination is specific to the Database Engine.
SQL Server Destination
The SQL Server destination connects to a local SQL Server database and bulk loads data into SQL Server tables and views. You should not use the SQL Server destination in packages that access a SQL Server database on a remote server; instead, use the OLE DB destination. The SQL Server destination offers the same high-speed insertion of data into SQL Server that the Bulk Insert task provides; however, by using the SQL Server destination, you can apply transformations to column data before the data is loaded into SQL Server.
For more information about this destination, see SQL Server Destination.
Other Integration Services Tasks and Components for the Database Engine
The Execute SQL task and many Integration Services components connect to relational databases. The Execute SQL task runs SQL statements; data flow components extract or load data, or use Database Engine to store temporary tables and indexes. Although not specifically designed to work with Database Engine, the following data flow components access data in relational databases or require a SQL Server database to do their work
Execute SQL task. Runs SQL statements or stored procedures. The task can contain either a single SQL statement or multiple SQL statements that run sequentially. The task can also execute SQL statements against some nonrelational data stores, such as Excel. For more information, see SSIS Execute SQL Task.
ADO NET source. Consumes data from a .NET provider and makes the data available to the data flow. For more information, see ADO NET Source.
OLE DB source. Extracts data from relational data sources by using an OLE DB provider. For more information, see OLE DB Source.
Fuzzy Grouping transformation. Performs data cleaning tasks by identifying rows of data that are likely to be duplicates. The transformation connects to an instance of Database Engine to create the temporary tables that the transformation algorithm requires to do its work. For more information, see Fuzzy Grouping Transformation.
Fuzzy Lookup transformation. Performs data cleaning tasks by doing fuzzy-match lookups in a reference table. The reference must be a table in a SQL Server database. The transformation connects to an instance of Database Engine to create the match indexes that it uses. For more information, see Fuzzy Lookup Transformation.
Lookup transformation. Extends or modifies a dataset by doing lookups and adding data from a reference table. The transformation connects to the relational database that contains the reference table. For more information, see Lookup Transformation.
OLE DB Command transformation. Runs an SQL statement for each row in the transformation input. The transformation connects to the relational database when running the SQL statement. For more information, see OLE DB Command Transformation.
Term Extraction transformation. Extracts terms from text and optionally, uses a table in a relational database to filter out exclusion terms. The transformation connects to the relational database that contains the table of exclusion terms. For more information, see Term Extraction Transformation.
Term Lookup transformation. Performs lookups by matching terms extracted from text with terms in a reference table. Also counts the number of times that a term is found. The transformation connects to the relational database that contains the lookup table. For more information, see Term Lookup Transformation.
OLE DB destination. Loads data into relational data sources by using an OLE DB provider. For more information, see OLE DB Destination.
|
.gif "Integration Services icon (small)")
Stay Up to Date with Integration Services