Use a SSIS package as Template in SSDT for Visual Studio 2017 Community

Question

. Hi everybody!, I'm running into a small problem.

I have VS 2017 Community Edition, I've installed SSDT (SQL Server Data Tools) I've built an SSIS package (a generic one) and now I would like to use it as a template for future packages.

So, I was digging in the web, also here in StackOverflow and found for example this article:

Setting up SSIS Item Template in SQL Server Data Tools for Visual Studio 2012

I found lot of other articles in MS for example:

https://learn.microsoft.com/en-us/sql/integration-services/create-packages-in-sql-server-data-tools

others, etc. and followed up to the tee but, for some reason I can't make it work.

This is my folder structure for my VS installation

I've left out of the tree some folders that doesn't apply to the problem

I put my package in the places I found in the articles and didnt work.

Sometimes, in the articles mentioned folders that I didn't have so I created and tried but nothing also (so deleted this folders)

I'm really lost, I tried everything I read, and some "creative things" from my yard but no results.

Anyone has come through this or has some experience about this issue? Anyone can help?

Thank you very much. Best regards and Happy new year!! Gabriel

Answer 1

Well based on the articles I've read and the help of @billinkc, I finally figured out where to put the package, I was missing something also, but I realised when I read @billinkc answer.

When you are on VS Community Version, you have to put the package in this path:

C:\Program Files (x86)\Microsoft Visual Studio\2017\Community\Common7\IDE\CommonExtensions\Microsoft\SSIS\ProjectItems\DataTransformationProject\DataTransformationItems

The files you need to make it work are, a dtsx file (the package that will be the template) and a vsdir file which mainly needs to have this structure I used this values and worked fine:

YourPackageName.dtsx| |The name you want to appear|100|The description of the package| |21| |#44

You can check all the fields of the vsdir file in the MS documentation:

https://learn.microsoft.com/en-us/visualstudio/extensibility/internals/template-directory-description-dot-vsdir-files

So now I have it running! Thanks for your help!!

Answer 2

I don't have 2017 installed but this appears to work for 2015 so if you don't mind being a guinea pig...

1. Close out all instances of Visual Studio

2. Copy your template package into Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransformationItems

3. Open your SSIS project

4. Right click on the project and select Add, New Item and you should have your template package listed (TemplatePackage2)

For reference, below is what my folder structure looks like. Since I couldn't remember whether the package templates lived at DataTransformationProject or DataTransformationProject\DataTransformationItems, I put a copy of my template package in both, varying by name to see what was picked up in the editor

• C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransformationItems
• C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransformationProjectItems.vsdir
• C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\TemplatePackage.dtsx
• C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransformationItems\DtsProjectItems.vsdir
• C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransformationItems\TemplatePackage2.dtsx

General notes about templates

The first is that the only way to get to your template is through the click path described above. If I want to add a new package, my default click path is right click on SSIS Package and select "New SSIS Package"

Pre-2012, the internal IDs of components and the package would remain the same. BIDS Helper had functionality to address this. Why it mattered was if you were attempting to track performance for packages, if they all report the same GUID you're going to have a hell of a time discerning whether Data Flow Task that ran for 10 hours is the same one that normally takes 10 seconds or was a second (different) package executed.

Developers will need to be local administrators on their machine to deploy the template package(s) and you'll need a process to keep that up to date. That or you need to engage the network admins to get these copied to developer machines.

Finally, if I had template logic, I'd probably abstract that away into Biml and then use source control to ensure people are working with the current version and avoids the whole permission issue.