Use LEFT and RIGHT arrow keys to navigate between flashcards;
Use UP and DOWN arrow keys to flip the card;
H to show hint;
A reads text to speech;
95 Cards in this Set
- Front
- Back
What does it mean to perform database oerations in atomic units?
|
Single statements or series of statements are either successful and affects the data or is not successful and the system returns to the state before the attempt. Each atomic unit is called a transaction.
|
|
What is MSDTC used for?
|
Microsoft Distributed Transaction Coordinator coordinates transactions.
|
|
What does the TransactionOption property do?
|
Required means the item must be successful for it to transact
supported means inherits the transaction setting of its parent (default) NotSupported prevents a task from participating in a transaction |
|
What does the SaveCheckpoints property do?
|
True allows SSIS to save checkpoitns during package execution.
|
|
What does the CheckpointUsage property do?
|
If set to IfExists it causes the package to run from the beginning if the checkpoitn file is not present or to run from the identified point if the file exists. If set to Always, the checkpoint file must be present or the package will not start.
|
|
If the TransactionOption of the pacakge is "Required" what happens to checkpoints?
|
They are not allowed
|
|
What property must be set to true on the package level if you want to write to the checkpoint file?
|
FailPackageOnFailure
|
|
At what level does logging need to be enabled?
|
The package level
but you can select individual containers and tasks from there |
|
What are the options under log provider type?
|
Log events destination locations, which include text files, SQL Server, windows event log, SQL server profiler, XML file
|
|
What log events would you use to determine how long it takes for a task to run?
|
OnPreExecute and OnPostExecute
|
|
If you select SQL Server how will the log details be stored?
|
In a table called dbo.sysssislog
|
|
What do blue lines indicate?
|
Task completion
|
|
What do dotted lines indicate?
|
"Or" constraints
|
|
What are the two primary evaluation indicators for precidence?
|
Constraints (execution status of upstream tasks) and Expressions (boolean evaluators written in SSIS expression language)
|
|
What are the two paths in the data flow?
|
Data paths where rows that have successfully gone through a component go and error paths where rows that fail in a component go
|
|
What are the four types of data viewers and how are they used?
|
Grid shows the raw data in columns and rows (most common to identify row-level data)
Histogram is used for numeric columns (Shows distribution across a range) Scatter plot reveals data relationships between two numeric columns highlighting outliers column chart displays summary values of a column |
|
When does the event handler OnError run?
|
When a task or container reports an error
|
|
When does the event handler OnExecStatusChanged run?
|
For all taks and containers when the execution status changes to In Process, Success, or Failed
|
|
When does the event handler OnInformation run?
|
When SSIS outputs information messages during the validation and execution of a task or container
|
|
When does the event handler OnPostExecute run?
|
Runs after a container or task successfully completes
|
|
When does the event handler OnPostValidate run?
|
After a containter or task has been successfully validated
|
|
When does the event handler OnPreExecute run?
|
Just before a container or task is executed
|
|
When does the event handler OnProgress run?
|
Executed when a progress message is sent by the SSIS engine, indicating tangible advancement of the task or container
|
|
When does the event handler OnQueryCancel run?
|
Invoked when an execute SQL task is cancelled through a manual intervention, such as topping the package
|
|
When does the event handler OnTaskFailed run?
|
Similar to OnError, but runs when a task fails rather than each time an error occurs
|
|
When does the event handler OnVariableValueChanged run?
|
Runs when the value changes in a variable for which the RaiseChangeEvent property is set to true
|
|
When does the event handler OnWarning run?
|
When a task returns a warning event such as a column not being used in a data flow
|
|
Suppose you want to deal with an event for an entire container except one task in a container. What would you do?
|
Turn DisableEventHandlers property to True
|
|
What is included in connection properties?
|
Connection string, server name, connection user name and password
|
|
What is included in Package variable properties?
|
Variable values, variable descriptions, and the Raise Change Event property
|
|
What is included in Package Properties?
|
Any property that you defined at the package level, such as package-level transaction options, checkpoint settings, and security settings.
|
|
What is included in task and containter properties?
|
Transactoins settings at the container or task level and specific properties directly related to the task or container type
|
|
How are package configurations stored?
|
As CML in the packages .dtsx file, along with the rest o fthe package.
|
|
What important part of a package cannot be updated by configurations?
|
Data Flow component properties. You have to use variables or SSIS expressions to modify these.
|
|
What is the default for package configurations?
|
Package configurations are turned off by default
|
|
What are the five types of package configurations?
|
XMLE Configuration file
environmental variable registry entry partent package variable sql server |
|
Describe an XML Configuration file package configuration:
|
Stores configuration settings in an XML file in the file system. You can store multiple configurations in an XML file.
|
|
Describe an Environmental Variable package configuration:
|
Saves the configuration information inside the system's global variables collection, which is called an environmental variable. When you add an environmental variable configuration, you can choose only one property for each environmental variable configuration.
|
|
Describe a Registry Entry package configuration:
|
Lets you save package properties and settings insdie your computer's registry. You can select multiple configuration settings at a time and store them in the registry.
|
|
Describe a parent package variable configuration type:
|
provides a way to inheit 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 Variable configuration. With this configuraiton type, you can choose only one package property setting at a time.
|
|
Describe a SQL Server configuration type:
|
Uses a SQL Server table to store the package setting information. This configuration setting lets you store multiple confiurations in a single table.
|
|
What are the two types of XML File Configuration?
|
Specify configuration settings directly by typing in a configuration file name
Sotre configuration location in an environmental variable |
|
How do you create an environment variable?
|
Control panel-->System-->Advanced System Settings-->Environment Variables
|
|
In addition to setting direct or indirect configuration settings, describe the three SQL Server Configuration Properties:
|
Connection is a Sql-Server based connection from the OLE DB Connection Manager
Configuration table is the table in whcih the configurations will reside. It has strict column name and data type requirements Configuration filter allows you to share the same table among multiple SQL Server configurations. The value here is used in the configuration filter column in the underlying table. |
|
Which package configuration types allow you to include more than one property per configuration and which ones don't?
|
XML Configuration File and SQL Server configuration do
Environmental variable, registry entry, and parent package variable dont |
|
What are the two most common uses of expressions?
|
Data flow conditional split or derived column
|
|
If you used What does it mean to perform database oerations in atomic units?
|
Single statements or series of statements are either successful and affects the data or is not successful and the system returns to the state before the attempt. Each atomic unit is called a transaction.
|
|
What is MSDTC used for?
|
Microsoft Distributed Transaction Coordinator coordinates transactions.
|
|
What does the TransactionOption property do?
|
Required means the item must be successful for it to transact
supported means inherits the transaction setting of its parent (default) NotSupported prevents a task from participating in a transaction |
|
What does the SaveCheckpoints property do?
|
True allows SSIS to save checkpoitns during package execution.
|
|
What does the CheckpointUsage property do?
|
If set to IfExists it causes the package to run from the beginning if the checkpoitn file is not present or to run from the identified point if the file exists. If set to Always, the checkpoint file must be present or the package will not start.
|
|
If the TransactionOption of the pacakge is "Required" what happens to checkpoints?
|
They are not allowed
|
|
What property must be set to true on the package level if you want to write to the checkpoint file?
|
FailPackageOnFailure
|
|
At what level does logging need to be enabled?
|
The package level
but you can select individual containers and tasks from there |
|
What are the options under log provider type?
|
Log events destination locations, which include text files, SQL Server, windows event log, SQL server profiler, XML file
|
|
What log events would you use to determine how long it takes for a task to run?
|
OnPreExecute and OnPostExecute
|
|
If you select SQL Server how will the log details be stored?
|
In a table called dbo.sysssislog
|
|
What do blue lines indicate?
|
Task completion
|
|
What do dotted lines indicate?
|
"Or" constraints
|
|
What are the two primary evaluation indicators for precidence?
|
Constraints (execution status of upstream tasks) and Expressions (boolean evaluators written in SSIS expression language)
|
|
What are the two paths in the data flow?
|
Data paths where rows that have successfully gone through a component go and error paths where rows that fail in a component go
|
|
What are the four types of data viewers and how are they used?
|
Grid shows the raw data in columns and rows (most common to identify row-level data)
Histogram is used for numeric columns (Shows distribution across a range) Scatter plot reveals data relationships between two numeric columns highlighting outliers column chart displays summary values of a column |
|
When does the event handler OnError run?
|
When a task or container reports an error
|
|
When does the event handler OnExecStatusChanged run?
|
For all taks and containers when the execution status changes to In Process, Success, or Failed
|
|
When does the event handler OnInformation run?
|
When SSIS outputs information messages during the validation and execution of a task or container
|
|
When does the event handler OnPostExecute run?
|
Runs after a container or task successfully completes
|
|
When does the event handler OnPostValidate run?
|
After a containter or task has been successfully validated
|
|
When does the event handler OnPreExecute run?
|
Just before a container or task is executed
|
|
When does the event handler OnProgress run?
|
Executed when a progress message is sent by the SSIS engine, indicating tangible advancement of the task or container
|
|
When does the event handler OnQueryCancel run?
|
Invoked when an execute SQL task is cancelled through a manual intervention, such as topping the package
|
|
When does the event handler OnTaskFailed run?
|
Similar to OnError, but runs when a task fails rather than each time an error occurs
|
|
When does the event handler OnVariableValueChanged run?
|
Runs when the value changes in a variable for which the RaiseChangeEvent property is set to true
|
|
When does the event handler OnWarning run?
|
When a task returns a warning event such as a column not being used in a data flow
|
|
Suppose you want to deal with an event for an entire container except one task in a container. What would you do?
|
Turn DisableEventHandlers property to True
|
|
Why would you use an expression in a derived column transformation?
|
to generate the value of a new oclumn or change the value of an existing column
|
|
Why would you use an expression in a in a conditional split transformation?
|
To evaluate Boolean result of conditions that determine to which output a row should be sent
|
|
Why would you use an expression in a For Loop Containter?
|
To set the initial condition of the loop and the change for each loop and then to determine whether the loop is complete.
|
|
Why would you use an expression in a variable expression?
|
Variable 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 se the EvaluateAsExpression property to true and then enter an expression in the expression property
|
|
Why would you use an expression in a property expression?
|
Property expressions are a way that SSIS can dynamically update the valudee of properties while a package is running. Instead of using a hard-coded value for a property, you can use an expression to generate a value.
|
|
How would you interpret A?B:C
|
If A is true, B. If A is false, C
|
|
When does a package read and use configuration entries?
|
The start of the package execution.
|
|
When are property expressions evaluated as a package is running?
|
Unlike configuration entries that are read at the start of the package execution, property expressions are updated when the property is accessed by the package during package execution so it can change during package execution.
|
|
What are the two options for package storage?
|
SSIS deployment to the file system
SSIS deployment to SQL server where it is tored in a table named dbo.sysssispackages |
|
What does the package deployment utility do?
|
Helps you create a deployment set which includes packages (and configuration files), miscellaneous files, and the project deployment manifest
|
|
What is the project deployment manifest?
|
The installer kit configuration file
|
|
How do you enable the deployment utility for an SSIS project?
|
Go to project properties and change the CreateDeploymentUtility from False to True. Be sure to set it back to false after its built or else it will overwrite every time you execute.
|
|
How do you launch an installer kit?
|
Run [project name].SSISDeploymentManifest
|
|
Does a deployment automatically update configuration file locations?
|
Yes
|
|
What does DTUtil do?
|
Performs package-management tasks such as deployment
|
|
What does the DTUtil paramater /exists do?
|
Checks for the existence of a package
|
|
What does the DTUtil paramater /sign do?
|
Digitally signs a package
|
|
What does the DTUtil paramater /fcreate do?
|
Creates and manages folder for the SSIS package store and in the SQL Server msdb database
|
|
What are two command-line scheduling and utility programs that can execute DTUtil?
|
SQL Server Agent
Windows Scheduler |
|
What is the meaning of the five DTUtil Command Return Codes?
|
0 Success
1 Failure 4 Package not found 5 Package cannot load 6 Bad Syntax |
|
Can you use DTUtil to delete a package that is deployed to SQL Server?
|
Yes
|