S

SaveCheckpoints property, 280, 411

SaveToDtsServer method, Application object, 493

SaveToSqlServer method, Application object, 493

SaveToSqlServerAs method, Application object, 493

SaveToXML method, Application object, 493

scalability

across machines, 307–310

definition of, 279

DTS and, 345–346

error outputs and, 301–304

memory requirements for, 304

staging data for, 305–310

scaling out

definition of, 304

memory requirements, 304

staging data, 305–310

scatter plot Data Viewer, 116

SCD (Slowly Changing Dimension) transformation

configuring, 190–192

definition of, 14, 107, 189–190

example using, 192–200

as row-based, non-blocking transformation, 320

Type I, II, III changes for, 190, 191

Script Component transformation

creating, 229–235

data cleansing and, 354

debugging, 235–237

definition of, 14, 107, 228

as row-based, non-blocking transformation, 320

as streaming, non-blocking transformation, 319

types of, 228

Script task

comments in, 217

configuring, 60–61, 216

creating, 213–218

debugging, 222–225

definition of, 6, 60, 213

events raised by, handling, 220–222

interacting with package, using Dts object, 218–219

logging for, 221, 222

.NET assemblies in, 225–226

variables, accessing from, 219–220

ScriptLanguage option, Script task, 60, 216

scripts. See also expressions

ActiveX, executing, 5, 60, 61

for Control Flow, 208

for custom transformations, using Script Component transformation, 14, 107, 228–235

for Data Flow, 208

executing with Script task, 60–61, 213–218

language for, 225

.NET assemblies in, 225–226

Structured Exception Handling (SEH) for, 227–228

types of, 208

scrubbing (cleansing) data

definition of, 354

example of

Conditional Split transformation, 130–131

connection for, 127–129

Data Flow for, 129

Derived Column transformation, 129–130

description of, 125–127

error handling, 134–138

Lookup transformation, 132

sending data to destination, 133–134

Union All transformation, 132–133

transformations used for, 354–357

SDLCs (Software Development Life Cycles)

definition of, 547, 548

history of, 548

types of, 549–550

security

for Access database, 250–251, 252

transferring logins to another database, 201–203, 204–205

for WMI connection, 514

SEH (Structured Exception Handling), 227–228

semi-blocking transformations

as asynchronous, 326

definition of, 321–322

Send Mail task, 6, 62

sequence container, 10, 73–74

serial processing, in Control Flow, 312

server folders, maintaining, 494

server instances, managing externally, 491–492

Server Name option, FTP task, 54

Server Port option, FTP task, 54

Server URL option, Web Service task, 63

Service option, Web Service task, 64

Service, SSIS, 3–4

Set Breakpoints dialog box, 408–409

SetComponentProperty method, 422, 486

SetExternalMetadataColumnDataType Properties method, 421

SetExternalMetadataColumnProperty method, 422

SetInputColumnProperty method, 422, 489

SetInputProperty method, 421

SetOutputColumnDataTypeProperties method, 421

SetOutputColumnProperty method, 422

SetOutputProperty method, 421

SetPackageRoles method, Application object, 495

settings. See configurations, package

SetUsageType method, 422–423, 447, 459

