Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

Business Intelligence > BI Flash Cards - BI Flash Cards (2) > Flashcards

BI Flash Cards - BI Flash Cards (2)

1
Q

SSDT

A

Sql Server Data Tools

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

Can you build packages without connecting to an SQL instance?

A

Yes

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

SSIS architecture

A

Five main components
SSIS Service (for legacy ssis packages)
SSIS Runtime engine and runtime executables
SSIS Catalog
SSIS Data Flow engin and the Data Flow Components
SSIS Clients

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

What handles the operational aspects of SSIS

A

SSIS catalog and SSIS service (for legacy mode packages)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

Do you need the service to run packages?

A

no

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

What happens if you stop the service?

A

any packages running will stop

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

Package Catalog

A

Has new features/ like running packages with T-SQL

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

What runs the packages?

A

SSIS Runtime engine and complementary programs

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

Containers

A

Add structure and scope to packages

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

Tasks

A

functionality

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

Event Handlers

A

Respond to raised events in your package

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Precedence Constraints

A

Provide ordinal relationship between various items in your package/ the order of execution (green arrow)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

Package

A

Collection of tasks that execute in an orderly fashion

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

Package is saved where?

A

SQL Server (MSDB) or package catalog database. It can also be saved as a .dtsx file (xml structured)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

Data flow tab is enabled when?

A

When a data flow task is created

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

Control Flow tab does..

A

handles the main workflow of the package

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

Data flow tab does

A

handles data transformation

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

How many control flows does a package have

A

1

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

How many data flows?

A

many

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

Excel Destination

A

outputs data from data flow to excel which must already exist

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

SQL Server Destination

A

Package/ must be executed at the destination

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

Transformations run in-memory or on disk?

A

In memory. Incurring I/O on the page file on disk will slow things way down

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

Aggregate transform

A

Similar to group by

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

Fuzzy Grouping

A

Finding rows that are likely duplicates

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

Fuzzy Lookup

A

Matches and standardizes data

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

Ole DB Command

A

Executes an OLE DB command for each row in the data flow/ can be used to run an update or delete statement inside the data flow

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

Sort

A

Sorts by a column and removes exact duplicates

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

Project

A

Container of one or more packages and other SSIS components

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q

Project Deployment model

A

New model that enables many of the new SSIS 2012 features. Deploys the entire project of packages/ and packages can be configured by the DBA

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
30
Q

Package Deployment model

A

Old model where packages are deployed one at a time

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
31
Q

Solution

A

Container of one or more projects. Can house a C# project/ an SSRS project etc all together

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
32
Q

Can a solution house more than one type of project?

A

Yes

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
33
Q

Shared parameters and shared connections are only available in the

A

Project deployment model

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
34
Q

.dtsx

A

SSIS Package

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
35
Q

.ds

A

Shared Data Source file

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
36
Q

.sln

A

solution file containing one or more projects

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
37
Q

.dtproj

A

SSIS project file

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
38
Q

.params

A

shared parameter file

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
39
Q

.conmgr

A

shared connection manager

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
40
Q

Miscellaneous folder

A

holds documentation/ etc. can be checked into version control software

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
41
Q

How to convert back and forth between package and project deployment

A

Right click on the project and select “convert to (product or package)”

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
42
Q

Two tabs in SSIS are..

A

Control flow and data flow

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
43
Q

Data flow tab consists of what tasks?

A

sources/ transforms and destinations

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
44
Q

Enter debug mode by doing what?

A

Execute the package by right clicking on it in the solution explorer and hit execute package. Opens progress tab

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
45
Q

Progress tab

A

Where you go when you have a problem you need to debug

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
46
Q

Server Explorer is accessed by

A

Tools - connect to database

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
47
Q

Server explorer lets you

A

run queries/ create stored procedures/ redesign tables

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
48
Q

Connection manager

A

connection that can be leveraged and consumed once or many times in a package

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
49
Q

Can a connection manager connect to the internet? If so/ what ways?

A

Yes/ SMTP/ FTP/ and HTTP

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
50
Q

Are connection managers externally available to the DBA

A

Yes

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
51
Q

Externally available (connection manager) means

A

that the connection manager can be repointed

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
52
Q

How to create a project connection manager

A

right click on connection managers in the solution explorer

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
53
Q

convert a connection manager to a project cm

A

right click on it in the connection manager pane

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
54
Q

configure tasks in control flow by

A

