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.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- using sharepoint as a data source for modern reporting
- case studies for ms bi inkey solutions
- programming interview notes database
- applied business intelligence
- sql notes for professionals
- software features ssrs
- execute ssrs report from ssis package
- sql server integration services ssis step by step tutorial
- plan a plan b plan c sql school
- ssrs interview questions and answers guide
Related searches
- national medical days of recognition
- days of life calculator
- days of our lives music
- last days of heart failure
- 12 days of yule traditions
- first days of school pdf
- last days of hitler documentary
- last days of nazi germany
- days of the year calculator
- final days of cirrhosis
- days of the year calendar
- funny days of the year