Had a nice day at SQL Saturday #336 in Utrecht! The PowerPoint slides of my SSIS Development Best Practices session are available for download. I added some screens, text and URL's for additional information (see notes in PowerPoint)
Showing posts with label DEVELOPMENT. Show all posts
Showing posts with label DEVELOPMENT. Show all posts
Had a nice day at SQL Saturday #336 in Utrecht! The PowerPoint slides of my SSIS Development Best Practices session are available for download. I added some screens, text and URL's for additional information (see notes in PowerPoint)
Case
Team Foundation Server (TFS) is a handy tool when you work with multiple people on the same visual studio project. You can check out the files you work on and you still have the previous versions if you mess up. How do you get TFS working for SSIS 2012?
Solution
SSIS 2012 uses SQL Server Data Tools for Visual Studio 2010 for development, but you can also use Visual Studio 2012. They are called Visual Studio 2010/2012 Shell. Both have a different version of Team Explorer:
Visual Studio 2010: Microsoft Visual Studio Team Explorer 2010
Visual Studio 2012: Team Explorer for Microsoft Visual Studio 2012
This blog post shows how to install Team Explorer and shows how to setup Visual Studio to use TFS for SSIS. I have tested these for TFS 2010 and 2012. The SQL Server version that I used was Developer Edition 11.0.2100.60 RTM and VS2010 Shell 10.0.40219 SP1 and VS2012 Shell 11.0.50727.1 RTM. For SSIS 2008 (R2) and VS2008 Shell see this earlier blogpost. If your boss didn't purchase TFS then you could also get a free online TFS workspace.
In Part I covers:
A) Install Team Explorer for Visual Studio 2010
B) Install Team Explorer for Visual Studio 2012
C) Setup Visual Studio to use TFS
In Part II you will read:
D) Adjusting development process
A) Install Team Explorer for Visual Studio 2010
Use the 2010 download link above. It's an ISO file. Mount or extract the iso file and execute the setup file. Next follow the setup screens. There are no real options to customize the installation: Next, Accept, Next, Install, Finish.
Reinstall Service Pack 1
If you have Service Pack 1 installed for Visual Studio 2010, then you have to reinstall SP1 when you finish installing Team Explorer. Else you could get an error like this when starting SSDT or SSMS:
You can download 'Microsoft Visual Studio 2010 Service Pack 1 (Installer)' here: http://www.microsoft.com/en-us/download/details.aspx?id=23691
B) Install Team Explorer for Visual Studio 2012
Use the 2012 download link above. It's an ISO file or an exe. Execute the setup file. Next follow the setup screens. There are no options to customize the installation: Accept, Install and launch VS2012.
C) Setup Visual Studio to use TFS
For both versions of Visual Studio setting up TFS is the same.
If you have updated Visual Studio 2012, then this first step will open the Team Explorer pane on the right side. There you can click on the Connect link to open the window in the next screenshot.
In the second part you read what's next.
Team Foundation Server (TFS) is a handy tool when you work with multiple people on the same visual studio project. You can check out the files you work on and you still have the previous versions if you mess up. How do you get TFS working for SSIS 2012?
Solution
SSIS 2012 uses SQL Server Data Tools for Visual Studio 2010 for development, but you can also use Visual Studio 2012. They are called Visual Studio 2010/2012 Shell. Both have a different version of Team Explorer:
Visual Studio 2010: Microsoft Visual Studio Team Explorer 2010
Visual Studio 2012: Team Explorer for Microsoft Visual Studio 2012
This blog post shows how to install Team Explorer and shows how to setup Visual Studio to use TFS for SSIS. I have tested these for TFS 2010 and 2012. The SQL Server version that I used was Developer Edition 11.0.2100.60 RTM and VS2010 Shell 10.0.40219 SP1 and VS2012 Shell 11.0.50727.1 RTM. For SSIS 2008 (R2) and VS2008 Shell see this earlier blogpost. If your boss didn't purchase TFS then you could also get a free online TFS workspace.
In Part I covers:
A) Install Team Explorer for Visual Studio 2010
B) Install Team Explorer for Visual Studio 2012
C) Setup Visual Studio to use TFS
In Part II you will read:
D) Adjusting development process
A) Install Team Explorer for Visual Studio 2010
Use the 2010 download link above. It's an ISO file. Mount or extract the iso file and execute the setup file. Next follow the setup screens. There are no real options to customize the installation: Next, Accept, Next, Install, Finish.
Reinstall Service Pack 1
If you have Service Pack 1 installed for Visual Studio 2010, then you have to reinstall SP1 when you finish installing Team Explorer. Else you could get an error like this when starting SSDT or SSMS:
 |
| Only some of the Microsoft Visual Studio 2010 products on this computer have been upgraded to Service Pack 1. None will work correctly until all have been upgraded. |
You can download 'Microsoft Visual Studio 2010 Service Pack 1 (Installer)' here: http://www.microsoft.com/en-us/download/details.aspx?id=23691
B) Install Team Explorer for Visual Studio 2012
Use the 2012 download link above. It's an ISO file or an exe. Execute the setup file. Next follow the setup screens. There are no options to customize the installation: Accept, Install and launch VS2012.
C) Setup Visual Studio to use TFS
For both versions of Visual Studio setting up TFS is the same.
 |
