S


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, 3031

snapshot fact tables, 145146

snapshots, 229233

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, 255259

source queries

parameterized, 70–71

variable bound SQL source, 71–72

source server package execution, 378379

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, 3637

sqlldr command, 330331

SQLNCI (SQL Native Client), 61

SQLServer: SSIS Pipeline, 308309

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, 6667

staging table, 171

stand-alone server package execution, 380381

standard calculations for measures, 159

star dimension, 123

star schema, 4

streaming transformations, 363

strong name for assembly, 3031

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



Expert SQL Server 2005 Integration Services
Expert SQL Server 2005 Integration Services (Programmer to Programmer)
ISBN: 0470134119
EAN: 2147483647
Year: 2004
Pages: 111

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net