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 (probably not the best descriptive name). This blogpost describes what I have figured out so far.

1) SSDT October preview
First download and install the SSDT 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: dtsxt and it only has two tabs (control flow and data flow). For this example I added a FILE connection manager (named myFile.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"
• You can't edit tasks in a template via the package. You need to do that via the template itself.