| In the Team-menu choose Connect to TFS |
If you have updated Visual Studio 2012, then this first step will open the Team Explorer pane on the right side. There you can click on the Connect link to open the window in the next screenshot.
 |
| Click on Servers-button to add a TFS server |
 |
| Click on Add-button to add a TFS server |
 |
| Add the tfs URL and choose between http and https |
 |
| Click the OK-button and wait |
 |
| Enter your credentials |
 |
| Click the Close-button |
 |
| Select the TFS project |
 |
| Now you have the Team Explorer pane available |
In the second part you read what's next.
Case
I have installed Team Explorer and setup Visual Studio to use it. What's next?
Solution
In Part I you read:
A) Install Team Explorer for Visual Studio 2010
B) Install Team Explorer for Visual Studio 2012
C) Setup Visual Studio to use TFS
This second part covers:
D) Adjusting development process
D) Adjusting development process
Because you can now work with multiple developers on the same project, you have to make some arrangements with your fellow developers, like:
1) Get latest version project
Get the latest version of the project on a regular basis. Otherwise you will miss new packages, project connection managers and project parameters. Do this for example each morning or before you start developing. There is also an option in Visual Studio to automatically get the latest version of the solution when opening it.
2) Get latest version package
Get the latest version of a package before editing it. There is also an option in Visual Studio to automatically get the latest version of a package when checking it out.
3) Adding new package to project
When you add a new package to the project, the project self will be checked out. First first rename the new package, save it and then check in the project and the new (empty/clean) package. Otherwise your fellow developers cannot change project properties or add new packages.
4) Disable multiple check out
Working together on the same file at the same time is nearly impossible, because it's hard to merge the XML of two versions of a package. Therefore you should disable multiple check out in TFS or check out your package exclusively (not the default in TFS).
5) Don't check in faulty packages
Try not to check in package that doesn't work. Especially when you work with the project deployment model, with which you can only deploy the complete project.
6) No large/complex packages
Don’t make packages to large/complex. Divide the functionality over multiple smaller packages, because you can’t work with multiple developers on the same large package at the same time.
7) Sensitive data
The default Package Protection Level is EncryptSensitiveWithUserKey. This will encrypt passwords and other sensitive data in the package with the username of the developer. Because your colleagues will probably have different usernames they can't edit or execute packages that you made without re-entering all sensitive package data.
The easiest way to overcome this, is to use DontSaveSensitive as Package Protection Level in combination with Package Configurations. Then all the sensitive data will be stored in the configuration table or file and when you open the package all this data will be retrieved from the configuration table or file.
If you're using the Project Deployment Model in combination with sensitive parameters instead of Package Configuration, then the easiest workaround is to use EncryptAllWithPassword or EncryptSensitiveWithPassword with a password that is known within the developmentteam.
8) Development standards
When you're developing with multiple people (or someone else is going to maintain your work) then it's good to have some Development Best Practices like using prefixes for tasks and transformations or using templates. This makes it easier to transfer work and to collaborate as a team.
9) Comments
When you check in a package, it's very useful to add a meaningful description of the change. This makes it easier to track history.
10) Branching, Labeling and building
Beside versioning and checking in/out packages there are more interesting functions in TFS that are probably more common in C# and VB.Net programming, but worth checking out. Here are some interesting links about TFS and SSIS:
I have installed Team Explorer and setup Visual Studio to use it. What's next?
Solution
In Part I you read:
A) Install Team Explorer for Visual Studio 2010
B) Install Team Explorer for Visual Studio 2012
C) Setup Visual Studio to use TFS
This second part covers:
D) Adjusting development process
D) Adjusting development process
Because you can now work with multiple developers on the same project, you have to make some arrangements with your fellow developers, like:
1) Get latest version project
Get the latest version of the project on a regular basis. Otherwise you will miss new packages, project connection managers and project parameters. Do this for example each morning or before you start developing. There is also an option in Visual Studio to automatically get the latest version of the solution when opening it.
 |
| Get everything when a solution or project is opened. |
2) Get latest version package
Get the latest version of a package before editing it. There is also an option in Visual Studio to automatically get the latest version of a package when checking it out.
 |
| Get latest version of item on check out. |
3) Adding new package to project
When you add a new package to the project, the project self will be checked out. First first rename the new package, save it and then check in the project and the new (empty/clean) package. Otherwise your fellow developers cannot change project properties or add new packages.
 |
| Adding new package will check out the project |
4) Disable multiple check out
Working together on the same file at the same time is nearly impossible, because it's hard to merge the XML of two versions of a package. Therefore you should disable multiple check out in TFS or check out your package exclusively (not the default in TFS).
 |
| In Team-menu click Team Project Settings, Source Control |
 |
