Table lock option, OLE DB destination, 375
tables.
See also fact tables
audit table, 68
date dimension tables, with SCD Wizard, 128–132
dimension tables
business key (candidate key) in, 95
correspondence to source tables, 97
definition of, 4
indexes for, optimizing, 121
loading of, updating inferred members during, 106–107
mapping source files to, 144
surrogate key for, 95, 143–144
updates to, optimizing, 121–122
multiple, in one file, 316–323
partition tables, performance affected by, 372
staging table, 171
Task Scheduler, scheduling packages using, 284
TaskHost container, 19
tasks
Analysis Services Execute DDL Task
for partition management, 217–218
processing SSAS objects using, 197, 203–204
Analysis Services Processing Task, 197, 199–203
Bulk Insert Task
location where executed, 377
migrating to SSIS, 339
configuring, 8–14
containers for, 19–20
creating, 8
Data Flow Task
memory utilization of, 357–360
source adapter in, 57
SQL operations compared to, 360–362
duration of, checking, 355–356
Execute Command Task, 197
Execute DDL Task, 214–216
Execute Process Task
ASCMD.exe executable used in, 210
migrating to SSIS, 339
Execute SQL Task
for acquiring dimension surrogate key, 149
location where executed, 377
migrating to SSIS, 339
precedence constraints for, 15–17
Script Tasks
accessing and updating connections from, 36–37
calling assembly from, 33
code reusability with, 22
definition of, 22
language used in, 24
raising errors in, 37–39
retrieving variables in, 24–26
32-bit and 64-bit issues regarding, 307
user-defined variables for, 22–24
WMI Data Reader Task, 251
WMI Data Watcher Task, 251
team development of packages, 255–259
templates for packages, 260–261
Term Extraction transformation, for data grain changes, 174
Term Lookup transformation, for data grain changes, 175
32-bit and 64-bit issues, 306–308
thread mapping model, 363
3DES (Triple DES), 40
timestamp datatype, incremental extraction using, 79–81
tnsnames.ora file, 326
Tracking History (Change Type 2)
definition of, 102, 103–104
with fact table ETL, 148
implications on SSAS attributes, 222–223
Transact SQL (TSQL), 2
transaction fact tables, 145
transactional system, 4
transactions
definition of, 242–243
enabling, 243
implementing, 243–245
performance of, 368–369
using with checkpoint files, 245
Transform Data tasks, migrating to SSIS, 339, 348–350
transformations.
See also ETL (extraction, transformation, and loading);
SCD (Slowly Changing Dimension) Wizard
Aggregate transformation
for data grain changes, 174
for maximum change identifier value, 77
for measure calculations, 160
asynchronous transformation, 45, 360
blocking transformations, 363, 365
Conditional Split transformation
for change identifier columns, 82–83
for data grain changes, 174
for identifying fact table changes, 163, 165–166
for separating record types, 319
for separating valid and invalid data, 51
configuring in Data Flow task, 9–13
Data Conversion transformation, 97, 98–99, 319–321
Derived Column transformation
for data grain changes, 174
for measure calculations, 159, 160–161
preparing source data for dimension ETL, 97, 99–100
Fuzzy Lookup transformation, for acquiring dimension surrogate key, 149, 155–159
in-line transformation processing, 360
Lookup transformation
for acquiring dimension surrogate key, 149–154
cached, 136–137
correlating data using, 133, 134
for data grain changes, 174
data preparation using, 101
definition of, 97
for identifying fact table changes, 163–166
optimizing, 366–367
Merge Join transformation
for acquiring dimension surrogate key, 149, 155
for data grain changes, 174
for identifying fact table changes, 167–169
joining source data to dimension data, 133–136
Merge transformation, for data grain changes, 174
Multicast transformation
change identifier columns using, 77
data staging using, 66–67, 368
partially blocking transformations, 363
Pivot transformation
for data grain changes, 174
null values handled by, 314
row transformations, 363
Sort transformation
alternative to, 366
for data grain changes, 174
streaming transformations, 363
synchronous transformation, 45
Term Extraction transformation, for data grain changes, 174
Term Lookup transformation, for data grain changes, 175
Union All transformation
for breaking up execution tree, 367–368
for data grain changes, 174
Unpivot transformation
for creating rows from columns, 312–313
for data grain changes, 174
Triple DES (3DES), 40
troubleshooting
code page warnings from Oracle sources, 328
event handling
auditing through, 226–229
for checkpoint files, 241–242
disabling, 229
error events, raising in Script Tasks, 37–39
exception handling, when migrating DTS to SSIS, 344–352
snapshots used with, 229–233
types of event handlers, 225–226
for variable value changing, 24
Package Store location affecting, 288
scheduling packages, 303
TSQL (Transact SQL), 2