Creating templates for use in BIDS (SQL Spackle)

Question

Post reply

Creating templates for use in BIDS (SQL Spackle)

WayneS

SSC Guru

Points: 95460
More actions
April 20, 2011 at 11:15 pm

#389337

Comments posted to this topic are about the item Creating templates for use in BIDS (SQL Spackle)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Samuel Vella SSCrazy Eights Points: 8055 More actions · Answer 1

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 THIS

Instead, 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.

Michael Lysons SSCertifiable Points: 6491 More actions · Answer 2

Michael Lysons

SSCertifiable

Points: 6491

April 21, 2011 at 2:05 am

#1314357

Excellent tip - thanks very much 🙂

gonsas Valued Member Points: 63 More actions · Answer 3

Samuel Vella (4/21/2011)
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.

Nice tip!

I'd also add that, after adding the template package, one should use BIDS Helper to reset GUIDs

LightVader Hall of Fame Points: 3810 More actions · Answer 4

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)?

The opinions expressed herein are strictly personal and do not necessarily reflect the views or policies of my employer.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 5

Nice spackle Wayne, thanks!

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

BIMind SSChasing Mays Points: 628 More actions · Answer 6

LightVader (4/21/2011)
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)?

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?

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 7

LightVader (4/21/2011)
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)?

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 (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.

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

WayneS SSC Guru Points: 95460 More actions · Answer 8

Samuel Vella (4/21/2011)
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 THIS
Instead, 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.

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!)

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

gonsas Valued Member Points: 63 More actions · Answer 9

LightVader (4/21/2011)
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)?

Maybe an overkill, but I'd suggest creating hardlinks for the template package files (perhaps using a shell extension).

WayneS SSC Guru Points: 95460 More actions · Answer 10

LightVader (4/21/2011)
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)?

Very good question... I'll look into it, and see if I can find anything.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

LightVader Hall of Fame Points: 3810 More actions · Answer 11

jcrawf02 (4/21/2011)
LightVader (4/21/2011)
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)?
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 (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.

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.

The opinions expressed herein are strictly personal and do not necessarily reflect the views or policies of my employer.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 12

Great tips Wayne - thanks

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

WayneS SSC Guru Points: 95460 More actions · Answer 13

gonsas (4/21/2011)
Samuel Vella (4/21/2011)
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.
Nice tip!
I'd also add that, after adding the template package, one should use BIDS Helper to reset GUIDs

I just found this MS KB article that states, at the end,

Note 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.

Edit: Here's another MS msdn link for making a PACKAGE template.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

gonsas Valued Member Points: 63 More actions · Answer 14

WayneS (4/21/2011)
I just found this MS KB article that states, at the end,
Note 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.

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 weird errors[/url] from happening.