D

data. See also files; text

bad data

error outputs for, 301–304

finding with Fuzzy Grouping transformation, 13, 101, 178–182

finding with Fuzzy Lookup transformation, 13, 101, 173–178

cleansing (scrubbing)

definition of, 354

example of, Conditional Split transformation, 130–131

example of, connection for, 127–129

example of, Data Flow for, 129

example of, Derived Column transformation, 129–130

example of, description, 125–127

example of, error handling, 134–138

example of, Lookup transformation, 132

example of, sending data to destination, 133–134

example of, Union All transformation, 132–133

transformations used for, 354–357

correlation of, 348–354

loading into a table with Bulk Insert task, 5, 46–51

loading into an OLE DB destination with Data Flow task, 5, 51

staging data, 305–310

Data Access Mode option

OLE DB destination, 88

OLE DB source, 80

data buffers, 317–318

Data Conversion transformation

configuring, 97–98

data cleansing and, 354

definition of, 13, 97

as streaming, non-blocking transformation, 319

Data Flow. See also transformations

adapters and, 328

bound to controller flow by Data Flow task, 32

checkpoints not possible in, 280

compared to Control Flow, 312–314

creating, 123–124

definition of, 312, 317

dividing

to allow checkpoints, 305

to scale across machines, 307–310

elements of

destinations, 12

sources, 11–12

transformations, 13–14

execution trees for, 330–335

memory buffers used by, 317–318

monitoring execution of, 335–339

optimizing, 359

restarting, 305–307, 359

Data Flow engine

definition of, 3, 311–312

optimizing, 361–363

Data Flow scripting, 208

Data Flow tab, SSIS Package Designer, 38–39, 123

Data Flow task

definition of, 5, 51

example using, 122

Data Mining Model Training destination, 12, 86

Data Mining Query Component transformation, 17

Data Mining Query task

available only in Enterprise Edition, 17

definition of, 5, 71

Data Mining Query transformation

definition of, 13, 98–99

as semi-blocking transformation, 321

Data Mining Training Destination transformation, 17

Data Pump task, 342–343

Data Reader source, 12, 84

data source connections

for Access database, 251–252

acquiring programmatically, 425, 433–435, 455–456

for Analysis Services Execute DDL task, 70

for Bulk Insert task, 46

configuring offline, 7

creating, 36, 120–122

for Execute SQL task, 45, 145

for File System task, 53

releasing programmatically, 425

for runtime components, 482–484

shared connections

adding to Connection Manager, 36

creating, 120–122

residing in project, 28

data source views (DSVs), 8–9. See also Data Viewers

Data Source Wizard, 120

data sources

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

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

data stores for package configuration, 509

Data Transformation Services (DTS)

architecture of, 342–343

definition of, 341–342

history of, 1

limitations of, 345–346

managing packages with SQL Server Management Studio, 388–389

migrating packages to SSIS, 371, 373–380

packages from SQL Server 2000, executing, 5

processing practices of, 343–345

relationship to controller flow, 33

running packages in SSIS, 380–383

data types

BLOB data types

exporting, 100–101, 159–160

importing, 101, 150–157

changing in DDL, 123

converting for columns, 13, 97–98

default, not using, 611

mapping from one back-end to another, 384

setting for pipeline components, 421

specifying for connections, 122

SQL Server and, 246

Data Viewers. See also DSVs (data source views)

for debugging, 235–237

definition of, 116

types of, 116

using, 116–117

when to use, 611

Database Password, Access, 250, 252

database, payment processing case study

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

databases

logins for, transferring, 201–203, 204–205

moving or copying, 201–202

objects in, transferring, 204–205

DataFileType option, Bulk Insert task, 46

DataReader destination

configuring, 86–87

definition of, 12, 86

David, Jean-Luc (Professional Visual Studio 2005 Team System), 547

DDL statements. See also SQL statements

changing data types in, 123

executing in Analysis Services, 5, 70

debugging

pipeline components, 461–466

for Script Component, 235–237

for Script task, 222–225

DelayValidation property, 43

Delete method, IDtsComponentUI interface, 472

DeleteInput method, 421, 448

DeleteOutput method, 421, 448

denormalizing data. See Pivot transformation

deploying packages, 587–591

Deployment Wizard, 588–589

Derived Column transformation

configuring, 99

data cleansing and, 354, 355–356

definition of, 13, 99

example using, 111

expressions in, 211–213

as streaming, non-blocking transformation, 319

DescribeRedirectedErrorCode method, 425

Description property, 43–44

Design assembly, 471

design surface, 14

design-time components, pipeline

debugging, 462–463

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

SetOutputColumnDataType Properties method, 421

SetOutputColumnProperty method, 422

SetOutputProperty method, 421

SetUsageType method, 422–423

Validate method, 420

destination adapters

building from pipeline components, 419, 454–461

definition of, 328

Destination option, WMI Data Reader task, 66, 513

Destination Type Component, 228

DestinationConnection option, File System task, 53

destinations

configuring, 84–85

Data Mining Model Training destination, 12, 86

DataReader destination

configuring, 86–87

definition of, 12, 86

definition of, 12, 84

Dimension Processing destination

configuring, 87

definition of, 12, 87

staging and, 305

Excel destination

configuring, 87

definition of, 12, 87

exporting to, 240–244

Flat File destination

configuring, 88

definition of, 12, 88

example using, 114

list of, 12

OLE DB destination

configuring, 88–89

definition of, 12, 88

when to use, 246

Partition Processing destination

definition of, 12, 87

staging and, 305

Raw File destination, 12, 89

Recordset destination, 12, 89

SQL Server destination

configuring, 90

creating, 123–124

definition of, 12, 90

when to use, 246

SQL Server Mobile destination, 12, 90

DestinationTable option, Bulk Insert task, 46

DestinationType option, WMI Data Reader task, 513

Dimension Processing destination

configuring, 87

definition of, 12, 87

staging and, 305

dimension tables. See Slowly Changing Dimension (SCD) transformation

directories, manipulating, 6, 52–58

dirty data, cleansing (scrubbing)

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

Disable property, 43

Distributed Transaction Coordinator transactions. See DTC transactions

document templates, MSF Agile, 571

Drop Temp Table task, 157–159

.ds files, 29, 384

.dsv files, 29

DSVs (data source views), 8–9. See also Data Viewers

DTC (Distributed Transaction Coordinator) transactions

definition of, 293

single package

multiple transactions using, 296–298

single transaction using, 293–296

two packages, one transaction using, 298–299

DTExec program, 466

.dtproj files, 29, 384

DTS (Data Transformation Services)

architecture of, 342–343

definition of, 341–342

history of, 1

limitations of, 345–346

managing packages with SQL Server Management Studio, 388–389

migrating packages to SSIS, 371, 373–380

packages from SQL Server 2000, executing, 5

processing practices of, 343–345

relationship to controller flow, 33

running packages in SSIS, 380–383

DTS Designer, relationship to BIDS, 3

DTS Migration Wizard, 371, 373–380

Dts object, 218–219

DTSClient DLL, 543

.dtsConfig file, 384

DTSFileConnectionUsageType enumeration, 434–435

DtsPipelineComponent attribute

definition of, 427–428

UITypeName property, 468, 475

DTSPipelineWrap assembly, 426, 471

DTSRuntimeWrap assembly, 426

dtswizard.exe tool, 19

.dtsx files

definition of, 29

saving packages as, 5, 384

.dwproj file, 384

dynamic packages, 140–142

dynamic properties. See property expressions

Dynamic Properties task, 1