Limit search to available items
Book Cover
E-book
Author Rossum, Joost van, author

Title Extending SSIS with .NET scripting : a toolkit for SQL Server Integration Services / Joost van Rossum, Regis Baccaro
Published [Berkeley] : Apress, 2015
©2015

Copies

Description 1 online resource : illustrations
Series The expert's voice in SQL server
Expert's voice in SQL server.
Contents Machine generated contents note: pt. I Getting Started -- ch. 1 Getting Started with SSIS and Scripting -- Performing a Basic Action with SSIS Built-in Components -- What Is SSIS? -- Development Tool -- Control Flow Tasks -- Data Flow Components -- Example 1 Load .csv File into Database -- Performing an Action with a Combination of SSIS Built-in Components -- Example 2 Find Files in a Folder and Load Them into the Database with Built-in Components -- .NET Scripting Makes Life Easier -- Example 3 Find the Latest Modified .csv File -- SSIS Versions and .NET Library Limitations -- Programming Language: C# or VB.NET -- Summary -- ch. 2 Script Task vs. Script Component -- Introduction to Script Task -- Package Design -- Edit Script Task -- Script -- Testing -- Introduction to Script Component -- Flat File -- Script Component -- When to Use a Script Task/Component -- Building Code -- Debugging in Visual Studio -- Script Task -- Script Component -- Summary -- ch. 3 .NET Fundamentals -- Introduction -- .NET Data Types .vs Data Flow Data Types -- Data Types -- Variables -- Operators -- Using/Import, Classes, and Namespaces -- Arrays -- Different Types of Arrays -- Initializing Arrays -- Accessing Array Members -- Collections -- Lists -- Generics -- Loops -- while Loop -- for Loop -- foreach Loop -- Error Handling -- throw Statement -- try-catch Statement -- try-catch-finally Statement -- Summary -- pt. II Script Tasks -- ch. 4 Script Task -- Editor -- Script Layout -- Variables and Parameters -- Method 1 ReadOnlyVariables and ReadWriteVariables -- Method 2 Variable Dispenser -- Referencing Assemblies -- Creating an Assembly -- Connection Managers -- File Connection Managers -- Logging Events -- FireCustomEvents -- Child Package -- Script -- Parent Package -- Script Task -- Code -- Summary -- ch. 5 File Properties -- Getting All Properties -- Checking for File Existence -- File Name, Extension, and Path -- File Created and Modified Time -- File Owner -- File Attributes and ReadOnly -- File Size -- Examples of the File Properties Mentioned -- Deleting Files Older Than X Days -- Checking for a Locked File -- Moving the File -- Foreach Loop Ordered File Enumerator -- Foreach Ordered File Enumerator -- Creating a Dataset -- Loading Files -- Adding Rows to the Dataset -- Writing the Result -- Putting It All Together -- Summary -- ch. 6 Working Through the Internet and the Web -- Sending HTML-Formatted Email -- SMTP Connection Manager -- Variables -- Script Task -- Code -- Results -- Downloading a File from a Web Server -- Data Flow Task -- HTTP Connection Manager -- Script Task -- Code -- Results -- Downloading the Latest File from an FTP Server -- Variables -- Results -- Downloading a File from an SFTP Server -- Download and Install -- Variables -- Script Task -- Add Reference -- Code -- Results -- Summary -- ch. 7 Working with Web Services and XML -- Windows Communication Foundation -- Web Services -- Creating Variables -- Script -- Service Reference -- Custom Namespace -- Config or Not Config -- Solution 1 Modifying .config Files -- Solution 2 In-Code Method -- Foreach Loop to Handle XML -- Variable Mappings -- Validating XML Against Schemas -- Validating XML with Schemas -- Connections -- Code -- Custom Namespaces -- Validating the XML -- Summary -- ch. 8 Advanced Solutions with Script Task -- Regular Expressions -- Variables -- Script Task -- Script -- Precedence Constraint -- Testing -- Zip/Unzip -- Downloading the Library -- Unzipping -- Adding a Reference -- Unzip Script -- Zipping -- Reference -- Zip Script -- Encrypt/Decrypt Files -- Connection Managers -- Variable -- Script Task -- Script -- Summary -- pt. III Script Component -- ch. 9 Script Component Foundation -- Editor -- Input Columns -- Inputs and Outputs -- Connection Managers -- Script Layout -- Variables and Parameters -- Method 1 ReadOnlyVariables and ReadWriteVariables -- Method 2 Variable Dispenser -- Conclusion -- Connection Managers -- Logging Events -- Changing .NET Versions -- Summary -- ch. 10 Script Component As Source -- Flat File with an Uneven Number of Columns -- Script Component Source -- Creating Output Columns -- Creating a File Connection Manager -- Code -- Results -- Flat File with Records Split over Multiple Rows -- Script Component Source -- Output Columns -- Create a Connection Manager -- Code -- Results -- Generate Random Data with the Script Component As a Source -- Script Component Source -- Output Columns -- Code -- Results -- Summary -- ch. 11 Script Component Transformation -- Script Component Transformation -- ProperCase -- Source -- Script Transformation -- Encrypting and Decrypting Data -- Solution Package -- Variables -- Script -- Salting the Password -- Encrypting the Stream -- Encrypting Data -- Decrypting Data -- Comparing Rows -- IsNumeric -- Creating Surrogate Keys -- Creating GUIDS -- Creating the Package -- Add Script Component -- Results -- Conditional Multicast -- Data Source -- Script -- Summary -- ch. 12 Script Component As Destination -- Basic Flat File Destination with Header and Footer -- Create a File Connection Manager -- Source -- Script Component -- Adding Input Columns -- Selecting Connection Manager -- Code -- Results -- Basic XML Destination -- Creating a File Connection Manager -- Source -- Script Component -- Input Columns -- Selecting Connection Manager -- Code -- Results -- Summary -- ch. 13 Regular Expressions -- Prerequisites -- Validating Email Addresses -- Script -- Calling the Method -- Removing HTML Tags -- Package -- Script -- Cleaning/Validating -- Package -- Script -- Summary -- ch. 14 Script Component Reflection -- Flexible XML Destination -- Script -- Results -- Transformation of All Columns -- Script Component Type -- Script Component Input Columns -- Script -- Results -- Calculating a Hash for a Row -- Variables -- Script Component -- ReadOnly Variable -- Input Columns -- Output Columns -- Script -- Results -- Summary -- ch. 15 Web Services -- Enriching with Weather -- Variables -- Script Component -- Input Columns -- Inputs and Outputs -- Add References -- Add Service Reference -- Namespaces -- Class Variables -- PreExecute -- Inputo_Processinputrow -- Postexecute -- Results -- Dynamics CRM -- Data Flow Example 1 -- Data Flow Example 2 -- Download CRM SDK -- Windows Identity Foundation -- Variables -- Add Script Component -- Input Columns -- Add References -- Add Namespaces -- Class Variables -- PreExecute -- Inputo_Processinputrow -- SharePoint -- Variables -- Script Component -- Script -- Extracting Excel Files from the Libraries -- PreExecute -- Inputo_Processinputrow -- Summary -- pt. IV Custom Tasks and Components -- ch. 16 Create a Custom Task -- Custom Task Preparations -- Creating Visual Studio Projects -- Adding SSIS References -- Default Namespace and Assembly Name -- Creating a Key for a Strong Name -- Getting the Public Key Token -- Icons -- Build Events -- Custom Task Runtime Code -- Task Properties -- Validating Task -- Execution Code -- SaveToXML and LoadFromXml -- Custom Task Form -- Form Code -- TaskHost and ServiceProvider -- PageLoad -- Interface Class Code -- Expression Builder -- References -- Usings -- Form Controls -- Code -- Summary -- ch
17 Create Custom Transformation -- Custom Transformation Preparations -- Creating Visual Studio Projects -- Adding SSIS References -- Default Namespace and Assembly Name -- Creating a Key for the Strong Name -- Getting the Public Key Token -- Icons -- Build Events -- Custom Transformation Runtime Code -- Component Properties and Input and Output Ports -- Validating Transformation -- Execution Code -- PreExecute -- PerformUpgrade -- Disable Advanced Editor -- Custom Transformation Form -- Form Code -- ServiceProvider, Connections, Variables, and Metadata -- FormLoad -- Interface Class Code -- Summary -- pt. V Scripting from .NET Applications -- ch. 18 Package Creation -- Creating an SSIS Package Programmatically -- Creating an SSIS Package with BIMLScript -- Creating Stage Database -- Adding a New BIML File -- Adding .NET Code -- Adding a Script Component with a RowNumber -- BIMLScript: Master Package -- Summary -- ch. 19 Package Execution from .NET -- Package Deployment Model -- Add Reference -- Create a Form -- Code -- Logging -- Results -- Project Deployment Model -- Add Reference -- Create a Form -- Code -- Results -- Summary
Summary 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
Notes Includes index
Print version record
SUBJECT SQL server. http://id.loc.gov/authorities/names/n90684343
SQL server fast
Subject Scripting languages (Computer science)
Software Engineering.
Microsoft programming.
COMPUTERS -- Programming Languages -- SQL.
Scripting languages (Computer science)
Form Electronic book
Author Baccaro, Regis, author
ISBN 9781484206386
148420638X