Extending SSIS Capabilities by Creating Custom SSIS Components for SharePoint

SQL Server Integration Services (SSIS) - a component of SQL Server - is an extensible platform for building high performance data integration (ETL - Extraction, Transformation and Loading) and workflow solutions. The extensibility part of SSIS comes from the fact that, the developers have ability to create custom components if the in-built components of it do not suffice the specific need. The platform makes it very simple to embed your own code into control flow or data flow pipeline.

This article demonstrates how to create custom SSIS components (connection manager, source component and destination component) that integrate data to/from SharePoint lists and work like any other native components of SSIS utilizing the SSIS pipeline for better performance. For more information click here.

Executing a SSIS Package from Stored Procedure in SQL Server

Recently we had a requirement to execute a SSIS package from one of our user stored procedures, but there is no direct way available in SQL Server for executing a SSIS package from a stored procedure. SQL Server has some undocumented system stored procedures for SSIS package management, but none is available that can be used to execute a SSIS package from the stored procedure. Now the question is, how can we execute a SSIS package from the user stored procedure? If the direct method is not available, is there any alternative for this?

Here are two different ways a SSIS package can be executed from a stored procedure. In the first approach, we will create a job, making SSIS package call a job step and executing it by calling the sp_start_job system stored procedure from the user defined function. In the second approach, we will enable xp_cmdshell to execute the DTEXEC command line utility from the user defined stored procedure, for information click here.

SQL Server Integration Services ( SSIS ) Interview Questions

When preparing for a SQL Server interview, it is helpful to understand what questions may be asked related to SSIS. In this series, I will try to cover as much as I can to help you prepare.

SSIS Interview Questions - Basics Concepts, Control flow and Data flow

SSIS Interview Questions for Basic Concepts and Event Logging

SSIS Interview Questions on Transactions, Event Handling and Validation

SSIS Interview Questions on Architecture and Internals

SSIS Interview Questions for Memory Management and Deployment

SSIS Interview Questions for Data Profiling, Protection Level and Package Configuration

SSIS Package Deployment Model in SQL Server 2012 - Denali

Deployment has always been a challenge for SSIS developers to deploy packages. SSIS developers are envious of SSRS/SSAS developers as they have an easy way to create a single unit of deployment (deployment package) that contains everything needed for the deployment. The good news is the inclusion of the SSIS Package Deployment Model in SQL Server 2012 code name Denali. In these articles I cover what it is and how to get started to simplify your SSIS package deployments.

SSIS Package Deployment Model in SQL Server Denali (Part 1 of 2)

SSIS Package Deployment Model in SQL Server Denali (Part 2 of 2)

How to upgrade SSIS 2005 Packages to SSIS 2008

How to upgrade SSIS 2005 Packages to SSIS 2008
There are several enhancements in SSIS 2008 such as enhanced lookup transformation, the development environment for Script Task and Script Component changing from VSA to VSTA, etc. If you plan to upgrade your SSIS 2005 packages to SSIS 2008 it may be a challenging task especially if it uses the Script Task or Script Component because of the development environmental changes (from VSA to VSTA) . SSIS provides several different options to upgrade SSIS packages, for more details click here.

SQL Server Integration Services ( SSIS ) - Best Practices

SQL Server Integration Services ( SSIS ) - Best Practices
Part 1 briefly talks about SSIS and its capability in terms of enterprise ETL. Then it gives you an idea about what consideration you need to take while transferring high volume of data. Effects of different OLEDB Destination Settings, Rows Per Batch and Maximum Insert Commit Size Settings etc. For more details click here.
Part 2 covers best practices around using SQL Server Destination Adapter, kinds of transformations and impact of asynchronous transformation, DefaultBufferMaxSize and DefaultBufferMaxRows, BufferTempStoragePath and BLOBTempStoragePath as well as the DelayValidation properties. For more details click here.
Part 3 covers best practices around how you can achieve high performance with achieving a higher degree of parallelism, how you can identify the cause of poorly performing packages, how distributed transaction work within SSIS and finally what you can do to restart a package execution from the last point of failure. For more details click here.
Part 4 talks about best practices aspect of SSIS package designing, how you can use lookup transformation and what consideration you need to take while using it, impact of implicit type cast in SSIS, changes in SSIS 2008 internal system tables and stored procedures and finally some general guidelines. For more details click here.