double clicking

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
55
Q

Naming tasks

A

Example SCR - Encrypt file. Helps you self document your package and gives clarity to the logs

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
56
Q

How is a package encrypted by default

A

With your windows user key

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
57
Q

EncryptSensitiveWithUserKey

A

Encrypts credentials with your user key/ also Encryptallwithuserkey does the whole package

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
58
Q

EncryptSensitiveWithPassword

A

Adds a password so that anyone who runs the package has to have the password

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
59
Q

What helps with packages running in production that fail due to permissions

A

select encryptallwithpassword and pass the password in when scheduling or running

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
60
Q

Arrow color meanings in control flow

A

Green - success
red - failure
blue completion

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
61
Q

When upgrading SSIS

A

Connection strings are updated/ but if your package is dynamically changing the strings it won’t upgrade that
old packages aren’t converted to the new deployment model

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
62
Q

Arrow color meanings in data flow

A

red - bad data / green - good data

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
63
Q

Predecessor tasks

A

any task with a precedence constraint that is connected to another task

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
64
Q

How many evaluation operations are there in precedence constraints

A

4

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
65
Q

What are the different evaluation options in a precedence constraint

A

Constraint, Expression, Expression and Constraint, Expression or constraint

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
66
Q

ForceExecutionResult

A

A property in a task that overrides the execution result to a desired result

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
67
Q

Operation Property in File System Task

A

Decides what the file system task will do

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
68
Q

File System Task: IsSourcePathVariable

A

Is the source of the file system task a variable or a location

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
69
Q

File System Task: OverwriteDestination caveats

A

If you are copying a directory and set this to yes, you may overwrite a needed file, set to no and if the directory exists then your package will fail

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
70
Q

Will the rename file operation property move a file?

A

If the source is different from the destination

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
71
Q

UseDirectoryIfExists

A

File system task option under create directory. Either uses the directory or fails

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
72
Q

Script Task is broken down into

A

Script / General / Expressions node

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
73
Q

Dts.Variables() method

A

used to change package level variables

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
74
Q

Common Uses for Script tasks

A

Changing connection strings, whether a file exists, whether it is in use, if-then logic

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
75
Q

TypeConversionMode (Execute SQL Task)

A

Allows for datatypes to be converted for SSIS tasks

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
76
Q

Resultset property (exec sql task)

A

Can be XML, single row, none, which can be stored in an object variable

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
77
Q

Exec SQL task takes what as the SQL Source Type

A

Direct input (type it in), File connection (command saved in a file), and variable

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
78
Q

Parameter mapping in Sql Task starts at

A

zero

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
79
Q

SQL Task - isquerystoredprocedure

A

Only available on the ADO and ADO.net option

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
80
Q

BypassPrepare Query property

A

“compiles” the query so it doesn’t need to be analyzed every time it is used, won’t parse unless it is set to false

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
81
Q

Execute Process Task

A

Allow for windows and console applications to be used from within the package. Encryption is an example

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
82
Q

Expression Task

A

Manipulates variables in the control flow without having to use VB.NET, good for incrementing variables in a loop, like a set of files

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
83
Q

Send Mail Task

A

Information about the package passed through system variables or user defined variables

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
84
Q

Where to place the send mail task

A

At the end of the package, or in the event handler area

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
85
Q

Send Mail OnPreExecute

A

Will send an email saying when the package starts

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
86
Q

Send Mail OnPostExecute

A

Send mail indicating the start and end time of the package

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
87
Q

OnError or OnWarning

A

Send Mail task will send mail on these conditions

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
88
Q

Can the sendmail task send files

A

Yes

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
89
Q

SendMail connects how?

A

Through SMTP connection manager

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
90
Q

SendMail Credentials

A

Either sent through SSL or windows authentication

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
91
Q

SendMail message sourcetype Direct Input

A

Message is typed in the send mail task

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
92
Q

sendmail MessageSourceType File connection property setting

A

Message is saved in an external file

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
93
Q

sendmail message sourcetype variable property

A

Message is stored in a package variable

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
94
Q

FTP Task

A

Sends and receives files via FTP

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
95
Q

FTP Port

A

Usually 21

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
96
Q

FTP Chunk Size

A

The chunk of file (similar to packet) to be sent, helps avoid exceeding network limitations

97
Q

FTP Stop on Failure

A

Do you want the transfer to continue if part of it fails?

98
Q

FTP Operation options

A

Send, receive, create local directory, create remote directory, remove local/remote directory, delete local/remote files

99
Q

Data Flow Task

A

transfers data from source to destination and can transform data as needed

100
Q

A Dataflow task can write to multiple locations T/F

A

TRUE

101
Q

OLE DB Provider Is being deprecated because of…

A

Cloud computing, but you should still use it over ODBC because of higher functionality

102
Q

Most common source used

A

Ole db

103
Q

OLE DB has 4 data access modes

A

Table or view, table or view indicatied in a variable, sql results, or sql statement initiated in a variable

104
Q

Can SSIS pass a variable into an SQL query in the oledb source?

A

Yes, by using the ?

105
Q

three nodes in an oledb source

A

connection manager, columns (only check the ones you want), Error output (redirects data, ignores, or fails the component)

106
Q

Can you run a query against an excel source?

A

Yes, it treats excel like a database

107
Q

Native Excel Driver is

A

32 bit

108
Q

If you use the native excel driver

A

your packages will have to run in 32 bit mode, even on a 64 bit workstation

109
Q

to change to 32 bit mode

A

right click on the project file and select properties, select the debugging tab change run64bitruntime to false

110
Q

32 bit runtime on Excel Source only applies

A

in SSDT because it is a 32 bit application. On a server you can download the microsoft access database engine which includes 64 bit driver

111
Q

How do you correct datatypes in a flat file source

A

in the advanced tab of the flat file connection manager

112
Q

Destinations cannot be configured until

A

you drag the blue/red line to it

113
Q

in the destination flow if you don’t see the destination you want

A

simply uncheck “show only installed destinations”

114
Q

Destination Flow - Table lock

A

prevents anyone from accessing the table targeted, speeds up the load

115
Q

What does the sheet creation look like in an excel destination?

A

It looks like a create table command in SQL

116
Q

Data conversion transform does what

A

changes datatypes

117
Q

output alias in Data Conversion Transform

A

defaults to copy of…

118
Q

length precision and scale

A

used in a data conversion transform

119
Q

length

A

number of bytes to store the number, or the number of characters in a string

120
Q

precision

A

total number of digits

121
Q

scale

A

number of digits to the right of the decimal

122
Q

Fast parse is available in which transforms?

A

Flat file source and data conversion transform.

123
Q

Fast parse does what?

A

Disables verification of a particular column

124
Q

When would you use fast parse?

A

Only if you are sure of the quality of the data

125
Q

synchronous transform

A

all the rows don’t need to loaded into memory before being manipulated. Output is synchronous with input

126
Q

asynchronous transform

A

all the rows need to be loaded into memory before being manipulated, sort and aggregate are examples

127
Q

The function of a derived column transform is?

A

To either replace or create a column in the data stream

128
Q

Common uses for derived column transform

A

Auditing rows and editing incoming data using the SSIS expressions

129
Q

Variables are preceded with what sign?

A

@

130
Q

what function parses the middle of a string

A

substring

131
Q

Number of keys text box in the aggregate transform

A

put the number of groups you estimate will be created, found in the advanced tab

132
Q

Aggregate transform is what kind of transform?

A

Asynchronous and fully blocking

133
Q

Sort Transform

A

sorts by multiple columns, May choose which columns will pass through, can remove rows with duplicate sort values

134
Q

How do you tell the package that the data is presorted?

A

Right click the source, click advanced editor, go to the input and output properties and select oledb source output. In the properties pane, change issorted property to true, then you go to output columns and click on each sort column and put in the order

135
Q

Lookup transform can join how many data sets?

A

two

136
Q

no cache mode in lookup transform

A

High performance overhead on the system

137
Q

Full cached mode in lookup transform

A

One table loaded entirely from memory

138
Q

Is a lookup transform synchronous?

A

Yes

139
Q

Partial cache

A

Middle ground between no cache and full cache in a lookup transform

140
Q

Match output and no match output

A

Chooses which rows go to the next destination, the matching or the non matching

141
Q

Cache Connection Manager and Transform

A

Allows you to persist lookup information in the cache (same or different package) so you don’t have to reload the cache every time the lookup information is used

142
Q

Where does the cache transform store data

A

in either the pagefile (virtual memory) or in the permanent file storage

143
Q

Cache Connection Manager can…

A

reference the in-memory cache and read and write the cache to a disk based file

144
Q

How does a CCM optimize itself?

A

