31 Days of SSIS

31 Days of SSIS

Version 1.0

Jason Strate \stratesql

JASON STRATE?SQL SERVER MVP

CONTENTS

Forward.................................................................................................................................................................... 8 Downloads ........................................................................................................................................................... 8 Biography ............................................................................................................................................................. 8

31 Days of SSIS ? The Introduction............................................................................................................................ 9 The 31 Days of SSIS Posts ..................................................................................................................................... 9

Raw Files Are Awesome.......................................................................................................................................... 11 Raw File Format ................................................................................................................................................. 11 Raw File Format Destination............................................................................................................................... 12 Raw File Format Source ...................................................................................................................................... 14 Raw File Wrap-Up .............................................................................................................................................. 15 References ......................................................................................................................................................... 15

Using Breakpoints................................................................................................................................................... 16 Breakpoint Demonstration ................................................................................................................................. 16 Breakpoint Wrap-Up .......................................................................................................................................... 19 References ......................................................................................................................................................... 19

Data Flow Breakpoints?!......................................................................................................................................... 20 Data Viewers Definition...................................................................................................................................... 20 Data Viewer Setup.............................................................................................................................................. 20 Data Viewer In Use ............................................................................................................................................. 22 Data Viewers Wrap-Up....................................................................................................................................... 23

Folder Structures .................................................................................................................................................... 25 The Folder Structure........................................................................................................................................... 25 The Folder Purpose ............................................................................................................................................ 25 Folder Structure Wrap-Up .................................................................................................................................. 26

One Variable to Rule Them All ................................................................................................................................ 27 Variable Expressions........................................................................................................................................... 27 The One Variable................................................................................................................................................ 28 Variable Wrap-Up............................................................................................................................................... 29 References ......................................................................................................................................................... 30

Recursive SSIS Package ........................................................................................................................................... 31 Package Requirements ....................................................................................................................................... 31 Recursive Solution .............................................................................................................................................. 31 Package Control Flow ......................................................................................................................................... 31



Page |1

@stratesql

JASON STRATE?SQL SERVER MVP

Package Variables............................................................................................................................................... 32 Package Data Flows............................................................................................................................................ 33

DF ? Build Hierarchy Root ............................................................................................................................... 33 DF ? Build Hierarchy Levels................................................................................................................................. 33 Package Output.................................................................................................................................................. 34 Recursive Wrap-up ............................................................................................................................................. 34 What The Sorts?!.................................................................................................................................................... 35 Sort Configuration .............................................................................................................................................. 35 String Comparisons ............................................................................................................................................ 35 Advanced Setting ............................................................................................................................................... 36 Sort Considerations ............................................................................................................................................ 36 Sort Wrap-Up ..................................................................................................................................................... 37 Merge Join Is My Friend ......................................................................................................................................... 38 Package Requirements ....................................................................................................................................... 38 Merge Join Solution............................................................................................................................................ 38 Package Control Flow ......................................................................................................................................... 38 Package Variables............................................................................................................................................... 39 Package Data Flows............................................................................................................................................ 39

DF ? Data Setup .............................................................................................................................................. 39 DF ? Match Rows ............................................................................................................................................ 40 Configuring Source Sort Order............................................................................................................................ 40 Configure Merge Join Transformation ................................................................................................................ 42 Word of Caution ............................................................................................................................................. 42 Merge Join Wrap-up........................................................................................................................................... 42 Override Variables .................................................................................................................................................. 44 Variable Expressions........................................................................................................................................... 44 Override Expressions .......................................................................................................................................... 45 Another Consideration ....................................................................................................................................... 47 Wrap-Up ............................................................................................................................................................ 47 Pivot Transformation .............................................................................................................................................. 48 Package Control Flow ......................................................................................................................................... 48 Package Data Flows............................................................................................................................................ 48 DFT ? Pivot Transformation ............................................................................................................................ 48 Pivot Transformation Configuration ................................................................................................................... 49



Page |2

@stratesql

JASON STRATE?SQL SERVER MVP