Sending HTML formatted email from SSIS

Sending HTML formatted email from SSIS

Send Mail Task which is quite simple to use and can be used in a scenario where you need to send plain text email with less development efforts. But you can use, Script Task to overcome the limitations imposed by the Send Mail Task. Click here to see what code you need to write to send HTML formatted mails from SSIS package.

Sending email from SSIS Package

Sending email from SSIS Package
SSIS provides a built-in "Send Mail Task" to send email. The Send Mail Task is quite simple and straight forward in its configuration and use. Click here to learn how to configure and use Send Mail Task in your SSIS Package.

VSTA support for Script Task and Script Component in SSIS 2008

VSTA support for Script Task and Script Component in SSIS 2008

Unlike SQL Server 2005, SQL Server 2008 provides VSTA (Visual Studio Tools for Applications) environment for writing Script Task and Script Component instead of VSA (Visual Studio for Applications) environment. VSTA includes all the standard features of the Visual Studio environment, such as the color-coded Visual Studio editor, IntelliSense, and Object Browser and debugging features like breakpoints, watch/auto/locals windows and many more.

Refer this link to learn more details about it.

http://www.sql-server-performance.com/articles/biz/SSIS_New_Features_in_SQL_Server_2008_Part5_p1.aspx

Pipeline Performance Improvements in SSIS 2008

Pipeline Performance Improvements in SSIS 2008

In SQL Server 2008 SSIS, the data flow task has been redesigned to do dynamic scheduling and can now execute multiple components in parallel, even if they belong to the same execution tree. In other words, several threads can work together to do the work that a single thread is forced to do by itself in SQL Server 2005 SSIS. This can give you several-fold speedup in ETL performance.

Refer this link to learn more details about it.

http://www.sql-server-performance.com/articles/biz/SSIS_New_Features_in_SQL_Server_2008_Part4_p1.aspx

Data Profiling task in SSIS 2008

Data Profiling task in SSIS 2008

In SQL Server 2008, SSIS introduces the Data Profiling task in its toolbox, which provides data profiling functionality inside the process of extracting, transforming, and loading data. By using the Data Profiling task, you can analyze the source data more effectively, understand the source data better, and prevent data quality problems before they are introduced into the data warehouse.

Refer this link for more details.

http://www.sql-server-performance.com/articles/biz/SSIS_New_Features_in_SQL_Server_2008_Part3_p1.aspx

Lookup Transformation in SSIS 2005 and 2008

Lookup Transformation in SSIS 2005 and 2008

The Lookup transformation performs lookups by joining data in input columns with columns in a reference dataset/table. If there is no matching entry in the reference dataset, no join occurs and no values are returned from the reference dataset. This is an error, and the transformation fails, unless it is configured to ignore errors or redirect error rows to the error output. If there are multiple matches in the reference table, the lookup returns only the first match based on the lookup query.

I have written articles covering Lookup Tranformation in SSIS 2005 and Lookup Transformation in SSIS 2008 in details including different caching mechanism and detail usage example.

Refer this link for more details.

http://www.sql-server-performance.com/articles/biz/SSIS_New_Features_in_SQL_Server_2008_Part1_p1.aspx

SSIS Parallel Processing

SSIS Parallel Processing

Parallel execution improves the performance on the computers that have multiple physical or logical processors. To support parallel execution of different tasks in the package, SSIS uses two properties: MaxConcurrentExecutables and EngineThreads.

In my next article on SSIS Parallel processing I will cover how you can utilize the parallel processing capabilities of SSIS. In the later part of this article, I will provide some tips for SSIS Performance optimization and finally I will talk of what you need to take care of when executing your SSIS Package on 64-bit computers.

Refer this link for more details.

http://www.sql-server-performance.com/articles/biz/SSIS_An_Inside_View_Part_4_p1.aspx

SSIS Buffer Management

SSIS Buffer Management
Data flow engine requires buffer to store incoming data from source, do the necessary transformation in-memory if any, and upload it in the destination. The creation, allocation and management of buffer are done by SSIS Buffer Manager.