by allowing you to decide which columns are indexed and which are referenced

145
Q

how to use cached connection

A

open the cache transform and create a new cached connection manager, choose which columns are index columns and what position they are in the index order

146
Q

Script task in a data flow can perform these functions

A

Transform, source, or destination

147
Q

data flow script task use as a source

A

May need to add some business rules as it comes out of the source, sometimes necessary with COBOL files

148
Q

data flow script task as a destination

A

use the script to write to non OLEDB destinations, like XML or Sharepoint

149
Q

selecting readWrite for a variable does what

A

Locks during the duration of the execution

150
Q

Input Columns tab of Data Flow Script Task caveat

A

make sure to only mark columns you need access to writing as such, as this slow things down

151
Q

Can a script task in a data flow support multiple outputs?

A

Yes

152
Q

debugging script task in a data flow

A

Doesn’t allow for breakpoints, so you will need to use more arcane methods like msgboxes

153
Q

Conditional Split uses what language?

A

SSIS expression language

154
Q

What can you use as a temporary destination

A

a union all transform

155
Q

when should you use .NET in a package?

A

Only when necessary, as the person who inherits the package might not know it

156
Q

OLE DB Transform

A

executes an SQL statement for every row in the data stream, ie an update for every row!

157
Q

fuzzy lookup

A

minimizes unmatched rows with fuzzy logic also indicates the quality of the match

158
Q

when should you use a fuzzy lookup transform

A

only after you have used a regular lookup transform

159
Q

fuzzy lookup requires at least one field to be

A

a string to be mapped to string in the reference table

160
Q

fuzzy lookup passthrough

A

column is not analyzed but appears in the output stream

161
Q

fuzzy grouping

A

eliminates possible duplicates

162
Q

Is fuzzy lookup/grouping expensive?

A

Yes

163
Q

similarity threshold

A

likeliness of match in percentage

164
Q

what are the two outputs of a lookup

A

match output and no match output

165
Q

How do you use an expression for the variable?

A

Press f4 to bring up properties after selecting the variable

166
Q

How do you change the scope of a variable?

A

Move Variable icon in the variables dialog

167
Q

Fuzzy grouping key in and key out does what?

A

Key in is the value in, and the key out is the best match of the possible matches

168
Q

How do you use a conditional split to clean up fuzzy grouping?

A

Only save rows where the key in matches the key out

169
Q

Connection managers function only as sources

A

false, they are used in both sources and destinations

170
Q

How do you get a variable to evaluate as an expression?

A

select it and press f4, this brings up properties. Change evaluate as expression to true, then enter the expression in the expression property

171
Q

Are variable names case sensitive?

A

Yes

172
Q

parameter value in ssdt

A

design or default value, may be replaced prior to execution

173
Q

paramter sensitive property

A

marked as sensitive, its value is encrypted

174
Q

parameter required

A

you must provide the parameter prior to execution, rather than using the design parameter

175
Q

parameter scope

A

package scope or project scope

176
Q

To create a project scoped parameter

A

select project.params in the solution explorer

177
Q

Uses for parameters

A

Can be used to provide encrypted information, like passwords. Also, it is much easier to set runtime values for parameters over variables

178
Q

you can change any available property…

A

using an expression

179
Q

When using expressions for properties remember to

A

convert to the appropriate datatype

180
Q

Common use of expressions in a connection manager

A

is to import a collection of flat files using the same package (foreach loop container)

181
Q

how do you use an expression for multiple files

A

change the connection string between iterations

182
Q

To use a backslash in SSIS expressions

A

write a double backslash, even in quotes

183
Q

Can you use expressions in tasks?

A

Yes

184
Q

How do you use expressions in tasks?

A

select the task and hit f4, find the expression property, click the ellipsis

185
Q

What should you do before you write an expression?

A

Determine the datatype of the property

186
Q

Two ways to track changes to the source table

A

Keep a shadow copy of the table, or use change data capture

187
Q

how to handle deletes in ETL?

A

either scan the table or keep a list of keys.

188
Q

Establishing the start and end dates

A

Search the destination table for the max modified date, set the start date to now. Use andExecuteSQL task with resultset set to one row

189
Q

Logical vs Physical Deletes

A

Logical uses a delete flag, physical is just a delete

190
Q

Passing values from executeSQL task to data flow OLEDB source

A

uses parameters set by executesqltask

191
Q

CDC Control Task

A

