package configurations
changing at runtime, 148–150
creating, 510–511
nonstatic configuration, 146
programming, 511–512
types of, 509
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
Package Explorer tab, SSIS Package Designer, 40
Package Installation Wizard
definition of, 25
installing to SSIS instance or File System, 589
Package Migration Wizard, 371, 373–380
Package object, 492
PackageID option, Audit transformation, 93
PackageID system variable, 38
PackageName option, Audit transformation, 93
PackageName system variable, 38
packages. See also projects
annotations for, 35–36
backups for, 385
checking whether it exists, 493
configuring at runtime, 509–512
creating, 27–28, 119–124
definition of, 5
deploying, 587–591
dynamic, 140–142
executing, 27, 41, 125
executing external program from, with Execute Process task, 6, 51–52
executing from within another package, with Execute Package task, 6, 59
execution time of, 314–315
failing when task fails, 44
files in, 29
importing to Integration Services instance, 589–591
loading, 493
log providers for, 505–508
logging, 412–416
managing
with Application object, 493
with SQL Server Management Studio, 386–388
migrating DTS packages to SSIS, 371, 373–380
monitoring, 495–496
optimizing processing of, 360–366
parallel processing in, 314–315
removing, 493
restarting
checkpoints for, 280–290, 411–412
reasons for, 279–280
roles, assigning to, 495
running across machines, 307–310
running DTS packages in SSIS, 380–383
saving, 27, 125, 493, 611
saving as .dtsx file, 5, 384
saving in msdb database, 5, 384–385
storage of, 384–385
viewing in Solution Explorer, 28
parallel processing
in Control Flow, 312, 315–316
in Data Flow, 313
in package, 314–315
parameter, passing to SQL Command, 255–258
Parent Package Variable option, package configuration, 509
ParseTheFileAndAddToBuffer method, 441–443
Partition Processing destination
definition of, 12, 87
staging and, 305
paths. See also precedence constraints
definition of, 38
specifying based on conditions, 13, 94–97, 130–131
patterns in data, finding. See Fuzzy Grouping transformation
payment method options, payment processing case study
check payments (lockbox), 594
e-mail payments (E-Pay), 594, 596
list of, 594
PayPal payments, 594, 596
wire payments (ACH), 594
payment processing case study
ACH file, 595, 596, 599–600
CaseStudy_Load package
ACH Control Flow, 635–638
ACH processing, 643–645
ACH validation, 639–643
description of, 595, 610–611
e-mail Data Flow processing, 648–651
e-mail payment processing, 645–647
File System tasks for, 612–616
Lockbox Control Flow, 616–622
Lockbox processing, 631–635
Lockbox validation, 622–631
naming conventions for, 611
setting up, 612–616
testing, 651
CaseStudy_Process package
description of, 595–596, 651–652
high-confidence matching, Data Flow for, 654–659
medium-confidence matching, Data Flow for, 659–662
results of, interpreting, 663–664
setting up, 652–654
database for
BankBatchDetail table, 606–607
creating, 602–603
Customer table, 603–604
CustomerLookUp table, 605–606
ErrorDetail table, 608
GLAccountData table, 607–608
Invoice table, 604–605
model of, 601–602
usp_BankBatch_Add table, 608–609
usp_BankBatch_Balance table, 610
usp_BankBatchDetail_Match table, 609–610
description of, 593–594
e-mail transactions, 601
file locations, 599
Lockbox file, 595, 596, 600–601
payment method options
check payments (lockbox), 594
e-mail payments (E-Pay), 594, 596
list of, 594
PayPal payments, 594, 596
wire payments (ACH), 594
PayPal transactions, 601
running in SQL Agent, 664
solution for, 594–598
PayPal transactions, payment processing case study, 601
PDSA (plan, do, study, act), 548
Percentage Sampling transformation
configuring, 105–106
definition of, 13, 105
as streaming, non-blocking transformation, 319
Performance Monitor (PerfMon), 366–369
PerformUpgrade method, 422
phrases
frequency of, determining with Term Extraction transformation, 14, 108–109, 165–170
matching with text, using Term Lookup transformation, 14, 109, 171–173
similar
finding with Fuzzy Grouping transformation, 13, 101, 178–182
finding with Fuzzy Lookup transformation, 13, 101, 173–178
pipeline
building components, 426–432
connection-time components, 425
debugging components
definition of, 461
design-time, 462–463
runtime, 463–466
definition of, 417
design-time components
definition of, 419–420
DeleteInput method, 421
DeleteOutput method, 421
InsertInput method, 421
InsertOutput method, 421
MapInputColumn method, 421
MapOutputColumn method, 421
OnInputPathAttached method, 423
OnOutputPathAttached method, 423
PerformUpgrade method, 422
ProvideComponentProperties method, 420
RegisterEvents method, 422
RegisterLogEntries method, 422
ReinitializeMetaData method, 420
SetComponentProperty method, 422
SetExternalMetadataColumnDataType Properties method, 421
SetExternalMetadataColumnProperty method, 422
SetInputColumnProperty method, 422
SetInputProperty method, 421
SetOutputColumnDataTypeProperties method, 421
SetOutputColumnProperty method, 422
SetOutputProperty method, 421
SetUsageType method, 422–423
Validate method, 420
example components
destination adapter, 419, 454–461
source adapter, 418, 432–443
transformation, 418, 443–454
runtime components
Cleanup method, 424
definition of, 423
DescribeRedirectedErrorCode method, 425
PostExecute method, 424
PreExecute method, 424
PrepareForExecute method, 423
PrimeOutput method, 424
ProcessInput method, 424
user interface (UI) for components
assembly for, 468–471
column properties for, 488–489
component properties for, 484–486
configuring component for, 468, 475–476
error handling, 486–488
form for, 468, 476–481
IDtsComponentUI interface, implementing, 467–468, 471–475
procedure for, 467–468
runtime connections for, 482–484
pipeline logging events, 335–339
pipeline performance monitoring, 366–369
PipelineComponent class, 419, 427
PipelineComponents folder, 428, 429
PipelineExecutionPlan event, 335–336, 337–339
PipelineExecutionTrees event, 335–337
PipelineHost assembly, 426
Pivot transformation
configuring, 182
data cleansing and, 354, 355
definition of, 13, 106–107, 182
example using, 183–186
as semi-blocking transformation, 321
plan, do, study, act (PDSA), 548
PostExecute method, 424
precedence constraints. See also paths
arrow indicators for, 391–392
conditional expressions for, 9–10, 34, 393–396
constraint values, 9, 34
creating, 33–34
definition of, 5, 9–10
error handling using, 391–398
multiple, 397–398
PrecompileScriptIntoBinaryCode option, Script task, 60, 216
PreExecute method, 424, 440, 448–450, 459–460
PrepareForExecute method, 423
PrimeOutput method, 424, 440, 453–454
private buffers, 367, 368
ProcessInput method, 424, 450–454, 460–461
Professional InfoPath 2003 (Williams and Greborio), 532
Professional Visual Studio 2005 Team System (David), 547
Project Portal, 573
projects. See also packages
creating, 29
definition of, 28
files in, 29
Team System container for, creating, 567–568
viewing in Solution Explorer, 28
properties for tasks
setting to expressions, 72, 146–148, 208–210
shared, list of, 43–44
validating at runtime, 43
Properties Window, 30–31
property expressions
creating, 72, 146–148, 208–210
definition of, 72, 146, 208
Property Expressions Editor, 72, 146–147, 209
ProvideComponentProperties method
building destination adapter using, 454–455
building source adapter using, 432–433
building transformation using, 443–445
definition of, 420
publications
An Investigation of the Laws of Thought, on which are founded the Mathematical Theories of Logic and Probabilities (Boole), 548
Professional InfoPath 2003 (Williams; Greborio), 532
Professional Visual Studio 2005 Team System (David), 547