"SQL Spackle" is a collection of short articles written based on multiple requests for similar code. These short articles are NOT meant to be complete solutions. Rather, they are meant to "fill in the cracks".

--Phil McCracken

Introduction

In Microsoft SQL Server 2005, Microsoft introduced the Business Intelligence Development Studio (BIDS) for working with SQL Server Reporting Services reports, and SQL Server Integration Services packages. BIDS is developed on the Visual Studio engine, so the interface may already be familiar to many developers.

Whenever you add a new item to a solution, BIDS presents a dialog box allowing you to select from a template. Typically, you would select the basic SSIS package or SSRS report, and then spend a bunch of time setting it up to match your other reports or package default configurations. Wouldn't it be nice if you could have a canned template, ready to use, that had a baseline to develop your SSIS Package or SSRS report from so that the reports would all have the same appearance to your customers, and the packages would all be set up with the proper servers and configurations to run against? Well, you can, and in this SQL Spackle article, I'll tell you how to do it.

Step 1 - make the files to become your template

The first step is to create the SSIS Package or SSRS report files that will become your templates. They can do whatever you want them to - you just need to put it in.

What I have in my SSIS Package template is a single Connection Manager, pointing to the key database. At the package level, I have one variable to contain the SQL Server Name (named SQLServerName). In the Connection Manager, I have an expression to replace the ServerName property with the value in the SQLServerName variable. And finally, in the Package Configurations, I have a single package configuration that contains just the value for the SQLServerName variable. Of course, you should add whatever you need in order to use this package as a template for all of your future packages.

In the SSRS Report template, you would set up the report again for how you would want all of your future reports to be based off of. You might even want to do two - one for Portrait, and one for Landscape. You can include things such as a header that contains the company logo, a footer that contains the userid of the person running the report, and page numbering (Page 1 of x, etc.). Oh - it makes things easier to include that shared data source that you made for all of your reports. Make sure that the report prints and exports fine, and add whatever else you would need in order to use these as templates for your future reports.

Step 2 - making the files become a template

The next step is to take these files, and to make them into templates. The really sad thing is how disgusting easy this is to accomplish - once you know how to do it.

Copy the SSIS package dtsx file to the following directory: C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransformationItems. (If you installed BIDS on a different drive, use that drive. If you are on a 32-bit OS, remove the (x86) part. If you are using SQL 2005, it will be in the "Microsoft Visual Studio 8.0" directory - the 9.0 is for SQL 2008 BIDS.

Copy the Reports rdlx files to the following directory: C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject. The same notes apply for 32/64 bit, installed drive, and SQL version.

While you are in the ProjectItems directory, note that there are directories also for ReportModels and AnalysisServicesProjects. Guess what you can put into those directories?

Step 3 - using the templates

To use these templates, fire up BIDS, and open your solution (either reports or SSIS packages). In the Solution Explorer window, right-click the solution, and from the resulting pop-up menu, select Add, then New Item. In the resulting dialog box, you will see your new template(s) in the "Visual Studio installed templates" section. Select the template, rename it, and click the Add button. Your package or report will be saved into your solution, opened up, and ready to be used.

Now, doesn't that make creating your new reports and packages a lot easier?