scalability
data flow optimization
data flow properties for, 369–371
destination optimization, 371–376
pipeline optimization, 365–369
package execution strategies for
Bulk Insert Task, 377
data flows, 378
destination server package execution, 379–380
distributed server package execution, 381–382
Execute SQL Task, 377
source server package execution, 378–379
stand-alone server package execution, 380–381
storage and execution locations, 376–377
performance evaluations for
memory utilization, 357–360
SQL operations compared to Data Flow Tasks, 360–362
SSIS Service status, checking, 354
task duration, checking, 355–356
of SCD (Slowly Changing Dimension) Wizard, 120, 133
SCD (Slowly Changing Dimension) Wizard
advantages of, 120
Changing Attributes Updates Output, 114
CurrentRowWhere property, 118
date dimension tables with, 128–132
dimension changes, specifying, 108–112
dimension table and keys, specifying, 107–108
Fixed Attribute Output, 120
Historical Attribute Inserts Output, 116–118
index optimizations for, 121
inferred members, specifying, 112–118
invoking, 107
limitations of, 120–121
locking contentions with, 121
New Output, 116
parent-child dimension tables with, 125–128
scalability of, 120, 133
snowflake dimension tables with, 123–125
SQLCommand property, 118
Unchanged Output, 119
update optimizations for, 121–122
scheduling DTS packages, 336
scheduling packages
with proxy accounts, 303–305
with SQL Server Agent, 301–303
troubleshooting, 303
when using central SSIS server, 284
scope of variable, 23
Script Component Source, 312
Script Components
for data grain changes, 175
data profiling in, 49–52
encrypting data in, 39–48
language used in, 24
retrieving variables in, 26
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
ScriptMain class, 24
security
firewall port settings, 299
package roles for, 297–299
Package Store location affecting, 287, 299
passwords for packages, setting, 299, 301
re-encrypting packages, 300
for SSAS integration, 198
Send Mail tasks, migrating to SSIS, 339
Sequence container, 19
Simple Load (UCASE).dts package, 341
Simple Load.dts package, 341
slowly changing dimension.
See also SCD (Slowly Changing Dimension) Wizard
custom package for
correlating source with destination, 133–137
dimension changes for, 137–141
definition of, 102
SN utility, creating key file using, 30–31
snapshot fact tables, 145–146
snapshots, 229–233
snowflake dimension tables
definition of, 94
with SCD Wizard, 123–125
Sort transformation
alternative to, 366
for data grain changes, 174
source adapters
creating connections for, 57
DataReader source adapter, 60
definition of, 57, 58
delaying validation of, 64
Excel source adapter, 61–63
flat file source adapter
change identifier columns in, 82–83
using, 58–60
OLE DB source adapter
parameterized source queries, 70–71
using, 60–61
raw file source adapter, 63
XML source adapter, 63
source control integration for packages, 255–259
source queries
parameterized, 70–71
variable bound SQL source, 71–72
source server package execution, 378–379
sources, data
correlating to destination, for incremental extraction, 83–86
DB2 sources, 331–332
joining to dimension data, 133–136
mapping to dimension and fact tables, 144
multiple record types in, 316–323
Oracle sources
code page warnings from, 328
Oracle client network components for, 326
parameterized queries with, 328–330
providers for, 327–328
reading from, 327–330
writing to, 330–331
preparing for dimension ETL, 97–101
raw files, 323–325
sharing among packages, 57
supported by SSIS, 56
VSAM sources, 332
SourceType property, Analysis Services Execute DDL Task, 203
sp_start_job stored procedure, 381
SQL
Data Flow Tasks compared to, 360–362
queries
in Data Flow task, 8–11
parameterized source queries, 70–71
variable bound SQL source, 71–72
SQL Native Client (SQLNCI), 61
SQL Server Agent
distributed package execution using, 381
scheduling packages using, 284, 301–303
SQL Server Integration Services Report Pack, 237
SQL Server 2000 DTS
migrating to SSIS
DTS Migration Wizard for, 339–340, 341–344
handling exceptions from, 344–352
SQL Server Upgrade Advisor, 340–341
success rates of, 339–340
packages
administration of, 335–339
editing, 336
importing, 336
running, 336–339
scheduling, 336
SQL Server 2005 Analysis Services objects. See SSAS objects
SQL Server 2005 Integration Services (SSIS)
book about, 7, 21
extending, approaches for, 21–22
uses of, 4
SQL Server 2005 Reporting Service (SSRS), 356
SQLCommand property, SCD Wizard, 118
SqlConnection object, 36–37
sqlldr command, 330–331
SQLNCI (SQL Native Client), 61
SQLServer: SSIS Pipeline, 308–309
SSAS (SQL Server 2005 Analysis Services) objects
AMO (Analysis Management Objects) for, 197
attributes of, affected by ETL processing, 222–223
connection for, 198
control flow tasks for
Analysis Services Execute DDL Task, 197, 203–204
Analysis Services Processing Task, 197, 199–203
Execute Command Task, 197
cubes, processing, 192, 220–221
data flow destinations for
Dimension Processing destination, 197, 205–207
Partition Processing destination, 197, 207–208
data mining objects
data flow features for, 361
processing of, 192, 196
database, 192
dimensions, 192–194
measure groups
definition of, 123
processing of, 192
partitions
creating and modifying, 197–198
processing of, 194–195
processing methods for, 197
scheduling processing of, 191–192
security for, 198
types of objects processed, 192
XMLA scripts for, 197, 209–211
SSIS Package Store
definition of, 281
location of
determining, 283
on file system, 287–288
in msdb database, 287–288
SSIS Service for, 281–283
SSIS Service
clustering, 285–286
configuring, 281–283
highly available, benefits of, 285
required for Package Store, 281
running packages with, 282
on separate, central server, 283–284
status of, checking, 354
stopping packages with, 282
SSIS (SQL Server 2005 Integration Services)
book about, 7, 21
extending, approaches for, 21–22
uses of, 4
SSRS (SQL Server 2005 Reporting Service), 356
staging data, 66–67
staging table, 171
stand-alone server package execution, 380–381
standard calculations for measures, 159
star dimension, 123
star schema, 4
streaming transformations, 363
strong name for assembly, 30–31
strong typing, 29
SuppressFinalize() method, garbage collector, 29
surrogate key of dimension table
acquiring for fact table ETL
with Execute SQL Task, 149
Fuzzy Lookup transformation for, 149, 155–159
for historical changes, 148
for inferred members, 148, 154
for large dimensions, 154
for late-arriving facts, 148, 154
Lookup transformation for, 149–154
Merge Join transformation for, 149, 155
for missing dimension records, 148
definition of, 95
in fact table, 143–144
symmetric encryption, 39
synchronizing data (data integration), 6
synchronous transformation, 45
sysdtslog90 table, 234