T

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