S
sample files
installing and using, 3–4
making changes to, 4
Scatter Plot viewer, 186
SCD (slowly changing dimensions), 384–397
adding new control flow for customer dimension updates, 392–393
adding new package for designing, 386–387
adding Percentage Sampling transformation, 387–388
adding SCD transformation, 394–397
connecting to SSMS to create simple database for a new dimension table, 388–389
creating new table within a SQL server destination, 389–391
initializing CurrentRecord column for SCD reference, 391–392
managing, 385–397
Slowly Changing Dimension Wizard, 385
types, 384
ScrapReason data source view, 18–19
Script component
basic programming model, 161
implementing, 162–169
implementing validation using Transformation script component, 164–169
InputBuffer, 161–162
Me.ComponentMetadata object, 162
Me.Variables object, 162
OutputBuffer, 161–162
reviewing a sample project, 162–169
understanding, 161–162
uses, 161
Script component, debugging, 198
Script Tasks
adding, 137
adding error-handling code, 146–148
creating new Script task and initiating code, 141–145
DTS objects and, 140
handling errors, 145–148
implementing, 141–160
modifying script to fire an event, 149–152
modifying variables at run time, 159–160
providing verbose information to Log File, 152–156
ScriptMain class, 139
understanding, 139–141
using variables, 156–159
XmlDocument class, 140
Script tasks
creating breakpoints in, 199
debugging, 190–197
reviewing state using VSA, 194–197
reviewing variables, 199
step through code in, 199
suspending execution, 199
walk through code using breakpoints, 190–194
Script Transformation Editor dialog box, 165, 168, 173
ScriptMain class, 139
Sequence Container
adding, 137
Sequence Container - File Exists
constraints, 136
Sequence containers, 120
ServerStorage, 291
setting breakpoints, 177–181
Show Me By tables, 360
Slowly Changing Dimension transformation, 72
Slowly Changing Dimension Wizard, 385
branches, 397
slowly changing dimensions (SCD), 384–397
adding new control flow for customer dimension updates, 392–393
adding new package for designing, 386–387
adding Percentage Sampling transformation, 387–388
adding SCD transformation, 394–397
connecting to SSMS to create simple database for a new dimension table, 388–389
creating new table within a SQL server destination, 389–391
initializing CurrentRecord column for SCD reference, 391–392
managing, 385–397
Slowly Changing Dimension Wizard, 385
types, 384
snowflake schema dimensions, 362
solution
defined, 32
solution designers
getting started, 2
Solution Explorer, 15–16, 76, 94, 101, 103, 110, 112
thumbtack icon, 17
Sort - Department Shift Employee task, 22
Sort transformation, 326
specifying new XML configuration file location, 202
split and join transformations, 71
conditional split, 71
lookup, 71
merge, 71
merge join, 71
multicast, 71
union all, 71
SQL Agent, 228–229
SQL Destination Editor, 98, 99
SQL Server Agent, 224–226
SQL Server configuration
creating, 215, 234
creating SQL Server table, 215–217
SQL Server destinations, 324–325
adding, 127–129, 138
testing DefaultBufferMaxRows and DefaultBufferSize, 325
using SQL Server connection manager, 325
SQL Server Import and Export Settings Wizard, 8
SQL Server Import and Export Wizard, 221–222
starting Server Wizard from BIDS, 221
starting Server Wizard from Management Studio, 221–222
SQL Server Integration Services project, 36–38
SQL Server Management Studio (SSMS), 9, 223
defined, 11
SQL Server table, 215–217
SQL Server tables, 209
SQL Server tables, configuration, 202
SQL statement parameters, mapping SSIS variables to, 247–248
SSIS
.dtsx (DST package) files, 9
BIDS, defined, 11
common applications, 4
components, 8–9
container types, 119–120
containers, defined, 10
control components, 114
control flow, 6
control flow elements, 114
control flow, defined, 10
data flow, 6
data flow, defined, 10
data pipeline, 6–7
data pipeline, defined, 11
Dtexec.exe, 9
Dtutil.exe, 9
event handler, 7–8
event handler, defined, 10
Integration Services Object Model, 9
Microsoft products and, 4
objects and process control components, 4–5
Package Migration Wizard, 10
packages, defined, 10
precedence constraints, defined, 10
process control, 5–8
SQL Server 2000DTS migration, 10
SQL Server environments and, 4
SSMS and, 9
SSMS, defined, 11
tasks, defined, 10
validation, 115
variables, defined, 10
SSIS design environment, 242–243
SSIS Designer, 188, 189, 190
breakpoints, 186
breakpoints and, 176
color coding, 180
Data Flow Task, 180
Hit Count type, 177
package execution, 183, 185
viewer types, 186–187
SSIS Designer toolbox, 114
SSIS engines, 310
Data Pipeline engine, 310
Runtime engine, 310
SSIS Import and Export Wizard, 26–29
creating tables in new database, 26–29
creating QuickStart solution to contain QuickStartIS SSIS project, 28
defined, 32
importing tables into new QuickStartODS database with a new package, 28–29
reviewing is2005sbs database using Management Studio, 27
running wizard in BIDS, 27
SSIS log reports, 337
SSIS package
defined, 10, 32
SSIS package design, 414–436
adding Data Flow tasks to child packages, 426
adding Execute Package tasks, 424–426
adding Row Count task and variable, 426–429
creating master-child package, 424–430
database snapshots, 420
designing for performance and maintenance, 420–422
defining best practices, 423
defining project folders, 433
designing for deployment, 434–435
disabling Execute Package task, 429–430
fast parse, 423
logging reports, 436
Lookup task vs. Merge Join, 419–420
managing buffers and memory, 434
managing CPU use, 434
managing multiple schemas, 435
managing performance and debugging, 433
managing SSIS application deployment, 434–436
opening project/building packages, 424
organizing package components, 430–434
OVAL principles, 414–417
using prefixes to identify package components, 430–433
using SSIS components, 417–423
using SSIS package configurations, 435
variables, 418–419
SSIS packages
storage, 14
SSIS project
creating new, 203–204, 234
SSIS Sample Solution.sln, 288, 302
SSIS transformations, 69–73
asynchronous transformations, 73
audit, 72
blocking transformations, 73
data quality transformations, 71–72
data-mining query, 72
data-mining transformations, 72
export column, 72
fuzzy grouping, 72
fuzzy lookup, 72
import column, 72
memory buffers, 69
partially blocking transformations, 73
row count, 72
row transformations, 70
rowset transformations, 70–71
slowly changing dimension, 72
split and join transformations, 71
synchronous transformations, 73
term extraction, 72
term lookup, 72
SSIS variables
mapping to SQL statement parameter, 285
SSIS variables, mapping to SQL statement parameters, 247–248
SSISDeploymentManifest file
build process and, 289
building SSIS sample project, 289
changing security levels, 293
creating deployment utility, 288
monitoring package installation, 304
Package Installation Wizard and, 307
push deployment and, 294
SSMS
defined, 11
staggered staging, 371
staging data from multiple sources, 370
staging schemes, 370–373
accumulated staging, 372
chunked accumulated staging, 373
destination considersations, 373
persisted staging, 371
staggered staging, 371
staging data from multiple sources, 370
staging tables
implementing, 369–370
uses, 369
star schema
defined, 366
star schema dimensions, 360–361
star schema model
characteristics, 368
defined, 411
dummy record members, 375
Step Into command, 191
Step Out command, 191
Step Over command, 191
Stop Debugging, 64
storing data in structures optimized for extraction and queries, 345
streaming transformation, 312
StreamWriter objects
Finally block and, 146
If statements and, 147
Try block and, 147
string data
validating, 174
surrogate keys, 352–353, 374
characteristics, 368
defined, 366, 411
defining dimension member rows, 374
uses, 374
synchronous and asynchronous processing, 312–313
synchronous tasks
defined, 437
synchronous transformation, 312
Synchronous transformations, 73
system administrators
getting started, 2
system requirements, 3
System.IO, 123
EAN: 2147483647
Pages: 152