| Uncheck the multiple checkout box |
5) Don't check in faulty packages
Try not to check in package that doesn't work. Especially when you work with the project deployment model, with which you can only deploy the complete project.
 |
| Don't check in faulty packages |
 |
| Check in comments |
http://msdn.microsoft.com/en-us/library/dn463982.aspx
http://www.mattmasson.com/2012/02/thoughts-on-branching-strategies-for-ssis-projects/
http://consultingblogs.emc.com/jamiethomson/archive/2007/08/06/SSIS_3A00_-Team-Development-Experiences.aspx- SSIS release and Source Control:
http://phil-austin.blogspot.com/2012/11/ssis-release-and-source-control.html - Building packages on TFS:
https://blogs.blackmarble.co.uk/blogs/rfennell/post/2013/04/24/Getting-SQL-2012-SSIS-packages-built-on-TFS-20122.aspx
Case
As an external employee I see a lot of SSIS packages at various companies made by a whole bunch of different people. Unfortunately some of those people made Quick & Dirty as a motto in life resulting in hard to read packages. And that's a waste of time for the companies.
Solution
Companies should require both well performing and well documented packages. Here is a list of some basic development Best Practices to achieve clear and manageable packages.
1) No default names and descriptions
Rename all default component names and give them explaining descriptions. This will help other developers that edit your packages. It is also very useful when debugging.
2) Annotations
Use annotations. This is very useful if the Control Flow or Data Flow isn't self describing (for others).
4 Flow directions
Flows should basically go top-down. This will make your packages more readable.
You can use the Auto-format option from SSIS to format your packages
5) Disabled Control Flow tasks
Do not use disabled Control Flow tasks in the Quality assurance or Production environment. If you want to conditionally execute a task at runtime use expressions on your precedence constraints. Do not use an expression on the “Disable” property of the task.
6) Spread large number of packages over serveral Visual Studio Project
You can add more than one projects to your Visual Studio Solution to spread large number of packages. Think about a proper layout. For example a datastaging project and a datawarehouse project.
7) Queries in source and look up components
Don't use too complex queries. Use a readable lay-out and add comments to explain parts of the query. For example:
8) Script Coding Conventions
Use condings conventions when scripting a script task or component. C# and VB.Net both have their own conventions which are widely available on the net.
9) Use naming conventions
Give tasks and transformations a prefix. This makes it easier to read the logging.
10) Use templates
You can create templates for SSIS. Things like logging, configurations and connection managers can be added to these templates.
Let me known if you have items that should be in the list of Development Best Practices!
As an external employee I see a lot of SSIS packages at various companies made by a whole bunch of different people. Unfortunately some of those people made Quick & Dirty as a motto in life resulting in hard to read packages. And that's a waste of time for the companies.
Solution
Companies should require both well performing and well documented packages. Here is a list of some basic development Best Practices to achieve clear and manageable packages.
1) No default names and descriptions
Rename all default component names and give them explaining descriptions. This will help other developers that edit your packages. It is also very useful when debugging.
 |
| No default names and descriptions |
2) Annotations
Use annotations. This is very useful if the Control Flow or Data Flow isn't self describing (for others).
 |
| Use annotations |
3 Group logical work
Use Sequence containers to organize package structures into logical units of work. This makes it easier to identify what the package does. It also helps to control transactions if they are being implemented. * Update: SSIS 2012 has a grouping feature *
Use Sequence containers to organize package structures into logical units of work. This makes it easier to identify what the package does. It also helps to control transactions if they are being implemented. * Update: SSIS 2012 has a grouping feature *
 |
| Use Sequence Containers |
4 Flow directions
Flows should basically go top-down. This will make your packages more readable.
 |
| Design your package Top down |
You can use the Auto-format option from SSIS to format your packages
 |
| Auto Layout is a good start |
5) Disabled Control Flow tasks
Do not use disabled Control Flow tasks in the Quality assurance or Production environment. If you want to conditionally execute a task at runtime use expressions on your precedence constraints. Do not use an expression on the “Disable” property of the task.
 |
| Disabled Control Flow Task |
6) Spread large number of packages over serveral Visual Studio Project
You can add more than one projects to your Visual Studio Solution to spread large number of packages. Think about a proper layout. For example a datastaging project and a datawarehouse project.
7) Queries in source and look up components
Don't use too complex queries. Use a readable lay-out and add comments to explain parts of the query. For example:
-- This query does something
SELECT a.field1
, a.field2
, b.field3
, b.field4
FROM table1 as a
LEFT JOIN table2 as b
on a.field5 = b.field6
WHERE a.field2 = 'x' -- Comment about x
ORDER BY a.field1
8) Script Coding Conventions
Use condings conventions when scripting a script task or component. C# and VB.Net both have their own conventions which are widely available on the net.
9) Use naming conventions
Give tasks and transformations a prefix. This makes it easier to read the logging.
10) Use templates
You can create templates for SSIS. Things like logging, configurations and connection managers can be added to these templates.
Let me known if you have items that should be in the list of Development Best Practices!
