How to Localize Your Custom Connection Manager

Similar to localizing a task, you can localize the display name and description of a custom connection manager by setting the LocalizationType property of the DtsConnection attribute. SSIS will look for two properties on the class:

  • ConnectionDescription
  • ConnectionDisplayName

These must be public, static string properties on your LocalizationType class.

The code looks like this:

Refresh an Excel Workbook with a Script Task

Are you loading data from an Excel workbook with external queries using SSIS? Do you want to make sure those queries are refreshed before the load takes place? Have I got the post for you! I recently had to do this myself, and after some googling with Bing, I found an MSDN forum post that describes how to refresh all of the queries within a workbook using an SSIS Script Task. I’ve adapted the code from the post, and you can find it below.

For the code to compile, you’ll need to add a reference to Microsoft.Office.Interop.Excel within your script task’s project.


Next, select the reference under the project explorer, and open the properties window. You’ll need to disable the Embed Interop Types option. (This assumes you’re using SQL 2012, which supports .NET 4 … if you are using 2008 with .NET 2/3.5, you won’t have to do this).


Include the following code in your script task. It iterates over all of the Excel connection managers within your package, loads the workbook, and calls RefreshAll() to refresh each of the queries in the workbook. The task then sleeps for 20 seconds (to give time for the query refresh to complete) before exiting.

I kept the code simple for this example, but there are a few different optimizations you can make here:

  • Add error handling
  • Pass in the excel file name through a variable instead of iterating over each connection manager
  • Refresh a specific query within the workbook, rather than calling RefreshAll
  • Find your query within the workbook and set the BackgroundQuery property to False to avoid the asynchronous refresh call, and having to sleep

Generate Data with a Script Component

I use the following code to generate data with a script component. I’ve found this useful in the past to demonstrate how buffers are handled in the data flow, as well as perf/load testing a destination component. This script will automatically generate data based on the columns you’ve defined for the component – no additional code modifications are needed. For optimal performance, it uses static values, but it could be updated to support randomly generated values as well (it does support random ints – look at the code for details)

To use it, do the following:

  • Add a new Script Component, and make it a Source
  • Bring up the component UI
  • On Inputs and Outputs, define your columns
  • When you are done, click OK to save the component. I know, you haven’t entered the script yet, but it is a good idea to save your component at this point. If for some reason I cancel out of the dialog, or you have an issue with the VSTA editor, you could lost all of the columns you just spent time defining.



  • Open the UI again
  • (Optional) If you want to be able to control the number of rows that get generated, define an Integer variable called MaxRowCount. Add it as a ReadOnly variable
  • Click the Edit Script… button
  • When the editor opens, replace your main.cs file with the following code

Download the script file here:


Encrypting Sensitive Properties in a Custom Connection Manager

SSIS provides a mechanism to automatically encrypt the sensitive properties of your Connection Manager (or Task) based on the package ProtectionLevel. To make use of this in your custom code, you’ll need to do two things:

  1. Implement the IDTSComponentPersist interface
  2. Add a Sensitive=”1” attribute to one or more elements of your persisted Connection Manager (or Task)

Sample code:

How to Localize Your Custom Task

The DtsTask attribute has a LocalizationType member. This should be your resource class. SSIS will look for two properties:

  • TaskDisplayName
  • TaskDescription

These must be public, static string properties on your “LocalizationType” class.

Your task code would look like this:

You can add localizable string resources to your class from the Project settings:


Note, the Access Modifier must be set to Public.

Double note (as I hit this when putting together the blog post) – in SQL 2012, when you first add a new task to your <sql>110DTSTasks directory, you can right click on the SSIS Toolbox and select Refresh Toolbox to make it show up.


If your LocalizationType settings are incorrect (for example, you forgot to set the Access Modified to Public), your task will show up with an empty name. You must fix your class and rebuild, but you’ll need to restart Visual Studio before the changes take effect (as the class has been instantiated, SSIS will have the old object cached…).

Automatically Select Input Columns in a Custom Data Flow Component

The following code snippet can be used in a custom data flow component to automatically select all input columns when you attach a path.

This is the equivalent to clicking the Select All box in the Advanced Editor.