SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Wayne Sheffield / Creating templates for use in BIDS (SQL Spackle) / Latest Posts

RE: Creating templates for use in BIDS (SQL Spackle)

Mike McIver — Sat, 23 Apr 2011 05:34:07 GMT

Good info . . . thanks!

RE: Creating templates for use in BIDS (SQL Spackle)

SQLRNNR — Fri, 22 Apr 2011 17:45:15 GMT

Looks like the discussion is taking on some great content as well.

RE: Creating templates for use in BIDS (SQL Spackle)

Daniel Bowlin — Fri, 22 Apr 2011 06:58:29 GMT

I have been using report templates for some time. I didn't know it was possible to make a package template for SSIS. Thanks. You also mention it is possible for report models and SSAS projects. Great tips thanks.

RE: Creating templates for use in BIDS (SQL Spackle)

Evil Kraig F — Thu, 21 Apr 2011 11:44:23 GMT

Very nice article Wayne, thank you. Perfect spackle.

RE: Creating templates for use in BIDS (SQL Spackle)

gonsas — Thu, 21 Apr 2011 09:40:31 GMT

[quote][b]WayneS (4/21/2011)[/b][hr]I just found this [url=http://support.microsoft.com/kb/908018][u]MS KB article[/u][/url] that states, at the end, [quote][b]Note[/b] We recommend that you generate a unique ID property value for the package to make analyzing log data easier. To generate a unique ID property value, click the ID in the Properties pane, and then click Generate New ID. If Generate New ID does not appear in the Properties pane, verify that the package properties appear in the Properties pane. If the properties for an item such as a connection manager or task appear in the Properties pane, Generate New ID is unavailable.[/quote][/quote]But BIDS Helper not only resets the package ID, but also all the tasks' and components' IDs as well (which also appear in the logs).As mentioned in the BIDS Helper wiki page "Reset GUIDs" feature, this prevents some [url=http://datachix.com/2010/01/31/the-case-of-the-mysterious-failing-packages/][u]weird errors[/u][/url] from happening.

RE: Creating templates for use in BIDS (SQL Spackle)

WayneS — Thu, 21 Apr 2011 09:30:02 GMT

[quote][b]gonsas (4/21/2011)[/b][hr][quote][b]Samuel Vella (4/21/2011)[/b][hr]Nice article, very concise (as a Spackle should be I guess)The reason for this is simple, renaming the file in the dialogue box will not change the object name. Waiting until afterwards will give you the option of automatically changing the object name to reflect the file name.[/quote]Nice tip!I'd also add that, after adding the template package, one should use BIDS Helper to [url=http://bidshelper.codeplex.com/wikipage?title=Reset%20GUIDs][u]reset GUIDs[/u][/url][/quote]I just found this [url=http://support.microsoft.com/kb/908018][u]MS KB article[/u][/url] that states, at the end, [quote][b]Note[/b] We recommend that you generate a unique ID property value for the package to make analyzing log data easier. To generate a unique ID property value, click the ID in the Properties pane, and then click Generate New ID. If Generate New ID does not appear in the Properties pane, verify that the package properties appear in the Properties pane. If the properties for an item such as a connection manager or task appear in the Properties pane, Generate New ID is unavailable.[/quote]Edit: Here's [url=http://msdn.microsoft.com/en-us/library/ms345191%28SQL.90%29.aspx][u]another MS msdn link[/u][/url] for making a PACKAGE template.

RE: Creating templates for use in BIDS (SQL Spackle)

SQLRNNR — Thu, 21 Apr 2011 08:17:12 GMT

Great tips Wayne - thanks

RE: Creating templates for use in BIDS (SQL Spackle)

LightVader — Thu, 21 Apr 2011 08:06:41 GMT

[quote][b]jcrawf02 (4/21/2011)[/b][hr][quote][b]LightVader (4/21/2011)[/b][hr]Is there an equally easy way to have the template on the network so all the developers in the company are using the same template (and it would only have to be updated once)?[/quote]I used to use a .cmd file to copy templates and SQL Prompt snippets from a shared location to the local folder on login, you could do similar for this. See attached. Also, Wayne, great article, been thinking about setting up a template for SSIS, so thanks! If anyone is doing this for SSRS, consider using this in tandem with report styles in the database, a la Barry King ([url=http://www.simple-talk.com/sql/reporting-services/reporting-services-with-style/]http://www.simple-talk.com/sql/reporting-services/reporting-services-with-style/[/url]). That way, you can have your template for the layout you want, and use the db to update the actual styles, or apply multiple styles, and change all reports by updating your db values. Very handy.[/quote]I really like the Barry King solution, I think I'll propose that one to my boss. I have to look into the other solution a little bit more. I'm sure I can get it to work for us, but we just had a virus issue so a lot of stuff is locked down right now.

RE: Creating templates for use in BIDS (SQL Spackle)

WayneS — Thu, 21 Apr 2011 07:47:04 GMT

RE: Creating templates for use in BIDS (SQL Spackle)

gonsas — Thu, 21 Apr 2011 07:28:50 GMT

[quote][b]LightVader (4/21/2011)[/b][hr]Is there an equally easy way to have the template on the network so all the developers in the company are using the same template (and it would only have to be updated once)?[/quote]Maybe an overkill, but I'd suggest creating [url=http://msdn.microsoft.com/en-us/library/aa365006(v=vs.85).aspx][u]hardlinks[/u][/url] for the template package files (perhaps using a [url=http://schinagl.priv.at/nt/hardlinkshellext/hardlinkshellext.html][u]shell extension[/u][/url]).

RE: Creating templates for use in BIDS (SQL Spackle)

WayneS — Thu, 21 Apr 2011 07:23:50 GMT

[quote][b]Samuel Vella (4/21/2011)[/b][hr]Another little tip when adding a template:When adding a new item you might be tempted to change the file name in the Add New Item dialogue box.DO NOT DO THISInstead, add the package/report leaving in the default file name, then rename it in the project explorer tree.The reason for this is simple, renaming the file in the dialogue box will not change the object name. Waiting until afterwards will give you the option of automatically changing the object name to reflect the file name.[/quote]Excellent tip Samuel. (And yet another excellent example of why you need to read the discussions on the articles - lot's of goodies in them, like this!)

RE: Creating templates for use in BIDS (SQL Spackle)

jcrawf02 — Thu, 21 Apr 2011 07:20:49 GMT

[quote][b]LightVader (4/21/2011)[/b][hr]Is there an equally easy way to have the template on the network so all the developers in the company are using the same template (and it would only have to be updated once)?[/quote]I used to use a .cmd file to copy templates and SQL Prompt snippets from a shared location to the local folder on login, you could do similar for this. See attached. Also, Wayne, great article, been thinking about setting up a template for SSIS, so thanks! If anyone is doing this for SSRS, consider using this in tandem with report styles in the database, a la Barry King ([url=http://www.simple-talk.com/sql/reporting-services/reporting-services-with-style/]http://www.simple-talk.com/sql/reporting-services/reporting-services-with-style/[/url]). That way, you can have your template for the layout you want, and use the db to update the actual styles, or apply multiple styles, and change all reports by updating your db values. Very handy.

RE: Creating templates for use in BIDS (SQL Spackle)

BIMind — Thu, 21 Apr 2011 07:18:36 GMT

[quote][b]LightVader (4/21/2011)[/b][hr]Is there an equally easy way to have the template on the network so all the developers in the company are using the same template (and it would only have to be updated once)?[/quote]I second that question....no reason to go to all that trouble if you're the only one using it unless its a true template for all developers....so how do you get it across to all devs as a template?

RE: Creating templates for use in BIDS (SQL Spackle)

Koen Verbeeck — Thu, 21 Apr 2011 06:33:14 GMT

Nice spackle Wayne, thanks!

RE: Creating templates for use in BIDS (SQL Spackle)

LightVader — Thu, 21 Apr 2011 06:03:22 GMT

Is there an equally easy way to have the template on the network so all the developers in the company are using the same template (and it would only have to be updated once)?

RE: Creating templates for use in BIDS (SQL Spackle)

gonsas — Thu, 21 Apr 2011 03:30:24 GMT

[quote][b]Samuel Vella (4/21/2011)[/b][hr]Nice article, very concise (as a Spackle should be I guess)The reason for this is simple, renaming the file in the dialogue box will not change the object name. Waiting until afterwards will give you the option of automatically changing the object name to reflect the file name.[/quote]Nice tip!I'd also add that, after adding the template package, one should use BIDS Helper to [url=http://bidshelper.codeplex.com/wikipage?title=Reset%20GUIDs][u]reset GUIDs[/u][/url]

RE: Creating templates for use in BIDS (SQL Spackle)

Michael Lysons — Thu, 21 Apr 2011 02:05:44 GMT

Excellent tip - thanks very much :-)

RE: Creating templates for use in BIDS (SQL Spackle)

Samuel Vella — Thu, 21 Apr 2011 01:56:15 GMT

Nice article, very concise (as a Spackle should be I guess)Another little tip when adding a template:When adding a new item you might be tempted to change the file name in the Add New Item dialogue box.DO NOT DO THISInstead, add the package/report leaving in the default file name, then rename it in the project explorer tree.The reason for this is simple, renaming the file in the dialogue box will not change the object name. Waiting until afterwards will give you the option of automatically changing the object name to reflect the file name.

Creating templates for use in BIDS (SQL Spackle)

WayneS — Wed, 20 Apr 2011 23:15:16 GMT

Comments posted to this topic are about the item [B]Creating templates for use in BIDS (SQL Spackle)[/B]