Configuring BIDS 2008 / 2008 R2 and Visual Studio 2008 to work with Database & Business Intelligence Projects and Team Foundation Server (TFS)

Working with the SSIS, SSAS, and SSRS Business Intelligence projects along with Database projects in the same Visual Studio 2008 development environment can be challenging as there is no clear documentation how to enable both set of projects.  Database projects are supported by Visual Studio 2008 Developer, Professional and Team System Database Editions. The  SSIS, SSAS, and SSRS Business Intelligence projects are supported by the Visual Studio 2008 shell that comes with SQL Server 2008 / 2008 R2.

If you have BIDS 2008 / 2008 R2 installed by itlself and you try to open a database project you get an error saying that this type of project is not supported. You get the same error if you have Visual Studio 2008 installed by itself and try to open one of the Business Intelligence projects. The reason behind these errors is that BIDS 2008 / 2008 R2 does not come with the database project template and Visual Studio 2008 does not come with the SSIS, SSAS, and SSRS Business Intelligence project templates.

So how do you get both set of templates in a single development environment?

In order to get both set of templates in the same development environment, you need to install both Visual Studio 2008 and BIDS in the right order as described in the following steps:

1. Uninstall all of these if you already have them installed in your environment:
   – Visual Studio 2008
   – BIDS 2008 / 2008 R2
   – Team Explorer 2008
2. Install Visual Studio 2008
3. Install Visual Studio 2008 Service Pack 1
   http://www.microsoft.com/en-us/download/details.aspx?id=10986
4. Install BIDS from your SQL Server 2008 / 2008 R2 installation media

At this point should have a working Visual Studio environment with both set of projects.  A quick way to verify that the installation was successful, is to take a look at the Visual Studio version in the Help|About page. It will show as Version 9.0.30729.4462 QFE as shown in the picture below. QFE stands for Quick Fix Engineering.

When you launch Visual Studio 2008 or BIDS 2008 / 2008 R2 you will be prompted to select the default environment settings. Since I work with the Business Intelligence projects most of the time, I select the Business Intelligence environment. This is an option presented only the first time you open Visual Studio. To change this setting, use the Import and Export Settings wizard, which is available on the Tools menu. For more information on choosing and changing the environment settings go to http://msdn.microsoft.com/en-us/library/6k364a7k(v=vs.90).aspx.

Connecting to TFS 2005 and 2008

To connect to TFS 2005 and 2008 you will need to download and install Team Explorer 2008 from http://www.microsoft.com/en-us/download/details.aspx?id=16338.

If your end goal is to be able to work with database projects, business intelligence projects and connect to TFS 2005 or TFS 20008 the complete steps are as follows:

1. Uninstall all of these if you already have them installed in your environment:
   – Visual Studio 2008
   – BIDS 2008 / 2008 R2
   – Team Explorer 2008
2. Install Visual Studio 2008
3. Install Visual Studio 2008 Service Pack 1
   http://www.microsoft.com/en-us/download/details.aspx?id=10986
4. Install BIDS from your SQL Server 2008 / 2008 R2 installation media
5.  Install Team Explorer 2008
   http://www.microsoft.com/en-us/download/details.aspx?id=16338.

Connecting to TFS 2010

