Wrox Press Knight's Microsoft SQL Server 2012 Integration Services 24 Hour Trainer (2013).pdf

Knight’s Microsoft® sQL server® 2012

integration services 24-hour trainer

......................................................... xxv

preface

WeLcoMe to ssis ......................................................... 1

⊲

section i

instaLLation and getting started

Moving Data with the Import and Export Wizard .................11

Lesson 1

Installing SQL Server Integration Services ......................17

Lesson 2

Installing the Sample Databases ..............................21

Lesson 3

Creating a Solution and Project ..............................25

Lesson 4

Exploring SQL Server Data Tools .............................29

Lesson 5

Creating Your First Package .................................35

Lesson 6

Upgrading Packages to SQL Server 2012. . . . . . . . . . . . . . . . . . . . . . .41

Lesson 7

Upgrading to the Project Deployment Model .................. 47

Lesson 8

⊲

section 2

controL fLoW

Using Precedence Constraints ...............................59

Lesson 9

Manipulating Files with the File System Task ...................63

Lesson 10

Coding Custom Script Tasks ..................................71

Lesson 11

Using the Execute SQL Task .................................79

Lesson 12

Using the Execute Process Task ..............................87

Lesson 13

Using the Expression Task ...................................93

Lesson 14

Using the Send Mail Task ....................................99

Lesson 15

Using the FTP Task ........................................ 107

Lesson 16

Creating a Data Flow .......................................113

Lesson 17

⊲

section 3

data fLoW

Extracting Data from Sources ................................121

Lesson 18

Loading Data to a Destination ............................... 139

Lesson 19

Continues

Lesson 20

Changing Data Types with the Data Conversion Transform ......151

Lesson 21

Creating and Replacing Columns

with the Derived Column Transform ......................... 159

Lesson 22

Rolling Up Data with the Aggregate Transform ................ 167

Lesson 23

Ordering Data with the Sort Transform ....................... 173

Lesson 24

Joining Data with the Lookup Transform ..................... 179

Lesson 25

Auditing Data with the Row Count Transform ................. 189

Lesson 26

Combining Multiple Inputs with the Union All Transform ........ 193

Lesson 27

Cleansing Data with the Script Component ................... 197

Lesson 28

Separating Data with the Conditional Split Transform ..........203

Lesson 29

Altering Rows with the OLE DB Command Transform ...........211

Lesson 30

Handling Bad Data with the Fuzzy Lookup .................... 221

Lesson 31

Removing Duplicates with the Fuzzy Grouping Transform ...... 231

⊲

section 4

MaKing pacKages dynaMic

Lesson 32

Making a Package Dynamic with Variables ................... 241

Lesson 33

Making a Package Dynamic with Parameters .................249

Lesson 34

Making a Connection Dynamic with Expressions ..............255

Lesson 35

Making a Task Dynamic with Expressions .................... 261

⊲

section 5

coMMon etL scenarios

Lesson 36

Loading Data Incrementally .................................269

Lesson 37

Using the CDC Components in SSIS ......................... 281

Lesson 38

Using Data Quality Services ................................295

Lesson 39

Using the DQS Cleansing Transform .........................309

Lesson 40

Creating a Master Package ..................................317

⊲

section 6

containers

Lesson 41

Using Sequence Containers to Organize a Package ...........327

Lesson 42

Using For Loop Containers to Repeat Control Flow Tasks ...... 331

Lesson 43

Using the Foreach Loop Container

to Loop Through a Collection of Objects .....................337

⊲

section 7

configuring pacKages

Lesson 44

Easing Deployment with Coniguration Tables ................347

Lesson 45

Easing Deployment with Coniguration Files ..................357

Lesson 46

Coniguring Child Packages ................................365

⊲

section 8

troubLeshooting ssis

Lesson 47

Logging Package Data .....................................375

Lesson 48

Using Event Handlers ...................................... 381

Lesson 49

Troubleshooting Errors .....................................387

Lesson 50

Using Data Viewers ........................................393

Lesson 51

Using Breakpoints .........................................399

⊲

section 9

adMinistering ssis

Lesson 52

Creating and Coniguring the SSIS Catalog ...................407

Lesson 53

Deploying Packages to the Package Catalog ..................411

Lesson 54

Coniguring the Packages .................................. 415

Lesson 55

Coniguring the Service .................................... 421

Lesson 56

Securing SSIS Packages ...................................425

Lesson 57

Running SSIS Packages .................................... 431

Lesson 58

Running Packages in T-SQL and Debugging Packages .........437

Lesson 59

Scheduling Packages ......................................443

⊲

section 10 Loading a Warehouse

Lesson 60

Dimension Load ........................................... 451

Lesson 61

Fact Table Load ...........................................459

⊲

section 11 Wrap up and revieW

Lesson 62

Bringing It All Together .....................................465

appendix a

SSIS Component Crib Notes ................................473

appendix b

Problem and Solution Crib Notes ............................477

appendix c

What’s on the DVD? ....................................... 481

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika: