T4 Support in SSDT

, 2014-03-29

Sometimes a piece of technology passes you by completely, T4 templating happens to have done that for me.  On tuesday night, at SQL Supper, Geoff Clark ( a uk MCM ) (t) done a fantastic presentation on end-to-end datawarehouse loading, a portion of this was on t4 templating.  The question arose about SSDT and T4 templating and as a coincidence literally the next day  the SSDT team put out the March 2014 release,  having my interest already piqued i thought id have a play :)

So… lets add a TSQL Template

sqltt

 

That will now add base TSQL template into you project, however there is a slight snag , its broken :(

broken

This is confirmed and will be fixed in the next release as stated in this msdn forum thread. Bugger!!!

However, until then, all is not lost,  although the DacFX portion is broken T4 templating still works so a more ‘generic’ solution is available.  The basis of this is remarkably similar to TSQL Smells,  iterate through a project adding the .sql files into a model and examining the DOM for interesting ‘stuff’.

After a bit of playing around, very much a T4 noob, this is the script I came up with:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
<#@ template language="C#" debug="true" hostspecific="true" #>
<#@ assembly name="Microsoft.VisualStudio.Shell.Interop.8.0" #>
<#@ assembly name="EnvDTE" #>
<#@ assembly name="EnvDTE80" #>
<#@ assembly name="VSLangProj" #>
<#@ assembly name="C:\Program Files (x86)\Microsoft SQL Server\120\SDK\Assemblies\Microsoft.SqlServer.TransactSql.ScriptDom.dll" #>
<#@ assembly name="C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\120\Microsoft.SqlServer.Dac.dll" #>
<#@ assembly name="C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\120\Microsoft.SqlServer.Dac.Extensions.dll" #>
<#@ import namespace="Microsoft.VisualStudio.Shell.Interop" #>
<#@ import namespace="EnvDTE" #>
<#@ import namespace="EnvDTE80" #>
<#@ import namespace="Microsoft.VisualStudio.TextTemplating" #>
<#@ import namespace="Microsoft.SqlServer.Dac" #>
<#@ import namespace="Microsoft.SqlServer.Dac.Model" #>
<#@ import namespace="System.IO" #>
<#@ import namespace="System.Collections.Generic" #>
<#@ output extension=".sql" #>
 
-- Dynamic File generated by db
<#
    var hostServiceProvider = (IServiceProvider)this.Host;
    var dte = (DTE)hostServiceProvider.GetService(typeof(DTE));
 
    using (TSqlModel model = new TSqlModel(SqlServerVersion.Sql110, new TSqlModelOptions { }))
    {
        foreach(Project project in dte.Solution)
        {
            IterateThroughProject(project.ProjectItems,model);
        }
 
        List<TSqlObject> allTables = GetAllTables(model);
        foreach (var table in allTables)
        {
#>
--				Table <#= table.Name.Parts[0] #>.<#= table.Name.Parts[1] #>
<#
 
        }
    }
 
#>
-- File Done
<#+
 
    public List<TSqlObject> GetAllTables(TSqlModel model)
    {
        List<TSqlObject> allTables = new List<TSqlObject>();
 
        var tables = model.GetObjects(DacQueryScopes.All, ModelSchema.Table);
        if (tables != null)
        {
            allTables.AddRange(tables);
        }
        return allTables;
    }
 
 
 
    private void IterateThroughProject(ProjectItems PrjItems,TSqlModel model)
    {
        foreach(ProjectItem PrjItem in  PrjItems)
        {
 
            if(PrjItem.Name.EndsWith(".tt", StringComparison.OrdinalIgnoreCase)){ // Dont Load the files we want to build
                continue;
 
            }
            if(PrjItem.ProjectItems!=null)
            {
                IterateThroughProject(PrjItem.ProjectItems,model);
            }
            if(//PrjItem.Object.GetType().ToString() == "Microsoft.VisualStudio.Data.Tools.Package.Project.DatabaseFileNode" && 
                PrjItem.Name.EndsWith(".sql", StringComparison.OrdinalIgnoreCase))
            {
#>
--				This is a sql file and will be processed
--				<#= PrjItem.FileNames[0] #>
<#+
                if (!PrjItem.Saved)
                {
                    PrjItem.Save();
                }
                StreamReader Reader = new StreamReader(PrjItem.FileNames[0]);
 
                string Script = Reader.ReadToEnd();
                model.AddObjects(Script);
            }
        }
    }
 
#>

This should all be fairly self explanatory, we iterate through the project in the member function IterateThroughProject adding any found .sql files to the model. Then use the model.GetObjects member to find all tables in the model, iterate over that list printing schema and table name.

I have seen a few clunky TSQL generation routines written in TSQL but i think porting those into T4 templating and having them built directly into a dacpac will be a big boon.

Have fun

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

2009-02-23

1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...

2009-02-17

1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.

2009-02-13

360 reads