SSIS package template location

Question

Can we change the default location for package templates? Or at least add more locations?

I would like to create some templates and add them to the source control but I'm not very keen on adding a file on the C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransformationItems folder to the source control.

How do you deal with that?

If you're interested in other approaches, using BIML + BIDSHelper to define your templates and then generate new might be an interesting approach. — billinkc, Feb 22 '13 at 19:07

billinkc · Accepted Answer · 2013-02-22T17:49:57.650

1

I've never tried moving it, but when we built out templates, we had a Common project that contained common material (we're creative like that) like configuration settings for databases as well as template packages. The solution also had a start up project with nothing in it but a post build script where we'd copy the templates into the above path. That way people didn't have to worry about this "complex copying stuff" to get started.

copy /y "$(SolutionDir)\SQL\SSIS\PackageTemplate.dtsx" "$(DevEnvDir)\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransformationItems"

As rvphx reminded me, you will want to have people that use your templates to reset the Package ID. Otherwise, you greatly complicate reporting against your sysdtslog90/sysssislog table. The free visual studio add on, BIDSHelper, has a feature to reset the GUIDs from the project window. Another issue I ran into with our own templates was they would a physical file name that we provided but did not match the Package Name property inside SSIS. We'd either assign a junk physical file name and then rename it to the proper name or just remember to fix it in the package properties.

edited Feb 22 '13 at 17:49

answered Feb 22 '13 at 14:28

billinkc

59,250
9
102
159

just to see if I get this right! If a new developer get on board he or she downloads this common project that contains the template (and other stuff) from the source control , builds it and then the build copy the template to the appropriate folder? – Diego Feb 22 '13 at 15:26
By virtue of *building* the empty project, the post deploy event fires which handles copying out the template packages into the correct folder. Otherwise, yeah, that was our process. No idea if there was a better mechanism but it worked for us. – billinkc Feb 22 '13 at 15:55
1

Just wanted to point out that when we think about building packages from a template file, the package GUIDS tends to remain the same. So when you are doing Logging and stuff, it gets a bit difficult to aggregate the data based on the GUIDS. Just saying because I faced a similar problem. – rvphx Feb 22 '13 at 17:01
To address that @rvphx, our templates have annotations explicitly stating "rename the object, not just the physical file. Use BIDSHelper to reset all the IDs" – billinkc Feb 22 '13 at 17:42
as weird as it sounds, this GUID thing is a known issue. If you check the training book, it will say that "After the package has been created from a user-defined template, the value of its ID property will be the same as the one in the template. Because this could make it difficult to distinguish between different packages created from the same template in log entries and audits, you should change the identifier before deploying the package, at best right after creating it." not sure why they didn't solve this! – Diego Feb 25 '13 at 09:23

score 1 · Answer 2 · answered Feb 22 '13 at 18:10

The template we've been using is pretty plain-vanilla (mostly a bunch of standard variables, connection managers and event handlers for SSIS 2008), and a couple of years back I wrote a NAnt script to build a simple one-package solution. Since the .dtsx format is just XML, it's not too terribly difficult to make a text template file out of a minimalist package and use NAnt's <replacetokens> task to replace things like package names and GUIDs. It's even easy to create unique GUIDs, since NAnt lets you embed C# code in your scripts:

<script language="C#" prefix="script" >
    <references>
        <include name="System.dll" />
        <include name="System.DirectoryServices.dll" />
    </references>
    <imports>
        <import namespace="System.DirectoryServices"/>
    </imports>
    <code>
        <![CDATA[
        [Function("create-guid")]
        public static string CreateGuid() 
        {
            return Guid.NewGuid().ToString().Trim();
        }
        ]]>
    </code>
</script>

<property name="PACKAGE_DTSID_GUID" value="${script::create-guid()}"/>
<property name="PACKAGE_VERSION_GUID" value="${script::create-guid()}"/>

score 1 · Answer 3 · answered Feb 23 '13 at 15:14

1

To plainly answer your questions :

No you can't change it. No you can't add more locations.

As described, you can script copy from one location to this folder. This is what most of us do.

answered Feb 23 '13 at 15:14

Dominic Goulet

7,983
7
28
56

SSIS package template location

3 Answers3