Component Properties ....................................................................................................................................... 49 Input Columns .................................................................................................................................................... 49 Input and Output Properties .............................................................................................................................. 49 Pivot Transformation Wrap-Up .......................................................................................................................... 52 UnPivot Transformation ......................................................................................................................................... 53 Package Control Flow ......................................................................................................................................... 53 Package Data Flows............................................................................................................................................ 53

DFT ? Unpivot Transformation ........................................................................................................................ 53 Unpivot Transformation Configuration ............................................................................................................... 54 Unpivot Transformation Wrap-Up ...................................................................................................................... 55 UnPivot Alternative ................................................................................................................................................ 57 Package Requirements ....................................................................................................................................... 57 Unpivot Alternative Solution .............................................................................................................................. 57 Package Control Flow ......................................................................................................................................... 57 Package Variables............................................................................................................................................... 59 Package Data Flows............................................................................................................................................ 60

DFT_Demo_File .............................................................................................................................................. 60 DFT_Unpivot ................................................................................................................................................... 60 SCR_Unpivot Configuration ................................................................................................................................ 60 Unpivot Wrap-up ............................................................................................................................................... 63 Create XML Document............................................................................................................................................ 64 Package Requirements ....................................................................................................................................... 64 Script Task Solution ............................................................................................................................................ 64 Package Control Flow ......................................................................................................................................... 64 Package Variables............................................................................................................................................... 64 Package Data Flows............................................................................................................................................ 65 DFT_Demo_File .............................................................................................................................................. 65 DFT_XMLDocument ........................................................................................................................................ 65 SCR_Fill_XMLData Configuration ........................................................................................................................ 66 Input0_ProcessInputRow Function ................................................................................................................. 67 Formatting Functions ......................................................................................................................................... 68 XML Script Task Wrap-Up ................................................................................................................................... 69 Instant Excel Spreadsheet....................................................................................................................................... 71 Trouble In Excel Land ......................................................................................................................................... 71



Page |3

@stratesql

JASON STRATE?SQL SERVER MVP

Instant Spreadsheet ........................................................................................................................................... 71 Excel Wrap-Up ................................................................................................................................................... 72 Every Which Way Outputs ...................................................................................................................................... 74 Multiple Outputs Requirements ......................................................................................................................... 74 Multiple Outputs Solution .................................................................................................................................. 74 Package Control Flow ......................................................................................................................................... 75 Package Variables............................................................................................................................................... 75 Package Data Flows............................................................................................................................................ 75 DFT_MultipeOutputs.......................................................................................................................................... 75 SCR_MultiOutput Configuration ......................................................................................................................... 76 Multiple Outputs Wrap-up ................................................................................................................................. 78 10 Things About Expressions .................................................................................................................................. 79 1. Expression Evaluation ..................................................................................................................................... 79 2. Use SystemStartDate for Timestamps............................................................................................................. 79 3. Conditional Logic ............................................................................................................................................ 79 4. AND and OR ................................................................................................................................................... 79 5. Backslash........................................................................................................................................................ 80 6. Too Many Parenthesis .................................................................................................................................... 80 7. Too Few Parenthesis....................................................................................................................................... 80 8. Date Functions ............................................................................................................................................... 81 9. Missing Functions Suck ................................................................................................................................... 81 10. Concatenation .............................................................................................................................................. 81 Expression Wrap-Up........................................................................................................................................... 82 Environmental Variable .......................................................................................................................................... 83 Why Environmental Variable? ............................................................................................................................ 83 Environmental Variable Setup ............................................................................................................................ 83 Environmental Variable Wrap-Up....................................................................................................................... 84 SQL Server Configuration ........................................................................................................................................ 86 Why SQL Server Configuration?.......................................................................................................................... 86 SQL Server Configuration Setup.......................................................................................................................... 86 SQL Server Configuration Wrap-Up .................................................................................................................... 87 DTSConfig Configuration......................................................................................................................................... 89 Heading.............................................................................................................................................................. 89 Configuration ..................................................................................................................................................... 89



Page |4

@stratesql

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download