Shannon, Claude (application of Boole's theories), 548

shared connections

adding to Connection Manager, 36

creating, 120–122

residing in project, 28

shared properties for tasks, 43–44

Sharma, Ashvini (ASP.NET example), 540

shelving and unshelving code, 583–584

Shewhart, Walter (statistical process control), 548

Simplified Chinese operation, Character Map transformation, 94

.sln files, 29

Slowly Changing Dimension (SCD) transformation

configuring, 190–192

definition of, 14, 107, 189–190

example using, 192–200

as row-based, non-blocking transformation, 320

Type I, II, III changes for, 190, 191

SMO (SQL Management Object) enumerator, Foreach Loop container, 78

Software Development Life Cycles (SDLCs)

definition of, 547, 548

history of, 548

types of, 549–550

Solution Explorer Window

definition of, 28

projects, creating, 29

windows in, 29–32

solutions. See also projects

definition of, 28

files in, 29

Sort transformation

as blocking transformation, 322–323

configuring, 107–108

data cleansing and, 354

definition of, 14, 107

example using, 113

optimizing, 365–366

SortedData option, Bulk Insert task, 48

source adapters

building from pipeline components

AcquireConnections method, 433–435

CreateOutputAndMetaDataColumns method, 439–440

ParseTheFileAndAddToBuffer method, 441–443

PrimeOutput method, 440

ProvideComponentProperties method, 432–433

ReinitializeMetaData method, 438–439

requirements for, 418

Validate method, 435–438

definition of, 328

execution trees and, 330, 332

source code control. See Team System; Visual SourceSafe

Source Type Component, 228

sources, data

back pressure on, 359

Data Reader source, 12, 84

definition of, 11, 80

Excel source

configuring, 83

definition of, 12, 83

importing from, 244–249

uses of, 240

files as

Flat File source, 12, 84, 121–122

Raw File source, 12, 84

Flat File source

configuring, 84

creating, 121–122

definition of, 12, 84

heterogeneous, 239

list of, 12, 239

.NET provider as (Data Reader source), 12, 84

OLE DB source

configuring, 80–83

definition of, 12, 80

Raw File source, 12, 84

XML source

configuring, 84

definition of, 12, 84, 272

importing from, 272–278

SourceSafe

configuring, 550–552

definition of, 550

example using

branching, 565

checking in project, 555–556, 558, 559

checking out project, 554–555

creating project, 552–554

creating project in, 556–558

debugging project, 559–561

rolling back to earlier version, 561–564

Internet version of, 552

SourceType option, Analysis Services Execute DDL task, 70

SourceVariable option, Execute SQL task, 45

SPC (statistical process control), 548

spiral SDLCs, 549

spreadsheet, Excel, as data source

configuring, 83

definition of, 12, 83

importing from, 244–249

uses of, 240

SQL Agent, 664

SQL Command, passing parameter to, 255–258

SQL Management Object (SMO) enumerator, Foreach Loop container, 78

SQL objects, transferring, 204–205

SQL Profiler Log Provider, 506, 508

SQL Server 2000

DTS Designer, 3

executing DTS packages from, 5

global variables, 10, 37

history of, 1

native transactions in, 293, 299–300

Workgroup Edition, 16

SQL Server 2005, editions of, 16–17

SQL Server destination

configuring, 90

creating, 123–124

definition of, 12, 90

when to use, 246

SQL Server Integration Services (SSIS)

architecture of

components of, 3–5

containers, 10

destinations, 12

DSVs (data source views), 8–9

packages, 5

precedence constraints, 9–10

sources, 7, 11–12

tasks in, 5–6

transformations, 13–14

variables, 10–11

definition of, 1

design practices

data cleansing, 354–357

data correlation, 348–354

example of, 357–358

factors in, 346–347

RDBMS, reducing reliance on, 348

staging data, reducing, 348

staging environments, 359–360

synchronicity, limiting, 347

features in, based on SQL Server edition, 16–17

history of, 1–2

migrating DTS packages to, 371, 373–380

running DTS packages in, 380–383

using with external applications, 523

SQL Server Log Provider, 506, 508

SQL Server Management Studio

managing DTS packages with, 388–389

managing SSIS packages with, 386–388

SQL Server Mobile destination, 12, 90

SQL Server option, Execute Package task, 59

SQL Server option, package configuration, 509

SQL Server package store, object maintenance operations on, 492–496

SQL Server Reporting Services (SSRS), producing report from RSS, 524–532

SQL statements. See also DDL statements

executing for each input row with OLE DB Command transformation

configuring, 163

definition of, 13, 105, 162–163

example using, 163–165

optimizing, 363–364

as row-based, non-blocking transformation, 320

executing with Execute SQL task

checkpoint in, example of, 280–285

configuring, 44–45, 144–145

definition of, 6, 44

example using, 48–51, 124, 148–150

uses of, 144

SQLSourceType option, Execute SQL task, 45, 145

SQLStatement option, Execute SQL task, 45, 145, 299

SSIS clients, 3

SSIS Data Flow engine

definition of, 3, 311–312

optimizing, 361–363

SSIS Package Designer

Connection Managers tab, 36

Control Flow tab, 33–36

Data Flow tab, 38–39

definition of, 32

Event Handlers tab, 39–40

executing package, 41

Package Explorer tab, 40

Variables Window, 37–38

SSIS package store, object maintenance operations on, 492–496

SSIS runtime engine, 3, 5

SSIS Service, 3–4

SSIS (SQL Server Integration Services)

architecture of

components of, 3–5

containers, 10

destinations, 12

DSVs (data source views), 8–9

packages, 5

precedence constraints, 9–10

sources, 7, 11–12

tasks in, 5–6

transformations, 13–14

variables, 10–11

definition of, 1

design practices

data cleansing, 354–357

data correlation, 348–354

example of, 357–358

factors in, 346–347

RDBMS, reducing reliance on, 348

staging data, reducing, 348

staging environments, 359–360

synchronicity, limiting, 347

features in, based on SQL Server edition, 16–17

history of, 1–2

migrating DTS packages to, 371, 373–380

running DTS packages in, 380–383

using with external applications, 523

.SSISDeploymentManifest files, 25

.ssmssqlproj file, 384

SSRS (SQL Server Reporting Services), producing report from RSS, 524–532

staging data

Data Flow restart using, 305–307

definition of, 305

reducing, 348

scaling across machines using, 307–310

when to use, 359

staging environments, 359–360

Standard Edition, SQL Server 2005, 16

StandardErrorVariable option, Execute Process task, 51

StandardInputVariable option, Execute Process task, 51

StandardOutputVariable option, Execute Process task, 51

StartTime system variable, 38

statistical process control (SPC), 548

Stored Packages folder, 386

stored procedures

executing

for each input row, with OLE DB Command transformation, 13, 104, 162–165

with Execute SQL task, 44–45, 144–145, 148–150

transferring, 203–204

streaming transformations

definition of, 319

as synchronous, 326

striping (labeling) versions of code, 586–587

Structured Exception Handling (SEH), 227–228

SUBSTRING function, 129–130

Success constraint value, 9

SuccessValue option, Execute Process task, 52

Sum operation, Aggregate transformation, 91

synchronous processing

definition of, 315–316

in DTS, 345

limiting, 347

synchronous transformation outputs, 326–328

SynchronousInputID property, 328

system variables

definition of, 37

list of, 38

system.mdw file, 250