P3.NET

Creating an SSIS Custom Task, Updated

Last year I posted a series of articles about creating custom SSIS tasks to cleanly wrap some common work you may find yourself doing in SSIS. You could do this with script tasks but if you need this same functionality everywhere then it becomes a maintenance issue. In that series of articles we created a framework for adding new tasks easily and created one task to generate SSRS reports. Since that time my company has been using these tasks successfully but we did identify a couple of issues. Since that series of blog posts was based upon the lessons we learned it makes sense to update the code with the changes we’ve made over the past year.

Read More

Creating an SSIS Custom Task, Part 6

It is time to wrap up this series on creating a custom task for SSIS. We are going to finish our discussion with some advanced, but common, UI needs including:

  • Support for viewing and creating connections
  • Support for viewing and creating variables
  • Support for dynamic properties
  • Support for enumerating reports in SSRS
Read More

Creating an SSIS Custom Task, Part 5

In the previous post we finished the runtime side of the SSIS task. It’s time to work on the design side. The design consists of a standard Winforms form and controls. If you already know Winforms then you are halfway there. If not then you should read up on it first.

Read More

Creating an SSIS Custom Task, Part 4

In the last article we set up some infrastructure for creating tasks. We then implemented the shell of the task to generate SSRS reports. In this article we are going to finish up the runtime side of the SSRS task. To do that we need to talk about task parameters, persisting the parameters, getting access to connections at runtime and how to work with variables at runtime.

Read More

Creating an SSIS Custom Task, Part 3

In the first part of this series we generated an SSRS report using an SSIS script task. In the second part we created a custom task to display “Hello World”. In this article we will combine the work to create a custom task to generate an SSRS report. For this post we will simply get the runtime side of the task working. Design time support will be added in a later article.

Read More

Creating an SSIS Custom Task, Part 2

In the last article we created a simple SSIS package to generate an SSRS report and save it to disk. For a single report this is fine as it didn’t require a lot of code but imagine if we wanted to write new packages and reuse this code. This is where script tasks break down. Each script task is a standalone block of code, basically its own .NET project. Code in one script task has no access to any other script, even in the same package. The only way to share code is copy/paste. This is error prone and unmaintainable.

One way to work around this is to create code outside the script task and copy paste the source files into each script task. This works but can be difficult to maintain over time. The ideal solution is to move this code into its own assembly and then reference the assembly in each script. Unfortunately SSIS requires that all script references be in the GAC. This complicates deployment as we’ll see later but is doable.

This is where SSIS custom tasks become useful. When you want to use the same script task in several different places or packages then it is time to promote it to a custom task. This provides several benefits.

  1. Code duplication is eliminated.
  2. The need for a script task goes away and is replaced by a reusable task.
  3. A custom task is easier to use in packages then script tasks.
  4. The custom task can access functionality that is difficult or impossible to do inside a script task.

For this article we will replace the existing script task to generate SSRS reports with a custom task. Creating the task and building the UI is straightforward once you get past the initial learning curve. Surprisingly though working with Winforms proves to be the most challenging aspect of the process. Before continuing be sure that everything is setup as discussed in the previous article.

Read More

Creating an SSIS Custom Task, Part 1

Recently I had the need to create a couple of custom tasks for SQL Server Integration Services (SSIS) at work and I was shocked at how few the resources were for doing this. There are a lot of blogs and articles about creating SSIS custom tasks, but most of them are outdated or wrong. Even Microsoft’s own documentation has clearly been promoted from older versions and is no longer accurate. This leads to a lot of wasted time and scratching of your head trying to figure out what is wrong.

In this series of articles I’m going to demonstrate how to create a custom task for SSIS 2014. This information is also applicable to SSIS 2012 but doesn’t necessarily work with earlier or later versions of SSIS. In this first article we will set up a simple SSIS package that we can use for the remainder of the series.

Read More

Using Parallel HttpClientConnection objects in SSIS

In SSIS (SQL Server Integration Service) you often need to talk with web resources like WCF services. To do that you will generally define the connection using Connection Manager and HTTP. This sets up SSIS to communicate with the remote resource. To use the connection you normally use a script task to get the connection from Connection Manager, create an HttpClientConnection object and then use the methods on the connection to communicate with the remote server. You will generally find code similar to the following.

Read More