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