In some cases, the solutions & projects are 2008 / 2008 R2 projects but the repository is TFS 2010 or TFS 2012. Even if you installed Team Explorer 2008 you will get an error when trying to connect to a TFS 2010 or TFS 2012 server. The reason for this is due to the fact that Team Explorer 2008 does not support full URL paths (i.e. https://myservername/mytfs/mycollection) in the TFS server name section.

To fix this issue you will need to download and install the Forward Compatibility Update Team Explorer 2008 SP1 for Team Foundation Server 2010 (http://www.microsoft.com/en-us/download/details.aspx?id=10834).

Notice that this update can only be applied to Team Explorer 2008 SP1 (Service Pack 1). The curve ball here is that there is no Team Explorer 2008 SP1 available  as a download. In order to turn Team Explorer 2008 into Team Explorer 2008 SP1 is to apply the Visual Studio 2008 SP1 to it. This means that if you followed steps 1 to 4 above, you will need to repeat Step 3 (Re-install Visual Studio 2008 SP1).

If your end goal is to be able to work with database projects, business intelligence projects and connect to TFS 2010 the complete steps are as follows:

1. Uninstall all of these if you already have them installed in your environment:
   – Visual Studio 2008
   – BIDS 2008 / 2008 R2
   – Team Explorer 2008
2. Install Visual Studio 2008
3. Install Visual Studio 2008 Service Pack 1
   http://www.microsoft.com/en-us/download/details.aspx?id=10986
4. Install BIDS from your SQL Server 2008 / 2008 R2 installation media
5. Install Team Explorer 2008
   http://www.microsoft.com/en-us/download/details.aspx?id=16338
6. Re-install Visual Studio 2008 SP1
7. Install the forward compatibility update for Team Explorer 2008 SP1 for Team Foundation Server 2010
   http://www.microsoft.com/en-us/download/details.aspx?id=10834

Connecting to TFS 2012

To connect to TFS 2012 the following update is required :

Visual Studio 2008 SP1 Compatibility GDR for Visual Studio 2012 Team Foundation Server and Team Foundation Service Preview (http://www.microsoft.com/en-us/download/details.aspx?id=29983).

If your end goal is to be able to work with database projects, business intelligence projects and connect to TFS 2012 the complete steps are as follows:

1. Uninstall all of these if you already have them installed in your environment:
   – Visual Studio 2008
   – BIDS 2008 / 2008 R2
   – Team Explorer 2008
2. Install Visual Studio 2008
3. Install Visual Studio 2008 Service Pack 1
   http://www.microsoft.com/en-us/download/details.aspx?id=10986
4. Install BIDS from your SQL Server 2008 / 2008 R2 installation media
5. Install Team Explorer 2008
   http://www.microsoft.com/en-us/download/details.aspx?id=16338
6. Re-install Visual Studio 2008 SP1
7. Install Visual Studio 2008 SP1 Compatibility GDR for Visual Studio 2012 Team Foundation Server and Team Foundation Service Preview
   (http://www.microsoft.com/en-us/download/details.aspx?id=29983

Hopefully this post helps you get your environment all squared away.

Advertisement

The affordable self-service BI revolution has started.

I am officially announcing that I have now become an independent consultant and business owner. Becoming independent has always been my dream and I have now decided to follow that dream. I believe it is the right time to do it for my family and myself. It’s time to soar!

Why did I leave Pragmatic Works?

Simple. I wanted to follow my career goals and carve my own path. It was a hard decision to leave Pragmatic Works as I really enjoyed the work environment and had great relationship with the top brass Brian Knight, Adam Jorgensen, and Sam Washburn as well as my co-workers. The folks at Pragmatic Works gave me a lot of opportunities to learn and grow for which I am forever grateful. It is a great company to work for and highly recommend working for them. I would return to work for them in a heartbeat.

What are my plans?

First of all, I have created a corporation, called Agile Bay, Inc. (http://www.agilebay.com) through which I will be providing Business Intelligence consulting services, Software Development, Training and Staffing services. I will also be doing contract work and hiring BI and Software developers to take on some of these jobs.

My plan is to focus on self-service Business Intelligence solutions using the Microsoft toolset, including:

• SQL Server Database Services
• Analysis Services
• Reporting Services
• Master Data Services
• Data Quality Services
• SharePoint
• Performance Point
• PowerPivot
• PowerView
• & more…

I will continue leading, volunteering and sharing my knowledge in the SQL Server and Business Intelligence community and in PASS.

Enter the affordable self-service BI revolution

The new release of SQL Server 2012 will bring a lot to offer in the BI space. I truly believe it will be a game changer and many companies will embark in new Data Warehouse, Business Intelligence and Master Data Management projects. Some of these projects may have been put on hold due to licensing and development costs. This is where SQL Server 2012 will shine as it introduces lower BI licensing costs with the new SQL Server Business Intelligence Edition. Organizations will no longer require Enterprise Edition to do all the cool things Microsoft has to offer with their BI suite.

Additionally, the shift to the BI Semantic Model and the focus on self-service BI will open a lot of doors to consulting firms with lower overhead like mine. Enter the affordable self-service BI revolution!

Hitting the ground running

I am very grateful to the fact that the word has spread out among my immediate professional network and have already been booked for projects and contract work several months ahead.

I have tried not to market myself or my company too much during this initial phase as I want to manage my growth more organically. I am in the process of hiring BI developers at all levels, so if you are interested feel free to contact me via http://www.agilebay.com/#!contact.

 Mission

My mission is to empower individuals and organizations through the Microsoft Business Intelligence Toolset.

Vision

My vision is to be the catalyst and leader of the affordable self-service Business Intelligence revolution.

Value proposition

There are many alternatives available when chosing a consulting firm to help you achieve a succesful Business Intelligence, Data Warehouse and Master Data Management implementation.  My value proposition is to achieve this same success at an affordable budget and by providing you with the knowledge transfer and mentoring needed to continue your own development efforts.

The sales pitch

 You need an experienced and expert professional to help you with your design and development efforts.  I have experience and expertise. Let’s talk.

You may contact me through my company’s website at http://www.agilebay.com/#!contact or through email: info@agilebay.com. 

I’m also available for any quick help through email at jchinchilla@sqljoe.com. Make sure to check out my blog at http://www.sqljoe.com as well.

The CASE of the missing non-NULL T-SQL Error: None of the result expressions in a CASE specification can be NULL.

Recently, while doing some data scrubbing for a customer I got an interesting error in SSMS with one of my CASE statements. The error received was:

None of the result expressions in a CASE specification can be NULL.

It was a long T-SQL script (and a long night) and could not understand the error message. After a couple minutes BinGling (Google +Bing) around the web, I still could not find the root cause, so I decided to comment out every single CASE statement and run one by one until I pinpointed the offending syntax.

 

To my surprise, the issue was very simple, yet undocumented. The T-SQL syntax I was writing was somewhat as follows:

 

SELECT ProductID,

CASE WHEN SerialNumber like ‘X%’ THEN NULL

WHEN SerialNumber = ‘0’ THEN NULL

WHEN SerialNumber = ‘-‘ THEN NULL

WHEN SerialNumber = ‘Unknown’ THEN NULL

END AS SerialNumber_Clean

FROM tblProduct

 

After executing this script the error mentioned above is raised. The same error is raised even if we rewrite the script as a simple CASE statement:

 

SELECT ProductID,

CASE SerialNumber

WHEN ‘0’ THEN NULL

WHEN  ‘-‘ THEN NULL

WHEN ‘Unknown’ THEN NULL

END AS SerialNumber_Clean

FROM tblProduct

 

So what if we add an ELSE statement as follows:

 

SELECT ProductID,

CASE WHEN SerialNumber like ‘X%’ THEN NULL

WHEN SerialNumber = ‘0’ THEN NULL

WHEN SerialNumber = ‘-‘ THEN NULL

WHEN SerialNumber = ‘Unknown’ THEN NULL

ELSE NULL

END AS SerialNumber_Clean

FROM tblProduct

 

We still get the same error. The issue as the raised error describes can be narrowed down in that you cannot explicitly return NULL for every resulting expression. There must be at least one non-explicit NUL in the resulting expression. For example, we can rewrite the script correctly as follows:

 

SELECT ProductID,

CASE WHEN SerialNumber like ‘X%’ THEN NULL

WHEN SerialNumber = ‘0’ THEN NULL

WHEN SerialNumber = ‘-‘ THEN NULL

WHEN SerialNumber = ‘Unknown’ THEN NULL

ELSE SerialNumber

END AS SerialNumber_Clean

FROM tblProduct

 

As you can observe, adding an ELSE statement that does not return another explicit NULL makes the script work and as a matter of fact, should be the correct syntax. Interestingly, ELSE is not necessary to make the script valid. The script will run even without an ELSE expression, but only if at least one of the resulting values is not an explicit NULL. The following script runs successfully (notice no ELSE):

 

SELECT ProductID,

CASE WHEN SerialNumber like ‘X%’ THEN NULL

WHEN SerialNumber = ‘0’ THEN NULL

WHEN SerialNumber = ‘-‘ THEN NULL

WHEN SerialNumber = ‘Unknown’  THEN NULL

WHEN SerialNumber = ‘No Serial’ THEN ‘Non-Serialized Product’

END AS SerialNumber_Clean

FROM tblProduct

 

Books on Line should include this caveat on their documentation. http://msdn.microsoft.com/en-us/library/ms181765.aspx

 

The following sections should be modified as follows:

THEN result_expression

Is the expression returned when input_expression equals when_expression evaluates to TRUE, or Boolean_expression evaluates to TRUE. result expression is any valid expression. If no else_result_expression is specified or if else_result_expression is set to return an explicit NULL, at least one result_expression has to be specified to return a non-explicit NULL.

 

ELSE else_result_expression

Is the expression returned if no comparison operation evaluates to TRUE. If this argument is omitted and no comparison operation evaluates to TRUE, CASE returns NULL. else_result_expression is any valid expression. The data types of else_result_expression and any result_expression must be the same or must be an implicit conversion and at least one of them should be specified to return a non-explicit NULL.

Todo lo que debes saber sobre SSIS en 1 hora!

Gracias a lo asistentes de mi charla Todo lo que debes saber sobre SSIS en 1 hora! en el evento 24HOP LATAM. La verdad me agrado mucho presentar en espanol. Aun cuando mi primera lengua es espanol, todas las presentaciones tecnicas en el pasado las he realizado en ingles.

Si deseas ver la presentacion PowerPoint puedes bajarla dando click en la imagen a continuacion.

Tambien puedes bajar el proyecto que utilize en mi presentacion dando click en la imagen a continuacion. Nota: Solo podras utilizar la base de datos y proyecto en SQL Server Denali, no servira en SQL Server 2008 o 2005.

T-SQL: Identifying, inserting and removing spaces in strings

Frequently, when working with strings you will need to identify, insert or remove spaces before, after or in between characters.

For example, you may want to show customer full names  by concatenating the columns that hold the different parts of customer names like FirstName, MiddleName, and LastName, separating each name part with a blank space. You may also want to remove spaces before, after or in between characters from email or website addresses. It is important to understand the different string functions available for you in T-SQL that may help you accomplish any of the tasks mentioned above:

Using single-quotes with space in between  ‘ ‘ versus char(32) to insert spaces between characters

Example #1:

DECLARE @FirstName varchar(25), @LastName varchar(25)
SET @FirstName = ‘Jose’
SET @LastName = ‘Chinchilla’
SELECT @FirstName + ‘ ‘ + @LastName — (one space between single-quotes)
–Result=> Jose Chinchilla

Example #2:

SELECT @FirstName + char(32) + @LastName — (single space special character represented by char(32))
–Result=> Jose Chinchilla

From these two examples we can observe that we get the same results by using single quotes with an empty space in between and char(32). The reason is because char(32) is the ASCII code value of a space character in T-SQL.

SELECT ASCII(‘ ‘)
Result=> 32

SELECT CHAR(ASCII(‘ ‘))
Result=> _  — (the underscore character ‘_’ represents a blank space for clarification purposes, no actual underscores will show)

SELECT CHAR(32)
Result=> _ — (the underscore character ‘_’ represents a blank space for clarification purposes, no actual underscores will show)

Using LEN vs DATALENGTH to get the number of characters in a string

Example #1:

DECLARE @FullName varchar(25)
SET @FullName = ‘ Jose Chinchilla ‘ — (notice spaces before and after the full name)
SELECT LEN(@FullName) –Result=>16

LEN will only count leading spaces and spaces in between the string but will not count trailing spaces.

Example #2:

SELECT DATALENGTH(@FullName)
–Result=>17

DATALENGTH will count spaces in between and both leading and trailing spaces as well.

Using LTRIM and RTRIM to remove leading and trailing spaces

DECLARE @FirstName varchar(25)
SET @FirstName = ‘ Jose ‘; — (notice space before and after the word Jose)

SELECT @FirstName
–Result=> _Jose_  — (the underscore characters ‘_’ represent a blank space for clarification purposes, no actual underscores will show) 

SELECT LTRIM(@FirstName)
–Result=> Jose_  –(only leading space is removed, the underscore character ‘_’ represent a blank space for clarification purposes, no actual underscore will show)

SELECT RTRIM(@FirstName)
–Result=> _Jose  — (only trailing space is removed, the underscore character ‘_’ represent a blank space for clarification purposes, no actual underscore will show)

SELECT LTRIM(RTRIM(@FirstName))
–Result=> Jose  — (both leading and trailing spaces are removed)

By nesting LTRIM and RTRIM you get similar results as the TRIM function in Excel and other programming languages. Unfortunately, there is no TRIM function in T-SQL.

Using CHARINDEX to find the position of all spaces in a string

In order for CHARINDEX to work properly you need to declare the data type length. For example: DECLARE @EmailAddress varchar(100) instread of DECLARE @EmailAddress  varchar. The same applies to nvarchar, char and nchar. You may use varchar(max) and nvarchar(max).

It is important to understand that CHARINDEX will only return the position of the first instance of the character you are looking for, in this case a space or char(32). To continue looking for the rest of the positions where you have empty spaces in your @EmailAddress variable you would need to loop through the entire string until no more spaces are found.

DECLARE @EmailAddress varchar(100), @SpacePositions varchar(max), @PositionIndex int
SET @EmailAddress = ‘ jchinchilla @ sqljoe.com ‘ — (notice leading and trailing spaces and spaces before & after the @ sign)
SET @SpacePositions = ”
SET @PositionIndex = CHARINDEX(char(32),@EmailAddress)

WHILE @PositionIndex > 0
BEGIN
SET @SpacePositions =
CASE
    WHEN CHARINDEX(char(32),@EmailAddress,@PositionIndex+1) > 0
THEN @SpacePositions + CONVERT(varchar,@PositionIndex) + ‘, ‘
ELSE @SpacePositions + CONVERT(varchar,@PositionIndex)
END

SET @PositionIndex = CHARINDEX(char(32),@EmailAddress,@PositionIndex+1)
END

SELECT @SpacePositions as  SpacePositions
–Result=> 1, 13, 15, 26

Using REPLACE to find and remove all spaces in a string

REPLACE will allow you to substitute all spaces in a string with an empty string, thus removing all spaces in between characters. In the following example, all spaces represented by char(32) will be replaced with an empty string ”.

DECLARE @EmailAddress varchar(max)
SET @EmailAddress = ‘ jchinchilla @ sqljoe.com ‘ — (notice leading and traling spaces and spaces before and after the @ sign)

SELECT REPLACE(@EmailAddress, CHAR(32),”)
–Result=> jchinchilla@sqljoe.com –(all spaces have been removed)

Using REPLICATE to insert ‘n’ number of spaces in a string

SELECT  ‘A’ + REPLICATE(‘ ‘,5)  + ‘Z’
Result=> A_____Z  — (the underscore characters ‘_’ represent a blank space for clarification purposes, no actual underscores will show)

SELECT  ‘A’ + REPLICATE(char(32),5)  + ‘Z’
Result=> A_____Z  — (the underscore characters ‘_’ represent a blank space for clarification purposes, no actual underscores will show)

T-SQL LOWER(): Change an all uppercase string to lower case

The T-SQL LOWER() command allows you to change an uppercase string to a lowercase string.

For example, it will allow you to change the word HELLO to hello.

Example 1:

Declare @MyVar varchar(50);
Set @Myvar=’HELLO’;
Select LOWER(@MyVar)   => Output will be hello in lowercase.

You can use the T-SQL LOWER() command with other commands to Capitalize only the first letter of a word.

For example, change FLORIDA  to Florida.

Example2:

DECLARE @MyVar varchar(50);
SET @Myvar=’FLORIDA’;
SELECT LEFT(@MyVar,1) +SUBSTRING(LOWER(@MyVar),2,49)

Since my string is of length 50, notice that I am selecting the first letter that is already in uppercase and concatenating with the rest 49 letters in lowercase starting with the second position in the string.