P

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