SSIS Naming Convention – Updated for SSIS 2016

I use an SSIS naming convention that is based on assigning a prefix to each type of component. My first version of SSIS naming convention list was based on SSIS 2008 R2. Since then, Microsoft has introduced new connections, tasks, sources and destinations that are mostly related to Azure, Hadoop, ODATA and CDC.

With the launch of SQL Server 2016, I have updated my list to include all those new components and assigned prefixes to them. There are minor changes to some existing prefixes too e.g. RBLDIDXT is now REBLDIDXT and few others like this.

Why Should You Use a SSIS Naming Convention

A naming convention may not appear to be a big deal at the time of SSIS development because you have a GUI development environment that makes it very easy to identify the components. But during the support and maintenance phase, when trying to read the execution logs or error messages, bad naming choices make it difficult to identify the source of errors quickly. Having a consistent naming convention across the enterprise makes a lot of difference in supporting the packages.

Download

Feel free to reuse the conventions in the list or use it as a template to define your own. I’d love to hear your thoughts and suggestions in the comments, Twitter or Facebook.

You can download the complete list at the TechNet Gallery link below –

=> Download SSIS Naming Convention (Updated for SSIS 2016) <=

Example screenshot –

SSIS Naming Convention
Screenshot of the SSIS Naming Convention Excel Workbook

Download Free SQL Server 2016 Developer Edition

Microsoft announced the general availability of SQL Server 2016 today. This version has many path-breaking updates for performance, security and business intelligence. On top of that, the Developer Edition of SQL Server 2016 and 2014 are now completely free! There is no restriction on features, capabilities or how long you can use them! Read on to find out how to download free SQL Server 2016 Developer Edition and other goodies.

In these Developer Editions, the databases do not have to be smaller than X GB, the feature Y will not be disabled, the time is not restricted to Z days (i.e. 180 days) and you don’t have to pay any $ Dollars. These Developer Editions have all the features of the Enterprise Edition, but should not be used for Production.

Download Free SQL Server Developer Edition

To download the Developer Edition, all you need is a free membership to the Visual Studio Dev Essentials and then go to the following URL to download the installation file.
Download Free SQL Server 2014 and 2016 Developer Edition

Download Free SQL Server Tools

If you don’t plan on downloading the full install of SQL Server yet, you can still get the latest tools.

New 2016 Version of SQL Server Management Studio (SSMS)

SSMS is a free client tool for general SQL Server management and TSQL development. It is compatible with prior versions of SQL Server too.
https://msdn.microsoft.com/library/mt238290.aspx

New 2016 Version of SQL Server Data Tools (SSDT)

SSDT is a free development tool that is a subset of Visual Studio IDE. It is focused on database and business intelligence related development i.e. TSQL, SSIS, SSRS and SSAS.
https://msdn.microsoft.com/en-us/library/mt204009.aspx

Download Free eBook

While at your downloading spree, get the final version of the free MS Press eBook that gives an overview of the latest features in the SQL Server 2016 version.

Introducing Microsoft SQL Server 2016
Mission-Critical Applications, Deeper Insights, Hyperscale Cloud

Authors: Stacia Varga (t), Denny Cherry (t), Joseph D’Antoni (t)
Pages: 215
ISBN: 978-1-5093-0195-9
https://info.microsoft.com/Introducing-SQL-Server-2016-eBook.html

Although the title says that it is an introductory book, it is certainly not a beginner’s level text. In its nine chapters, it introduces the new features of SQL Server 2016. So it will be more useful if you have already worked with SQL Server for a while and are aware of features (or possibilities for enhancements) in the previous versions.

Free SQL Server eBook - Introducing SQL Server 2016

Find Top 25 Inefficient Query Plans by CPU, IO, Recompiles, Execution Count

Microsoft TechNet Gallery is a treasure trove of scripts that can save you a lot of coding time or sometimes introduce creative ways of solving a challenge.

I came across this collection of scripts that will show you the top 25 inefficient query plans (in XML format) sorted by CPU, IO, recompiles, execution counts etc.

Download: http://gallery.technet.microsoft.com/Find-inefficient-query-88f4611f

SSIS Components Naming Conventions

Read a New Version of This Blog Post – SSIS Naming Convention – Updated for SSIS 2016

I wrote earlier about my naming conventions for SSIS variables that let me quickly figure out the source of the variable value very easily. This post continues on to the prefix-based naming conventions I use (or plan to use) for the 120+ components in SSIS packages.

