BOOKS FOR PROFESSIONALS BY PROFESSIONALS® van Rossum Baccaro Extending SSIS with .NET RELATED Scripting Extending SSIS with .NET Scripting is a timeless and comprehensive scripting toolkit for SQL Server Integration Services to solve a wide array of everyday problems that SSIS developers encounter. The detailed explanation of the Script Task and Script Component foundations helps you develop your own scripting solutions, but this book also shows a broad arsenal of readymade and well- documented scripting solutions for common problems. All examples are in both C# and VB.NET, and work for all current versions of SSIS. SSIS is one of the leading ETL, Data Consolidation, and Data Transformation tools in today’s market. SSIS is used by ETL Developers, DBAs, and Data Analysts to transform data as required for different ETL processes. There are many built-in components and tasks to help developers to perform actions. For example, there are tasks for sending and receiving files through FTP, sending an email, and for accessing a wide range of database management systems. Yet there are times when developers require a task or component that does not exist and it would make their life much easier if they could create that task or component, and that is what this book it is about. It shows how to write .NET scripts and use the powerful Microsoft .NET library to implement new functionality as needed. • Provides a timeless scripting toolkit for all current SSIS versions • Gives a comprehensive explanation of scripting in SSIS • Offers a wide array of readymade examples for everyday problems Shelve in ISBN 978-1-4842-0639-3 54999 Databases /MS SQL Server User level: Beginning–Advanced SOURCE CODE ONLINE 9781484206393 www.apress.com www.it-ebooks.info Extending SSIS with .NET Scripting A Toolkit for SQL Server Integration Services Joost van Rossum Régis Baccaro www.it-ebooks.info Extending SSIS with .NET Scripting Copyright © 2015 by Joost van Rossum and Régis Baccaro This work is subject to copyright. All rights are reserved by the Publisher, whether the whole or part of the material is concerned, specifically the rights of translation, reprinting, reuse of illustrations, recitation, broadcasting, reproduction on microfilms or in any other physical way, and transmission or information storage and retrieval, electronic adaptation, computer software, or by similar or dissimilar methodology now known or hereafter developed. Exempted from this legal reservation are brief excerpts in connection with reviews or scholarly analysis or material supplied specifically for the purpose of being entered and executed on a computer system, for exclusive use by the purchaser of the work. Duplication of this publication or parts thereof is permitted only under the provisions of the Copyright Law of the Publisher’s location, in its current version, and permission for use must always be obtained from Springer. Permissions for use may be obtained through RightsLink at the Copyright Clearance Center. Violations are liable to prosecution under the respective Copyright Law. ISBN-13 (pbk): 978-1-4842-0639-3 ISBN-13 (electronic): 978-1-4842-0638-6 Trademarked names, logos, and images may appear in this book. Rather than use a trademark symbol with every occurrence of a trademarked name, logo, or image we use the names, logos, and images only in an editorial fashion and to the benefit of the trademark owner, with no intention of infringement of the trademark. The use in this publication of trade names, trademarks, service marks, and similar terms, even if they are not identified as such, is not to be taken as an expression of opinion as to whether or not they are subject to proprietary rights. While the advice and information in this book are believed to be true and accurate at the date of publication, neither the authors nor the editors nor the publisher can accept any legal responsibility for any errors or omissions that may be made. The publisher makes no warranty, express or implied, with respect to the material contained herein. Managing Director: Welmoed Spahr Lead Editor: Jonathan Gennick Development Editor: Douglas Pundick Technical Reviewer: John Welch Editorial Board: Steve Anglin, Mark Beckner, Gary Cornell, Louise Corrigan, Jim DeWolf, Jonathan Gennick, Robert Hutchinson, Michelle Lowman, James Markham, Susan McDermott, Matthew Moodie, Jeffrey Pepper, Douglas Pundick, Ben Renow-Clarke, Gwenan Spearing, Matt Wade, Steve Weiss Coordinating Editor: Jill Balzano Copy Editor: Kim Burton-Weisman Compositor: SPi Global Indexer: SPi Global Artist: SPi Global Cover Designer: Anna Ishchenko Distributed to the book trade worldwide by Springer Science+Business Media New York, 233 Spring Street, 6th Floor, New York, NY 10013. Phone 1-800-SPRINGER, fax (201) 348-4505, e-mail [email protected], or visit www.springeronline.com. Apress Media, LLC is a California LLC and the sole member (owner) is Springer Science + Business Media Finance Inc (SSBM Finance Inc). SSBM Finance Inc is a Delaware corporation. For information on translations, please e-mail [email protected], or visit www.apress.com. Apress and friends of ED books may be purchased in bulk for academic, corporate, or promotional use. eBook versions and licenses are also available for most titles. For more information, reference our Special Bulk Sales–eBook Licensing web page at www.apress.com/bulk-sales. Any source code or other supplementary material referenced by the author in this text is available to readers at www.apress.com. For detailed information about how to locate your book’s source code, go to www.apress.com/source-code/. www.it-ebooks.info Contents at a Glance About the Authors ���������������������������������������������������������������������������������������������������xix About the Technical Reviewer ��������������������������������������������������������������������������������xxi Acknowledgments ������������������������������������������������������������������������������������������������xxiii Introduction �����������������������������������������������������������������������������������������������������������xxv ■ Part I: Getting Started �������������������������������������������������������������������������1 ■ Chapter 1: Getting Started with SSIS and Scripting ����������������������������������������������3 ■ Chapter 2: Script Task vs� Script Component ������������������������������������������������������31 ■ Chapter 3: �NET Fundamentals ����������������������������������������������������������������������������55 ■ Part II: Script Tasks ��������������������������������������������������������������������������67 ■ Chapter 4: Script Task �����������������������������������������������������������������������������������������69 ■ Chapter 5: File Properties ����������������������������������������������������������������������������������115 ■ Chapter 6: Working Through the Internet and the Web �������������������������������������145 ■ Chapter 7: Working with Web Services and XML ����������������������������������������������175 ■ Chapter 8: Advanced Solutions with Script Task �����������������������������������������������195 ■ Part III: Script Component ��������������������������������������������������������������217 ■ Chapter 9: Script Component Foundation ����������������������������������������������������������219 ■ Chapter 10: Script Component As Source ���������������������������������������������������������243 ■ Chapter 11: Script Component Transformation �������������������������������������������������267 ■ Chapter 12: Script Component As Destination ��������������������������������������������������295 iii www.it-ebooks.info ■ Contents at a GlanCe ■ Chapter 13: Regular Expressions ����������������������������������������������������������������������311 ■ Chapter 14: Script Component Reflection ���������������������������������������������������������327 ■ Chapter 15: Web Services ����������������������������������������������������������������������������������349 ■ Part IV: Custom Tasks and Components �����������������������������������������383 ■ Chapter 16: Create a Custom Task ��������������������������������������������������������������������385 ■ Chapter 17: Create Custom Transformation ������������������������������������������������������411 ■ Part V: Scripting from �NET Applications ����������������������������������������431 ■ Chapter 18: Package Creation ���������������������������������������������������������������������������433 ■ Chapter 19: Package Execution from �NET ��������������������������������������������������������449 Index ���������������������������������������������������������������������������������������������������������������������467 iv www.it-ebooks.info Contents About the Authors ���������������������������������������������������������������������������������������������������xix About the Technical Reviewer ��������������������������������������������������������������������������������xxi Acknowledgments ������������������������������������������������������������������������������������������������xxiii Introduction �����������������������������������������������������������������������������������������������������������xxv ■ Part I: Getting Started �������������������������������������������������������������������������1 ■ Chapter 1: Getting Started with SSIS and Scripting ����������������������������������������������3 Performing a Basic Action with SSIS Built-in Components ����������������������������������������������3 What Is SSIS? �����������������������������������������������������������������������������������������������������������������������������������������3 Development Tool �����������������������������������������������������������������������������������������������������������������������������������4 Control Flow Tasks ����������������������������������������������������������������������������������������������������������������������������������5 Data Flow Components ���������������������������������������������������������������������������������������������������������������������������7 Example 1: Load �csv File into Database ����������������������������������������������������������������������������������������������11 Performing an Action with a Combination of SSIS Built-in Components ������������������������18 Example 2: Find Files in a Folder and Load Them into the Database with Built-in Components ����������19 �NET Scripting Makes Life Easier �����������������������������������������������������������������������������������25 Example 3: Find the Latest Modified �csv File ��������������������������������������������������������������������������������������26 SSIS Versions and �NET Library Limitations �������������������������������������������������������������������30 Programming Language: C# or VB�NET ��������������������������������������������������������������������������30 Summary ������������������������������������������������������������������������������������������������������������������������30 ■ Chapter 2: Script Task vs� Script Component ������������������������������������������������������31 Introduction to Script Task ���������������������������������������������������������������������������������������������33 Package Design ������������������������������������������������������������������������������������������������������������������������������������33 Edit Script Task �������������������������������������������������������������������������������������������������������������������������������������33 v www.it-ebooks.info ■ Contents The Script ���������������������������������������������������������������������������������������������������������������������������������������������34 Testing ��������������������������������������������������������������������������������������������������������������������������������������������������36 Introduction to Script Component ����������������������������������������������������������������������������������37 Flat File �������������������������������������������������������������������������������������������������������������������������������������������������37 Script Component ���������������������������������������������������������������������������������������������������������������������������������38 When to Use a Script Task/Component ��������������������������������������������������������������������������43 Building Code �����������������������������������������������������������������������������������������������������������������43 Debugging in Visual Studio���������������������������������������������������������������������������������������������45 Script Task ��������������������������������������������������������������������������������������������������������������������������������������������46 Script Component �����������������������������������������������������������������������������������������������������������50 Summary ������������������������������������������������������������������������������������������������������������������������54 ■ Chapter 3: �NET Fundamentals ����������������������������������������������������������������������������55 Introduction ��������������������������������������������������������������������������������������������������������������������55 �NET Data Types �vs Data Flow Data Types ���������������������������������������������������������������������55 Data Types ����������������������������������������������������������������������������������������������������������������������56 Variables �������������������������������������������������������������������������������������������������������������������������57 Operators ������������������������������������������������������������������������������������������������������������������������57 Using/Import, Classes, and Namespaces �����������������������������������������������������������������������58 Arrays �����������������������������������������������������������������������������������������������������������������������������59 The Different Types of Arrays ����������������������������������������������������������������������������������������������������������������59 Initializing Arrays ����������������������������������������������������������������������������������������������������������������������������������60 Accessing Array Members ��������������������������������������������������������������������������������������������������������������������61 Collections ����������������������������������������������������������������������������������������������������������������������61 Lists ��������������������������������������������������������������������������������������������������������������������������������61 Generics �������������������������������������������������������������������������������������������������������������������������62 Loops ������������������������������������������������������������������������������������������������������������������������������62 The while Loop �������������������������������������������������������������������������������������������������������������������������������������62 The for Loop ������������������������������������������������������������������������������������������������������������������������������������������63 The foreach Loop ����������������������������������������������������������������������������������������������������������������������������������64 vi www.it-ebooks.info ■ Contents Error Handling ����������������������������������������������������������������������������������������������������������������65 The throw Statement ����������������������������������������������������������������������������������������������������������������������������65 The try-catch Statement �����������������������������������������������������������������������������������������������������������������������66 The try-catch-finally Statement������������������������������������������������������������������������������������������������������������66 Summary ������������������������������������������������������������������������������������������������������������������������66 ■ Part II: Script Tasks ��������������������������������������������������������������������������67 ■ Chapter 4: Script Task �����������������������������������������������������������������������������������������69 Editor ������������������������������������������������������������������������������������������������������������������������������69 Script Layout ������������������������������������������������������������������������������������������������������������������72 Variables and Parameters ����������������������������������������������������������������������������������������������77 Method 1: ReadOnlyVariables and ReadWriteVariables ������������������������������������������������������������������������77 Method 2: Variable Dispenser ���������������������������������������������������������������������������������������������������������������83 Referencing Assemblies �������������������������������������������������������������������������������������������������88 Creating an Assembly ���������������������������������������������������������������������������������������������������������������������������89 Connection Managers ����������������������������������������������������������������������������������������������������98 File Connection Managers ��������������������������������������������������������������������������������������������������������������������98 Logging Events �����������������������������������������������������������������������������������������������������������������������������������106 FireCustomEvents ��������������������������������������������������������������������������������������������������������109 Child Package ���������������������������������������������������������������������������������������������������������������110 The Script ���������������������������������������������������������������������������������������������������������������������110 The Parent Package �����������������������������������������������������������������������������������������������������111 The Script Task �����������������������������������������������������������������������������������������������������������������������������������113 The Code ����������������������������������������������������������������������������������������������������������������������113 Summary ����������������������������������������������������������������������������������������������������������������������114 ■ Chapter 5: File Properties ����������������������������������������������������������������������������������115 Getting All Properties ����������������������������������������������������������������������������������������������������115 Checking for File Existence ����������������������������������������������������������������������������������������������������������������118 File Name, Extension, and Path ����������������������������������������������������������������������������������������������������������118 File Created and Modified Time ����������������������������������������������������������������������������������������������������������119 vii www.it-ebooks.info ■ Contents File Owner ������������������������������������������������������������������������������������������������������������������������������������������119 File Attributes and ReadOnly ��������������������������������������������������������������������������������������������������������������119 File Size ����������������������������������������������������������������������������������������������������������������������������������������������119 Examples of the File Properties Mentioned ����������������������������������������������������������������������������������������119 Deleting Files Older Than X Days ���������������������������������������������������������������������������������121 Checking for a Locked File �������������������������������������������������������������������������������������������124 Moving the File �������������������������������������������������������������������������������������������������������������128 Foreach Loop Ordered File Enumerator ������������������������������������������������������������������������133 Foreach Ordered File Enumerator �������������������������������������������������������������������������������������������������������133 Creating a Dataset ������������������������������������������������������������������������������������������������������������������������������135 Loading Files ��������������������������������������������������������������������������������������������������������������������������������������135 Adding Rows to the Dataset ���������������������������������������������������������������������������������������������������������������136 Writing the Result �������������������������������������������������������������������������������������������������������������������������������136 Putting It All Together��������������������������������������������������������������������������������������������������������������������������137 Summary ����������������������������������������������������������������������������������������������������������������������144 ■ Chapter 6: Working Through the Internet and the Web �������������������������������������145 Sending HTML-Formatted Email ����������������������������������������������������������������������������������145 SMTP Connection Manager ����������������������������������������������������������������������������������������������������������������145 Variables ���������������������������������������������������������������������������������������������������������������������������������������������146 Script Task ������������������������������������������������������������������������������������������������������������������������������������������147 The Code ���������������������������������������������������������������������������������������������������������������������������������������������147 The Results �����������������������������������������������������������������������������������������������������������������������������������������150 Downloading a File from a Web Server ������������������������������������������������������������������������151 Data Flow Task �����������������������������������������������������������������������������������������������������������������������������������151 HTTP Connection Manager �����������������������������������������������������������������������������������������������������������������152 Script Task ������������������������������������������������������������������������������������������������������������������������������������������152 The Code ���������������������������������������������������������������������������������������������������������������������������������������������153 The Results �����������������������������������������������������������������������������������������������������������������������������������������155 Downloading the Latest File from an FTP Server ���������������������������������������������������������155 Variables ���������������������������������������������������������������������������������������������������������������������������������������������155 The Results �����������������������������������������������������������������������������������������������������������������������������������������165 viii www.it-ebooks.info ■ Contents Downloading a File from an SFTP Server ���������������������������������������������������������������������165 Download and Install ��������������������������������������������������������������������������������������������������������������������������166 Variables ���������������������������������������������������������������������������������������������������������������������������������������������166 Script Task ������������������������������������������������������������������������������������������������������������������������������������������166 Add Reference ������������������������������������������������������������������������������������������������������������������������������������167 The Code ���������������������������������������������������������������������������������������������������������������������������������������������168 The Results �����������������������������������������������������������������������������������������������������������������������������������������174 Summary ����������������������������������������������������������������������������������������������������������������������174 ■ Chapter 7: Working with Web Services and XML ����������������������������������������������175 Windows Communication Foundation ��������������������������������������������������������������������������175 Web Services ����������������������������������������������������������������������������������������������������������������176 Creating Variables �������������������������������������������������������������������������������������������������������������������������������177 The Script �������������������������������������������������������������������������������������������������������������������������������������������177 Service Reference ������������������������������������������������������������������������������������������������������������������������������177 Custom Namespace ����������������������������������������������������������������������������������������������������������������������������179 Config or Not Config ���������������������������������������������������������������������������������������������������������������������������179 Solution 1: Modifying �config Files ������������������������������������������������������������������������������������������������������180 Solution 2: The In-Code Method ���������������������������������������������������������������������������������������������������������181 The Foreach Loop to Handle XML �������������������������������������������������������������������������������������������������������184 Variable Mappings ������������������������������������������������������������������������������������������������������������������������������186 Validating XML Against Schemas ���������������������������������������������������������������������������������187 Validating XML with Schemas ������������������������������������������������������������������������������������������������������������188 Connections ����������������������������������������������������������������������������������������������������������������������������������������188 The Code ���������������������������������������������������������������������������������������������������������������������������������������������189 Custom Namespaces ��������������������������������������������������������������������������������������������������������������������������189 Validating the XML ������������������������������������������������������������������������������������������������������������������������������191 Summary ����������������������������������������������������������������������������������������������������������������������193 ■ Chapter 8: Advanced Solutions with Script Task �����������������������������������������������195 Regular Expressions �����������������������������������������������������������������������������������������������������195 Variables ���������������������������������������������������������������������������������������������������������������������������������������������196 Script Task ������������������������������������������������������������������������������������������������������������������������������������������196 ix www.it-ebooks.info
Description: