Creating templates for use in BIDS (SQL Spackle)

  • WayneS

    SSC Guru

    Points: 95341

    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

  • Samuel Vella

    SSCrazy Eights

    Points: 8055

    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: 6468

    Excellent tip - thanks very much 🙂

  • gonsas

    Valued Member

    Points: 63

    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: 3618

    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: 258938

    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

    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

    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: 95341

    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

    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: 95341

    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: 3618

    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: 281210

    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: 95341

    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

    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.

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

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