While looking around online for SSIS component naming conventions, most of the suggestions are based on Jamie Thompson’s (Blog | @JamieT) post SSIS: Suggested Best Practices and naming conventions. He has tried to limit the prefixes to 3 or 4 characters and included most of the frequently used components. Using the prefixes as described in his post has helped me a lot in the maintenance phase of a package when my memory has faded about the functionality and the workflow. Some more motivation to adopt a naming convention can be found in 31-days of SSIS series by Jason Strate (Blog | @StrateSQL).

My goal was to make an exhaustive list of components with their prefixes so that –

  • I do not need to put on my thinking hat every time I start using a component for the first time.
  • The naming convention can be future-proof and avoid confusion with new components being introduced in SSIS platform.
  • Remove the guesswork (especially for new developers and maintenance teams who may not be SSIS developers primarily) by avoiding prefixes that sound too similar to another.

I created an Excel sheet of all the components (more than 120) that I found listed in MSDN, i.e. containers, connection managers, sources, destinations, tasks, and transformations. My prefixes are more elaborate than Jamie’s for the reasons mentioned above, but that is just my preference. You can use the Excel as a template for you organization and modify the prefixes to you liking.

The following is an example of one section from the Excel.

Download: Get the full Excel workbook at the TechNet Galleries

Example –

SSIS Component Naming Conventions

Awesome collection of free Microsoft eBooks

Eric Ligman at Microsoft has posted a series of blogs posts with an awesome collection of free ebooks (in multiple formats) related to Microsoft technologies like SQL Server, Azure, SharePoint etc.

I’m posting the links to the original posts with full list of books here.

http://blogs.msdn.com/b/mssmallbiz/archive/2012/07/27/large-collection-of-free-microsoft-ebooks-for-you-including-sharepoint-visual-studio-windows-phone-windows-8-office-365-office-2010-sql-server-2012-azure-and-more.aspx

http://blogs.msdn.com/b/mssmallbiz/archive/2012/07/30/another-large-collection-of-free-microsoft-ebooks-and-resource-kits-for-you-including-sharepoint-2013-office-2013-office-365-duet-2-0-azure-cloud-windows-phone-lync-dynamics-crm-and-more.aspx

http://blogs.msdn.com/b/mssmallbiz/archive/2013/06/18/huge-collection-of-free-microsoft-ebooks-for-you-including-office-office-365-sharepoint-sql-server-system-center-visual-studio-web-development-windows-windows-azure-and-windows-server.aspx

 

A TechNet wiki page with links to more books. There could be overlaps with the previous links.

http://social.technet.microsoft.com/wiki/contents/articles/11608.e-book-gallery-for-microsoft-technologies.aspx

 

Another post with links of free Microsoft Office products training –

http://blogs.msdn.com/b/mssmallbiz/archive/2013/09/17/free-microsoft-office-training-resources-and-more-how-to-get-them.aspx

Parent-Child Table Dependency Script

Requirement:

You need to delete and then insert data into tables which have parent-child relationships defined via primary and foreign keys. The delete and insert commands have to be executed in proper sequence.

The sequence for DELETE is Child-before-Parent.

The sequence for INSERT is Parent-before-Child.

I created a quick script in response to a forum post. The script is not the best out there because –

  • It does not handle circular references in relationships
  • It takes 30 seconds or more if run on a database with hundreds of tables

I’m sharing it for my future reference and probably others with smaller number of tables would find it useful too.

Download script at –
http://gallery.technet.microsoft.com/Parent-Child-Table-45fa5268

Further reference:
For a faster (but not with all these output columns) script, look at Erland Sommarskog’s response in the forum thread.

Script output would look like the example below –

TableDependencyReport

How to Read MSDN Forums with an NNTP News Reader

If you indulge in Microsoft MSDN Forums (social.msdn.microsoft.com/Forums) frequently then you might have had a thought about using a News reader.

The MSDN Forums Help Wiki mentions an “NNTP bridge” by Microsoft to configure one’s News reader but that project seems to have been off the shelf since the release of the new MSDN forums. It seems there is no other information about the future availability of a new bridge which would be compatible with the current forums.

There is a CodePlex community project at Community Forums NNTP Bridge that provides the functionality to provide this bridge. Download the zip file (approx 1 MB). Extract and run the setup.exe to install. Its documentation is quite explanatory but I’d like to restate and add to it –

  • The bridge essentially acts as an NNTP server so it has to be running when you plan to use your News Reader. It can be turned off (closed) at other time.
  • You have to use the IP 127.0.0.1 as the NNTP server in your News Reader configuration while setting up the forums. This is not mentioned clearly in an otherwise good documentation. Screenshots below.
  • You have to turn on (enable) the NNTP option in your forum settings page before you can use the bridge.
  • You have to use the same Windows Live login (Hotmail, MSN, Live etc.) account credentials while starting the NNTP bridge server that you use for the forums.
  • Microsoft Outlook can not be used as a News reader but its cousins viz. Outlook Express, or more recently, Windows Live Mail (WLM) can be used as such. WLM is a part of the free Microsoft Essentials package. If you have other favorite news readers then give them a try too.

