mSQL - SSIS Tutorial - Setting Up A Template

Setting up a template

1) Launch SSDT, first create a new integration services project, then drop a key generation task onto the package



2) rename the task as a best practice, when there are multiple tasks with similar names, it may be harder to keep track later’


3) double click on the task, or right click and choose edit to open the task editor


4) If you used the tasks before and chose the US template, this is probably the first thing you’ve seen when choosing the template, but it doesn’t tell you how. 


*Templates were a new addition to mSQL starting with 1.4.8, so if you don’t see them then you’re running an older version which uses a single template xml for all tasks which is stored in the SSIS folder in program files and will require admin permissions to edit.


5) lets start out by clicking the manage button

6) click the US template then click copy

7) click on the copy then click rename, give it a more appropriate name

At this point, if you select that template going forward, that will get that warning to go away, and by using that template you can avoid most of the errors that may go along with an upgrade being done without proper QA.


8) Now let's take a look at a template:

Click edit on the template


9) More advanced users may want to make changes to the template xml, or support may ask you to change the template so that you can help them troubleshoot.
The most common change is that support will ask you to do is enable 'outputCompareResults' , this makes it easier to troubleshoot down the line if we're trying to understand why something is scoring sure, likely, or possible

– support may also ask you to locate the debuglog node and change level to 4, when you're done with debugmode you'll want to remember change it back to 0, otherwise the log will just keep growing and growing and could impact performance after a few months.

Other common changes may include disabling the reports (search for reports)
Another is if you find the groupmatches task is taking too long because you have a very high duplication rate, sometimes a quick fix is is disabling MRI by setting this to false

<masterRecordIdentification enabled="true" />


Lastly, if your data is always in caps, and you turn off consider casing, if you change it here, you don’t need to always remember to change it in the key generation task every time you make a new one

Some options (outputCompareResults, Reporting and debuglog) can only be changed through the template.

Templates may be useful as different departments or clients may have different requirements, so it's less the end user needs to remember to change when setting up a new process in the long run.