SQL Server 2016 CTP 3.0 New Control Flow Templates


A long long time ago ( 2008 ) there was a connect request for Package Parts to make it easier to reuse certain parts of a package. In SQL 2016 CTP 3.0 Microsoft added Control Flow Templates. This blogpost describes what I have figured out so far.

1) SSDT October preview

First download and install theSSDT October preview for Visual Studio 2015.

SSDT October Preview

2) Create new SSIS project

Now open SSDT and create a new SSIS project. In the solution explorer you will find a new folder called Control Flow Templates. Right click it to create a new template. You can also add an existing template from an other project/folder or even reference an existing template which makes it easier to create some kind of template archive. For this example just create a new one Call it MoveToArchive .

Control Flow Templates

3) Template

When you have created a new template a new file is opened which looks just like a package. The only difference is its extension: dtsx t and it only has two tabs (control flow and data flow). For this example I added a FILE connection manager (namedmyFile.txt)pointing to a random file and second FILE connection manager pointing to an archive folder. A File System Task will move the file to the archive folder. Rename the task to FSYS - Move to Archive. Optionally add a annotation describing the template. Then save the template.

File System Task moving file to archive folder

4) Adding template to package

You might have noticed the extra menu item in the SSIS Toolbar called Control Flow Templates. Drag the new template from the SSIS Toolbar to your package. You will see that the task has a T in the upper right corner and that it uses the name of the template and not the name of the task in the template. Any annotations are ignored and will not show up in the package.

Adding template to package

5) Configuring the template

Now double click the template in your package or right click it and choose Edit. In the new Template Configuration Dialog, go to the second tab called Connection Managers. Select the myFile.txt connection manager and then select the connectionstring property and change the path of it to let it point to an other file.

Unfortunately you can only hardcode any changes. It would be useful to have expressions or even use a connection manager from your package to override the connection manager from your template.

Template Configuration Dialog

6) Testing

Now run the package to test the result.

Running the package

7) Point of attentions

You can only have one execution in a template. If you need more tasks then you have to add them in a Sequence Container. Script Tasks that uses Connection Manager wont work unless you copy the connection manager to the package. Apparently it searches the name in the package instead of in the template. Renaming a template will screw up packages using that template. Make sure first give it a correct name. If you want to use naming conventions than you should give the template the name of taks. In this example: "FSYS - Move to archive.dtsxt"