Other links:

Windows Live Mail Configuration:

WLMConfigurationGrayscale

SSIS Package Development Effort Estimation

I blogged about a DTS to SSIS conversion effort estimation project that I had a chance to work upon previously. I had created an Excel workbook to help in that estimation.

That workbook was not just limited to the DTS to SSIS conversion estimates. It can be used for regular SSIS development project estimations too. I’ve updated the workbook a bit to reflect its generic nature.

The Excel workbook can help in estimating the effort required to create new SSIS packages. The new SSIS could either be a conversion of an existing DTS package or a fresh requirement. The estimation methodology would be similar in both cases.

Benefits of Using this Estimation Methodology

  • Standardize the estimates by using the same rules instead of guessing randomly, which could return widely varying figures depending on whom you talk to.
  • Standardizing also means that the estimation process for small, medium or large projects is the same. The importance of having a standardized process is directly proportional to the project size, to reduce the variance.
  • The estimates can be fine-tuned over time with real-world feedback so that the estimates get better with each new project.
  • Flexible to include the developer skill factor.

Two Steps to Fill the Estimation Workbook

  1. Put in the estimated number of minutes required for –
    a) creating each type of component in SSIS. This time could vary depending on the developer’s experience and skill. Sample values are included in the workbook.
    b) standard time additions for activities like analysis, error handling, logging and documentation. Sample values are included in the workbook.
  2. Update the workbook with the details like the type and number of all tasks, connections, transformations etc. that would be used in the SSIS package.
    a) If the basis is a DTS package then open the DTS and count the tasks, connections etc.
    b) If the basis is a fresh requirement then put the details according to those requirements. This will also help you to plan the package development.

A brief Help section is also included in the workbook.

Download

Download at: http://gallery.technet.microsoft.com/DTS-to-SSIS-Conversion-7153a957

How to monitor IDENTITY column values in SQL Server

One (lesser known) task of a DBA should be to keep an eye on the values of the IDENTITY columns so that they do not hit the ceiling for their data type and catch you by surprise. Not a pleasant one, of course. This is all the more important if you have tables that grow quickly.

Vyas Kondreddi has a nice script to look for current IDENTITY values in all tables of a database and compare that value against its datatype. It could be useful to automate it as an alert or run it manually once in a while. He has versions of the script for SQL Server 7.0, 2000, 2005 and later but all of them run against one database at a time which might make it a little inconvenient to run against all your 200 databases.

I’ve taken the script compatible with SQL Server 2005 and later, and enhanced it a bit to run it against all databases in one go using the undocumented stored procedure SP_MSFOREACHDB. There goes your excuse to procrastinate this!

Change the value against the TOP keyword to suit your preference. I’ve used TOP 5.

Download my script from the TechNet Gallery link –
http://gallery.technet.microsoft.com/Monitor-IDENTITY-column-fd9c6552

The output would look like the screenshot below.

MonitorIdentityColumns

DTS to SSIS Conversion Effort Estimate Workbook

I had to work on providing an effort estimate to convert multiple DTS packages into corresponding SSIS packages. An automated conversion tool was not available to us so the SSIS packages were to be built from scratch based on the existing DTS packages.

I took this opportunity to catalog all our DTS packages and created an Excel workbook to aid in the estimation. The formulas in the workbook calculate the effort in person-days for the rewrite of the current DTS logic into a SSIS package.

I approached this requirement as follows –

  1. Open each DTS package and used the Excel workbook to make an inventory of the type and number of all the tasks and connections in the package. Obviously, this is a repetitive and the most time consuming activity in the whole process.
  2. Decide on an estimated number of minutes required to recreate each type of task/connection in SSIS. This time could vary depending on the developer’s experience and skill.
  3. Lastly, put in standard time additions for activities like analysis, error handling, logging and documentation.

You can try that estimation workbook by downloading it at the link give below and tweaking the values to suit your needs. While you are there, don’t forget to rate it! 🙂 Please let me know in the comments section of this post if there are any suggestions or questions.

Download from the Technet Gallery link –
DTS to SSIS Conversion Effort Estimation Template.xlsx  approx size: 30 KB
http://gallery.technet.microsoft.com/DTS-to-SSIS-Conversion-7153a957

Do you have some of your own SSIS estimation tips? Please share in the comments section here or at this SSIS Development Effort Estimation Methodologies Wiki on TechNet.