Responsible for INITIAL loading of data and maintaining incremental load status by keeping with the log serial number associated with changes, does error handling

192
Q

CDC Source Task

A

Reads data that has been inserted, updated, or deleted within a range and provides those rows to downstream consumers

193
Q

CDC Splitter

A

Accepts dataflow from the CDC source task and splits it into three data flows, insert, update, delete

194
Q

How does CDC work?

A

By reading the log files

195
Q

Uses for CDC

A

data marts and data warehouses, also replication and auditing

196
Q

CDC uses how many packages?

A

two, one for initial, and one for incremental

197
Q

Data Quality Services

A

Helps improve data in the organization

198
Q

Master Package does what?

A

Runs child packages, in certain order

199
Q

Master Package variables

A

are controlled globally and shared with the child packages

200
Q

Master packages allow you to control parallelism t f

A

True

201
Q

sequence containers

A

group tasks to disable a part of the package that isn’t needed
Narrow the scope of a variable to just the container
collapsing and expanding the container to hide the tasks within
managing the properties of multiple tasks in one step by setting the container
using one method to make sure multiple tasks execute successfully
creating a transaction across data related tasks, not the entire package
event handlers on a single container

202
Q

for loop container

A

loops through control flow tasks

203
Q

Types of iterating containers

A

for and foreach

204
Q

for each has what types of objects?

A

File, Item (set manually), ADO (list of tables or rows), SSIS variable, XML nodelist, SMO (SQL Management Objects)

205
Q

Slowly changing dimension transform

A

Inserts and updates to dimension tables, only works with sql server

206
Q

Bulk Insert Task

A

Loads directly into SQL Server without transformation

207
Q

Execute Process Task

A

Opens application or batch file

208
Q

Expression task

A

builds and evaluates expressions that set variables at runtime

209
Q

Parameters can be scoped to

A

Package or project level

210
Q

How do you create a package level parameter

A

parameter tab

211
Q

How do you create a project level parameter?

A

Project.params under solution explorer

212
Q

Data Quality Services Checks for

A

Completeness, Consistency, Conformity, Validity, Accuracy, Duplication

213
Q

Data Quality Services keeps decisions in

A

DQKB

214
Q

DQKB

A

Data Quality Knowledge Base

215
Q

Data Quality Services has what components?

A

Data Quality Server and Client

216
Q

=

A

Set a variable to a value

217
Q

==

A

checks to see if there is equality between two values

218
Q

Configuration Tables are used for

A

Storing values to be used for production deployment, for example connection strings, etc

219
Q

Configuration tables are only available in the

A

package deployment model

220
Q

Best practice for configuration manager for configuration table

A

It should have its own connection manager, even if it is in the source database

221
Q

Many packages may use the same configuration table?

A

True

222
Q

How does each package filter out its configurations?

A

With the filter name on the configuration table

223
Q

How do you save configuration information in an SSIS package?

A

Configuration tables, or XML configuration files

224
Q

advantages of using XML to store configuration info

A

compatible with source control systems, easier than adding rows to a database

225
Q

using xml files for configuration requires which deployment model?

A

Package

226
Q

Ways to troubleshoot

A

Logging, event handlers, data viewers, breakpoints

227
Q

how to enable loggoing

A

Right click in the control flow and select logging

228
Q

places logs can be stored

A

Sql Server, SQl Server Profiler, Windows Event Log, Text File, XML file

229
Q

two common uses for an event handler

A

notification and logging

230
Q

how to create custom logging

A

Use Execute SQL Tasks in the Event Handlers tab

231
Q

When should data viewers be removed?

A

When sending a package to production

232
Q

How do you create a data viewer

A

Double click on the red or blue line connecting two tasks, select the dataviewer node

233
Q

Once in the dataviewer node of the data flow path editor, how do you proceed

A

enable the dataviewer with the checkbox, and then select the columns you want

234
Q

What does the detach button do in the data viewer

A

It allows the package to continue running without triggering the dataviewer

235
Q

How do breakpoints work

A

allow you to inspect the state of tasks, variables, and data at different points in the package

236
Q

To use configuration tables, your package must

A

use the package deployment model

237
Q

Configuration tables can be used for

A

any property for the package, connection, container, variable, or task

238
Q

Project deployment model uses what for configuration information

A

parameters and environments

239
Q

How to create configuration table for a package

A

Right click on the canvas in the control flow and choose package configurations

Business Intelligence (2 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions