Shared Flashcard Set

Details

MCTS 70-448 - SSIS Terms
Microsoft MCTS Exam 70-448 - SSIS Terms
130
Other
Professional
05/31/2010
Click here to study/print these flashcards.

Create your own flash cards! Sign up here.

Additional Other Flashcards

 

 

Cards

Term
ActiveX Script Task
 Definition
Runs Microsoft Visual Basic Scripting Edition (VBScript) and JScript code and is included mainly for legacy support when a Data Transformation Services (DTS) package is migrated to SSIS.
Term
Analysis Services Execute DDL Task
 Definition
"Runs XML for Analysis (XMLA) code against an SSAS database. XMLA is the data definition language (DDL) for SSAS; therefore, this task lets you perform common structure changes such as adding partitions to cubes. "
Term
Analysis Services Processing Task
 Definition
Allows the processing of SSAS objects through an SSIS package.
Term
Bulk Insert Task
 Definition
Allows the execution of bulk copy operations for SQL Server. This task works only against a SQL Server Database Engine.
Term
Data Flow Task
 Definition
Allows data processing from sources to destinations. Lesson 3 in this chapter covers the data flow task in more detail.
Term
Data Mining Query Task
 Definition
Performs data mining queries and lets you capture the results for analysis.
Term
Data Profiling Task
 Definition
"Allows the analysis of source data for patterns, missing data, candidate keys, and statistics. These results typically inform developers about what logic to include in their SSIS packages based on their data needs. "
Term
Execute DTS 2000 Package Task
 Definition
Runs a DTS package within SSIS.
Term
Execute Package Task
 Definition
Runs other SSIS packages either deployed to SQL Server or in the file system.
Term
Execute Process Task
 Definition
Runs a command-line operation such as program or batch file execution.
Term
Execute SQL Task
 Definition
Runs SQL code against any underlying database connection in the SQL language of the connected database engine.
Term
File System Task
 Definition
"Lets you copy, move, and delete files as well as perform other file and folder operations. "
Term
FTP Task
 Definition
Sends and receives files between the file system and an FTP server and performs simple file and folder operations on the FTP server.
Term
Message Queue Task
 Definition
Integrates with Message Queuing (MSMQ) on a server running Windows to read and send messages.
Term
Script Task
 Definition
Runs Microsoft Visual Basic 2008 or Microsoft Visual C# 2008 within an SSIS package.
Term
Send Mail Task
 Definition
Sends an e-mail message through an SMTP server.
Term
Transfer [Object] Task
 Definition
"Tasks that copy SQL Server objects from one system to another, including databases, SQL Server Agent jobs, error messages, logins, master stored procedures, and database-level objects. "
Term
Web Service Task
 Definition
Lets you connect to a Web service to send or receive information.
Term
WMI Data Reader Task
 Definition
Lets you run a Windows Management Instrumentation (WMI) query against the operating system to capture server informa-tion.
Term
WMI Event Watcher Task
 Definition
Waits for a particular event before executing.
Term
XML Task
 Definition
"Combines, queries, and differentiates multiple XML files on the server. "
Term
Column Null Ratio Profile
 Definition
Evaluates the column and returns the percent of NULLs in the column relative to the total number of rows in the table.
Term
Column Statistics Profile
 Definition
"For numeric and datetime columns, returns the spread and averages of the values. "
Term
Column Value Distribution Profile
 Definition
Identifies the uniqueness of the values in a column across all the rows for that column.
Term
Column Length Distribution Profile
 Definition
Shows the various value lengths for a text column and the percentage of all the rows that each length takes up.
Term
Column Pattern Profile
 Definition
Displays any patterns found in the column data and returns the regular expression pattern that matches the pattern.
Term
Candidate Key Profile
 Definition
Identifies one or more columns that are unique across all the rows; the percentage of uniqueness is shown.
Term
Functional Dependency Profile
 Definition
"Lists any columns that have value dependencies on other columns within the table, where a value from one column is found only when the value of another column is distinct. "
Term
ADO.NET Source
 Definition
Provides connections to tables or queries through an ADO.NET provider.
Term
Excel Source
 Definition
Allows extractions from an Excel worksheet defined in an Excel file.
Term
Flat File Source
 Definition
Connects to a delimited or fixed-width file created with different code pages.
Term
OLE DB Source
 Definition
"Connects to installed OLE DB providers, such as SQL Server, Access, SSAS, and Oracle. "
Term
Raw File Source
 Definition
Stores native SSIS data in a binary file type useful for data staging.
Term
XML Source
 Definition
Allows raw data to be extracted from an XML file; requires an XML schema to define data associations.
Term
ADO.NET Destination
 Definition
Allows insertion of data by using an ADO.NET managed provider.
Term
Data Mining Model Training
 Definition
Lets you pass data from the data flow into a data min-ing model in SSAS.
Term
DataReader Destination
 Definition
Lets you put data in an ADO.NET recordset that can be programmatically referenced.
Term
Dimension Processing
 Definition
Lets SSAS dimensions be processed directly from data flowing through the data flow.
Term
Excel Destination
 Definition
"Used for inserting data into Excel, including Excel 2007. "
Term
Flat File Destination
 Definition
Allows insertion of data to a flat file such as a comma-delimited or tab-delimited file.
Term
OLE DB Destination
 Definition
Uses the OLE DB provider to insert rows into a destina-tion system that allows an OLE DB connection.
Term
Partition Processing
 Definition
Allows SSAS partitions to be processed directly from data flowing through the data flow.
Term
Raw File Destination
 Definition
Stores native SSIS data in a binary file type useful for data staging.
Term
Recordset Destination
 Definition
Takes the data flow data and creates a recordset in a package variable of type object.
Term
SQL Server Compact Destina-tion
 Definition
Lets you send data to a mobile device running SQL Mobile.
Term
SQL Server Destination
 Definition
Provides a high-speed destination specific to SQL Server 2008 if the package is running on SQL Server.
Term
Audit
 Definition
Adds additional columns to each row based on system package variables such as ExecutionStartTime and PackageName.
Term
Cache Transform
 Definition
Allows data that will be used in a Lookup Transformation to be cached and available for multiple Lookup components.
Term
Character Map
 Definition
"Performs common text operations, such as Uppercase, and al-lows advanced linguistic bit conversion operations. "
Term
Copy Column
 Definition
Duplicates column values in each row to new named columns.
Term
Data Conversion
 Definition
"Creates new columns in each row based on new data types converted from other columns—for example, converting text to numeric. "
Term
Derived Column
 Definition
"Uses the SSIS Expression language to perform in-place calcula-tions on existing values; alternatively, allows the addition of new columns based on expressions and calculations from other columns and variables. "
Term
Export Column
 Definition
"Exports binary large object (BLOB) columns, one row at a time, to a file. "
Term
Import Column
 Definition
Loads binary files such as images into the pipeline; intended for a BLOB data type destination.
Term
Row Count
 Definition
Tracks the number of rows that flow through the transformation and stores the number in a package variable after the final row.
Term
Conditional Split
 Definition
"Routes or filters data based on Boolean expressions to one or more outputs, from which each row can be sent out only one output path."
Term
Lookup
 Definition
Allows matching between pipeline column values to external data-base tables; additional columns can be added to the data flow from the external table.
Term
Merge
 Definition
"Combines the rows of two similar sorted inputs, one on top of the other, based on a defined sort key."
Term
Merge Join
 Definition
"Joins the rows of two sorted inputs based on a defined join column(s), adding columns from each source."
Term
Multicast
 Definition
"Generates one or more identical outputs, from which every row is sent out every output."
Term
Union All
 Definition
"Combines one or more similar inputs, stacking rows one on top of another, based on matching columns. "
Term
Aggregate
 Definition
"Associates records based on defined groupings and generates ag-gregations such as SUM, MAX, MIN, and COUNT. "
Term
Percent Sampling
 Definition
Filters the input rows by allowing only a defined percent to be passed to the output path.
Term
Pivot
 Definition
Takes multiple input rows and pivots the rows to generate an out-put with more columns based on the original row values.
Term
Row Sampling
 Definition
"Outputs a fixed number of rows, sampling the data from the entire input, no matter how much larger than the defined output the input is. "
Term
Sort
 Definition
Orders the input based on defined sort columns and sort direction and allows the removal of duplicates across the sort columns.
Term
Unpivot
 Definition
"Takes a single row and outputs multiple rows, moving column values to the new row based on defined columns. "
Term
OLE DB Command
 Definition
"Performs database operations such as updates and deletes, one row at a time, based on mapped parameters from input rows. "
Term
Slowly Changing Dimen-sion
 Definition
"Processes dimension changes, including tracking dimension history and updating dimension values. The Slowly Changing Dimension Transformation handles these common dimen-sion change types: Historical Attributes, Fixed Attributes, and Changing Attributes. "
Term
Data Mining Query
 Definition
Applies input rows against a data mining model for predic-tion.
Term
Fuzzy Grouping
 Definition
"Associates column values with a set of rows based on similar-ity, for data cleansing. "
Term
Fuzzy Lookup
 Definition
Joins a data flow input to a reference table based on column similarity. The Similarity Threshold setting specifies the close-ness of allowed matches—a high setting means that match-ing values are closer in similarity.
Term
Script Component
 Definition
"Provides VB.NET scripting capabilities against rows, columns, inputs, and outputs in the data flow pipeline. "
Term
Term Extraction
 Definition
Analyzes text input columns for English nouns and noun phrases.
Term
Term Lookup
 Definition
Analyzes text input columns against a user-defined set of words for association.
Term
OnError
 Definition
Runs when a task or container reports an error.
Term
OnExecStatusChanged
 Definition
"Runs for all tasks and containers when the execution status changes to In Process, Success, or Failed."
Term
OnInformation
 Definition
Runs when SSIS outputs information messages during the validation and execution of a task or container.
Term
OnPostExecute
 Definition
Runs after a container or task successfully completes.
Term
OnPostValidate
 Definition
Executes after a container or task has successfully been validated.
Term
OnPreExecute
 Definition
Runs just before a container or task is executed.
Term
OnPreValidate
 Definition
Runs before the component is validated by the engine.
Term
OnProgress
 Definition
"Executed when a progress message is sent by the SSIS engine, indicating tangible advancement of the task or container."
Term
OnQueryCancel
 Definition
"Invoked when an Execute SQL Task is cancelled through manual intervention, such as stopping the package."
Term
OnTaskFailed
 Definition
"Similar to OnError, but runs when a task fails rather than each time an error occurs."
Term
OnVariableValueChanged
 Definition
Runs when the value changes in a variable for which the RaiseChangeEvent property is set to True.
Term
OnWarning
 Definition
Runs when a task returns a warning event such as a column not being used in a data flow.
Term
XML Configuration File
 Definition
Stores configuration settings in an XML file in the file system. Select XML Configuration File if you are comfortable working with configuration files and your project requirements let you store configuration information in a file system file. You can store multiple configurations in a single XML file.
Term
Environment Variable
 Definition
"Saves the configuration information inside the system’s global variables collection, which is called an environment variable. When you add an Environment Variable configuration, you can choose only one property for each Environment Variable con-figuration. "
Term
Registry Entry
 Definition
Lets you save package properties and settings inside your com-puter’s registry. You can select multiple configuration settings at a time and store them in the registry.
Term
Parent Package Variable
 Definition
"Provides a way to inherit the value of a variable from a parent package. When a package is executed from another SSIS package by using the Execute Package Task, the values of its variables are available to the child package through the Parent Package Vari-able configuration. With this configuration type, you can choose only one package property setting at a time. "
Term
SQL Server
 Definition
Uses a SQL Server table to store the package setting information. This configuration setting lets you store multiple configurations in a single table.
Term
Connection
 Definition
"This must be a SQL Server–based connection that sets the server and database in which your configurations will be stored and from which they will be read. If a connection does not exist, you can click New next to Connection to open the Configure OLE DB Connection Manager dialog box. "
Term
Configuration Table
 Definition
"The configuration table is the table in which the configura-tions will reside. This table has strict column name and data type requirements. To create the table, you can click New next to Configuration Table to open the Create Table dialog box in which you can change the name of the table and execute the table-creation statement on the connection that you specified. "
Term
Configuration Filter
 Definition
"Multiple SQL Server configurations can share the same table, which you can specify by using the Configuration Filter drop-down list. You can either enter a new filter or choose one that already exists in the table. The name you select or enter for this property is used as a value in the Configuration Filter column in the underlying table. "
Term
Derived Column
 Definition
The Derived Column Transformation uses the SSIS expression
Term
Transformation
 Definition
language to generate the value of a new column or change the value of an existing column.
Term
Conditional Split
 Definition
The Conditional Split Transformation uses the SSIS expression
Term
Transformation
 Definition
language to evaluate the Boolean result of conditions that determine to which output a row should be sent.
Term
For Loop Container
 Definition
The For Loop Container uses an expression to set the initial condition of the loop and the change for each loop and then to determine whether the loop is complete. This uses a combina-tion of Boolean checks and set operations.
Term
Variable Expression
 Definition
"Variables can be dynamically set through an SSIS expression. This is done when a variable is selected in the Variables window and the Properties window is open. To use this capability, you need to set the EvaluateAsExpression property to True and then enter an expression in the Expression property. "
Term
Property Expressions
 Definition
"Property expressions are a way that SSIS can dynamically update the value of properties while a package is running. Instead of using a hard-coded value for a property, you can use an expression to generate the value. These properties include package properties at the control flow level and container and task properties and are accessed through the editors and the Properties window. We will discuss property expressions at the end of this section. "
Term
/COPY
 Definition
Copies packages from a source to a destination
Term
/MOVE
 Definition
Moves packages from a source to a destination
Term
/DELETE
 Definition
Deletes packages from a destination
Term
/EXISTS
 Definition
Checks for the existence of a package
Term
/ENCRYPT
 Definition
Encrypts an existing package
Term
/DECRYPT
 Definition
Decrypts a package by using a package password
Term
/SIGN
 Definition
Digitally signs a package
Term
/FCREATE
 Definition
Creates and manages folders for the SSIS Package Store and in the SQL Server msdb database
Term
db_ssisadmin
 Definition
View all packages. Execute all packages. Export all packages. Execute all packages in SQL Server Agent. Delete all packages. Change all package roles. Import packages.
Term
db_ssisltduser
 Definition
View user’s packages. Execute user’s packages. Export user’s packages. Delete user’s packages. Change user’s package roles. Import packages.
Term
db_ssisoperator
 Definition
View all packages. Execute all packages. Export all packages. Execute all packages in SQL Server Agent.
Term
Windows Admin
 Definition
View execution details of currently running packages. Stop currently running packages.
Term
DontSaveSensitive
 Definition
"Sensitive information is not saved in the package at all. Each time the package is reopened in BIDS, the connection passwords must be reentered. For package execution, the password needs to be a package configuration or passed to the package stored in through the command-line setting. This is the recommended setting if you are exclusively using Windows Authentication for your connections."
Term
EncryptAllWithPassword
 Definition
"The entire package is encrypted with a password that is set in the PackagePassword property. To open the package in BIDS, you need to enter the correct package password, and the package cannot be executed without passing the password to the package at execution. "
Term
EncryptAllWithUserKey
 Definition
"The entire package is encrypted based on the current user and computer. Only the user who last designed or exported the package can design or execute the package. A package cannot be opened in BIDS unless it is opened by the user who developed it, on the com-puter on which it was developed. The package can be executed only through the same user account on the same computer. "
Term
EncryptSensitiveWithPassword
 Definition
"The sensitive information in the package is encrypted based on a package password, which is set in the PackagePassword property. Each time the package is reopened, a password must be provided. If the password is not provided, the package will open, but all sensitive data will be replaced with blanks. If a user attempts to execute the package without a password, the execution will fail."
Term
EncryptSensitiveWithUserKey
 Definition
"The sensitive information (connection passwords) in the package is encrypted based on the current user and computer. If the same user reopens the package, nothing is changed. If a different user opens the pack-age, all the sensitive information is cleared out. Other users can still design or execute the package, but they need to enter the password or pass the password into the package through the command line or SSIS configurations."
Term
ServerStorage
 Definition
"This setting can be used only when a package is stored in a SQL Server database. Server storage means that nothing in the package definition is en-crypted. Instead, the entire package is protected by using a SQL Server database role. This option is not supported when a package is saved to the file system. "
Term
General
 Definition
"As described earlier in steps 2 and 3, you can specify the package location and name on this property page. "
Term
Configurations
 Definition
"In addition to the configurations already defined in the package, you can add XML configurations to a package at execution time through this property page. These XML configurations are added to the configurations that currently exist in the package. "
Term
Command Files
 Definition
"The DTExec.exe parameters can be stored in a separate file, and the Command Files properties let you specify the text file that will contain the parameters. "
Term
Connection Managers
 Definition
"For the selected package, the connections can be over-written at run time with new connection information. Select the connections to overwrite, and then manually alter the connection string. "
Term
Execution Options
 Definition
"The Execution Options property page allows advanced execution details for the package execution, including the following: n Validate Package Without Executing , n Maximum Concurrent Executables, n Enable Package Checkpoints, n Browse To Checkpoint File, n Override Restart Options, n Restart Options "
Term
Reporting
 Definition
The Reporting properties define the information returned to the command-line output. The selected events determine the level of information returned.
Term
Logging
 Definition
Log providers can be added at run time.
Term
Set Values
 Definition
"The Set Values properties let you override package properties, including package variables. "
Term
Command Line
 Definition
"The Command Line property page shows the command line that will be passed to DTExec. All the options you configured on the other property pages will be ac-counted for in the command-line text, which you can edit manually. "
Term
Verification
 Definition
"On this property page, you set verification options that allow a package to run, using the following: n Execute Only Signed Packages n Verify Package Build n Build n Verify Package ID n Package ID n Verify Version ID n Version ID "
Supporting users have an ad free experience!