I have written an article covering all aspects of SSIS buffer management, including how buffers are allocated and de-allocated for the transformation, different kind of buffer related performance counters etc.

Refer this link for more details.
http://www.sql-server-performance.com/articles/biz/SSIS_An_Inside_View_Part_3_p1.aspx

SSIS Transformation and Execution Tree

SSIS Transformation and Execution Tree

There are two main concepts related to SSIS internals which need to be understood before we deep dive in optimizing SSIS packages.
Transformation - There are different kinds of tranformation in SSIS which overall impacts the performance of SSIS.
Execution Tree - At run time, the data flow engine breaks down Data Flow task operations into execution trees. Execution trees are enormously valuable in understanding buffer usage.

I have written an article covering different kinds of transformation and how data flow task operations are divided into execution trees.

Refer this link for more details.

http://www.sql-server-performance.com/articles/biz/SSIS_An_Inside_View_Part_2_p1.aspx

SSIS Architecture

SSIS Architecture
SSIS is a component of SQL Server 2005/2008 and is successor of DTS (Data Transformation Services) which had been in SQL Server 7.0/2000. Though from end-user perspective DTS and SSIS looks similar to each to some extent, it is not the case in actual. SSIS has been completely written from the scratch (it’s a new enterprise ETL product altogether) and hence it overcomes the several limitations of DTS.

I have written an article covering SSIS architecure and how it is different from DTS. Understanding these things will let you understand internal of SSIS or how SSIS actually works.

Refer this link for more details.
http://www.sql-server-performance.com/articles/biz/SSIS_An_Inside_View_Part_1_p1.aspx

SQL Server Integration Services (SSIS) - Checkpoint Restart-ability

Checkpoint Restart-ability

Normally ETL operations are very complex in nature and time consuming process as it often deals with millions of records while pulling it or doing transformation on it. Now even if a package fails in middle of its execution; on next execution it will again start from the begining, repeating all the tasks completed in last run.

SSIS provides Checkpoint restart functionality which simplify the recoverability of packages that contain complex operations and can provide significant time saving because the package does not need to reprocess all of the tasks prior to the checkpoint and will start from last point of failure.

Refer this link for more details.

http://www.sql-server-performance.com/articles/biz/SSIS_Features_And_Properties_Part3_p1.aspx

SQL Server Integration Services (SSIS) - Transaction Support

Transaction Support

By Default every component in a SSIS package executes in its own transaction. Using Transaction Support feature of SSIS you can group two or more components in a single group and let all those components inside that group to execute in single transaction.

I have written an article covering all aspects of SSIS Transaction Support functionality, starting with how to enable a transaction on a group of components, what are different options available, execution behavior in these available options, examples of Transaction support, its configuration and finally best practices to follow while using this feature.

Refer this link for more details.

http://www.sql-server-performance.com/articles/biz/SSIS_Features_And_Properties_Part2_p2.aspx

SQL Server Integration Services (SSIS) - Event Handlers

SSIS Event Handlers

Like any other event driven programming language, SSIS package and its components also generate events in their execution life-cycle. You can extend package functionality by writting event handlers for these events and can make package management easier during runtime.

I have written an article covering all aspects of SSIS Event Handler functionality, starting with what Event Handler is, examples of some events, it usage scenarios, its configuration and finally best practices to follow while using this feature.

Refer this link for more details.

http://www.sql-server-performance.com/articles/biz/SSIS_Features_And_Properties_Part2_p1.aspx

SQL Server Integration Services (SSIS) - Event Logging

SSIS Event Logging

While troubleshooting your SSIS package or tuning your SSIS package for performance you need to know what SSIS Runtime Engine and SSIS Pipeline Engine are doing under the hood. For that purpose, SSIS provides Event Logging feature which traces the execution of SSIS Package and its components during its execution life-cycle. I have written an article covering all aspects of SSIS Event Logging, starting with what Event Logging is, it usage scenarios, its configuration and finally best practices to follow while using this feature.

Refer this link for more details.

http://www.sql-server-performance.com/articles/per/SSIS_Features_And_Properties_Part1_p1.aspx