• Post Categories

  • Browse Blogs

  • Blog Stats

    • 475,763 hits
  • Syndications

    SQLServerPedia Contributor

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.

VS2008QFE

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.

SQL Server Upgrade error: It is not possible to change the SQL Server features to be upgraded in this release. Validation errors. There are no features selected for upgrade.

On a recent project I was asked to “upgrade” a licensed SQL Server 2008R2 Standard Edition to SQL Server 2008R2 Enterprise Edition. I have done this multiple times with no issues. The “kicker” was that the upgrade required a 48-hour turnaround time but no installation media for SQL Server Enterprise was going to be available in that same timeframe due to a delay in the procurement process.

In order to meet project requirements and gain some time, a team member suggested we go ahead and do the “upgrade” using a downloaded copy of SQL Server 2008R2 Evaluation Edition since Evaluation Edition contains all the Enterprise features that were required. Once the installation media for SQL Server 2008R2 Enterprise arrived, we were to simply upgrade the Evaluation Edition to Enterprise Edition.

My immediate response was that going from a licensed edition to an evaluation edition was not possible. It is not a “natural” upgrade path and is actually considered a “downgrade” as you are going from a licensed edition to an unlicensed edition. As a professional I had to back this statement up with official Microsoft documentation. As a geek no matter what the documentation said I had to try it myself to see what happened. So I did both.

Documentation
A quick search of Books on Line on SQL Server 2008R2 led me to the following MSDN article titled “Version and Edition Upgrades” (http://msdn.microsoft.com/en-us/library/ms143393(v=sql.105).aspx) . This article is great as it outlines all the supported upgrade scenarios for all versions and editions of SQL Server. A quick scroll towards the bottom shows that the only two upgrade paths supported for Standard Edition is Enterprise and Datacenter. As a matter of fact this article shows that there is no support going from any other edition to Evaluation Edition.

Additionally, there are a couple of footnotes at the bottom. An important footnote to take a look at is footnote No. 2. Although the statements in footnote No. 2 are within the context of failover clusters, some of them also apply to stand-alone installations. In this footnote it is clearly stated that SQL Server 2008R2 Standard to SQL Server 2008R2 Evaluation upgrade is not supported.

Test

I wanted to see for myself the error message generated if I tried to carry out the upgrade anyways. Of course I did not try to do this on the actual client’s environment. That is a big no no! I decided to spin up my own sandbox virtual environment. The steps I followed on my test were:

  1. Install SQL Server 2008R2 Standard Edition (Database Services, Analysis Services, SSMS, BIDS)
  2. Reboot
  3. Upgrade SQL Server 2008R2 Standard Edition to SQL Server 2008R2 Evaluation Edition using the Upgrade Setup Wizard from Installation Center.

As I went through the Upgrade Setup Wizard I got the following validation error as expected:

In summary, you cannot upgrade from a licensed SQL Server edition to SQL Server Evaluation Edition. It is perfectly possible though, to upgrade from SQL Server Evaluation Edition to other licensed SQL Server editions. For more details on the supported upgrade paths refer to MDSN article: http://msdn.microsoft.com/en-us/library/ms143393(v=sql.105).aspx.

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.

Codeplex: SSIS Community Tasks and Components

I recently bumped into this very exhaustive list of SSIS tasks, components and samples in CodePlex http://ssisctc.codeplex.com. The list is maintained by SQL Server MVP (Blog|Twitter) and  grouped in the following sections:

  • Tools
  • Connection Managers
  •  Log Providers
  • Tasks (for Control Flow)
    • Foreach Enumerators
    • Script Task
    • Script Samples
  • Components (for Data Flow)
    • Script Component Samples
    •  Sources
    • Transforms
    • Destinations

I hope this list keeps being updated and more people contribute. Very useful.

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.

SQL Server Codename “Denali” CTP3 Resources

SQL Server codename “Denali” CTP3 has been released to the public after much anticipation. You may ask yourself, “What happened to CTP2?” The answer is simple. It was a private build for MVPs and some Microsoft partners. By private, I mean it was not released to the general public. The good news is that you did not miss much in CTP2. A lot of areas were incomplete or not working. But that is to be expected as it is a work in progress.

This blog post will serve as a means to gather resources such as links and blog posts regarding SQL Server “Denali” CTP3. Check back soon as I will be adding new resources. If you have a blog post about SQL Server Denali CTP3 please pingback or email me to add it to the list.

Downloads:

SQL Server DENALI CTP3 Demo VHD
http://www.microsoft.com/download/en/details.aspx?id=27253

A HyperV image of SQL Server Denali CTP3 in action, including fully configured services and integration with SharePoint 2010 and Office 2010
The following software is configured on the virtual machine:

  • SQL Server “Denali” CTP3
  • SharePoint 2010
  • Office 2010

SQL Server codename “Denali” Community Technology Preview 3 CTP3
https://www.microsoft.com/betaexperience/pd/SQLDCTP3CTA/enus/default.aspx

SQL Server code name “Denali” Express Core Community Technology Preview 3 (CTP 3)
http://www.microsoft.com/download/en/details.aspx?id=26784

SQL Server code name ‘Denali’ Community Technology Preview 3 (CTP 3) Feature Pack
http://www.microsoft.com/download/en/details.aspx?id=26726

The SQL Server  code name “Denali” CTP 3 Feature Pack is a collection of stand-alone packages which provide additional value for Microsoft® SQL Server® code name ‘Denali’ CTP 3. It includes the latest versions of  tool and components an add-on providers.

Includes:

  • SQL Servercode name “Denali” Master Data Service Add-in for Excel CTP 3
  • SQL Servercode name “Denali” Semantic Language Statistics CTP 3
  • SQL ServerReport Builder for SQL Servercode name “Denali” CTP 3
  • SQL Servercode name “Denali” PowerPivot for  Excel CTP 3
  • SQL Servercode name “Denali” Reporting Services Add-in for  SharePoin Technologies
  • SQL Servercode name “Denali” Data-Tier Application Framework CTP 3
  • SQL Servercode name “Denali” Transact-SQL Language Service CTP 3
  • SQL Servercode name “Denali” Transact-SQL ScriptDom CTP 3
  • SQL Servercode name “Denali” Transact-SQL Compiler Service CTP 3
  • SQL ServerCompact 4.0
  • SQL ServerCompact 4.0 Books On-line
  • SQL ServerJDBC Driver 4.0 Community Technology 2 (CTP 2)
  • Connector 1.1 for SAP BW for SQL Server code name “Denali” CTP 3
  • System CLR Types for SQL Server code name “Denali” CTP 3
  • SQL Servercode name “Denali” Remote Blob Store CTP 3
  • SQL Servercode name “Denali” Books On-line CTP 3
  • SQL Servercode name “Denali” Upgrade Advisor CTP 3
  • SQL Servercode name “Denali” Native Client CTP 3
  • OLEDB Provider for DB2 v4.0 for SQL Server code name “Denali” CTP 3
  • SQL Servercode name “Denali” Command Line Utilities CTP 3
  • SQL ServerService Broker External Activator for SQL Server code name “Denali” CTP 3
  • Windows PowerShell Extensions for SQL Server code name “Denali” CTP 3
  • SQL Servercode name “Denali” Shared Management Objects CTP 3
  • SQL Servercode name “Denali” ADOMD.NET CTP 3
  • Analysis Services OLE DB Provider for SQL Servercode name “Denali” CTP 3
  • SQL Servercode name “Denali” Analysis Management Objects CTP 3
  • SQL ServerDriver for PHP 2.0
  • SQL ServerMigration Assistant
    1. Microsoft SQL Server Migration Assistant for Access
    2. Microsoft SQL Server Migration Assistant for MySQL
    3. Microsoft SQL Server Migration Assistant for Oracle
    4. Microsoft SQL Server Migration Assistant for Sybase
    5.  Microsoft SQL Server Migration Assistant 2008 for Sybase PowerBuilder Applications
  • SQL ServerStreamInsight v1.2

Adventure Works sample databases for SQL Server codename Denali CTP3
http://msftdbprodsamples.codeplex.com/releases/view/55330

Denali CTP3 Adventure Works Sample Databases Readme
http://social.technet.microsoft.com/wiki/contents/articles/sql-server-samples-readme.aspx

Includes:

  • AdventureWorks2008R2 Data File
  • AdventureWorksDWDenali Data File
  • SSAS Multidimensional Model Projects Denali CTP3
  • SSAS Tabular Model Projects Denali CTP3
  • SSAS AMO2Tabular Denali CTP3

Don’t forget to read the sample databases readme file:
http://social.technet.microsoft.com/wiki/contents/articles/sql-server-samples-readme.aspx

Tutorials

Tutorials for SQL Server “Denali”
http://msdn.microsoft.com/en-us/library/hh231699(v=sql.110).aspx

Includes:

  • Multidimensional Modeling (Adventure Works Tutorial)
  • Tabular Modeling (Adventure Works Tutorial)
  • Tutorial for Project Crescent

Blogs / Wikis:

Microsoft SQL Server Code-Named “Denali” CTP3 Release Notes
http://social.technet.microsoft.com/wiki/contents/articles/3711.aspx

SQL Server Team: SQL Server Code Name “Denali” CTP3 and SQL Server 2008 R2 SP1 are HERE!
http://blogs.technet.com/b/dataplatforminsider/archive/2011/07/11/sql-server-code-name-denali-ctp3-is-here.aspx

SSIS Team Blog: Matt Mason – What’s new in SQL Server Denali?
http://blogs.msdn.com/b/mattm/archive/2011/07/12/ssis-what-s-new-in-sql-server-denali.aspx

SQL Server Reporting Services Team Blog: Thierry Dhers – SQL Server codename “Denali” CTP3, including Project “Crescent” is now publically available
http://blogs.msdn.com/b/sqlrsteamblog/archive/2011/07/12/sql-server-codename-quot-denali-quot-ctp3-including-project-quot-crescent-quot-is-now-publically-available.aspx

What’s New in Master Data Services (MDS) in Denali CTP3
http://social.technet.microsoft.com/wiki/contents/articles/3714.aspx

Marco Russo: Installing Analysis Services ssas #Denali CTP3 and PowerPivot Denali CTP3
http://sqlblog.com/blogs/marco_russo/archive/2011/07/13/installing-analysis-services-ssas-denali-ctp3-and-powerpivot-denali-ctp3.aspx

Brent Ozar: CTP3 is Here! Five Things to Know About the Next Version of SQL Server
http://www.brentozar.com/archive/2011/07/five-things-sql-server-denali-ctp3/

Jamie Thompson: SSIS enhancements in Denali CTP3
http://www2.sqlblog.com/blogs/jamie_thomson/archive/2011/07/12/ssis-enhancements-in-denali-ctp3.aspx

MSDN SQL Server (Pre-release) forum threads
http://social.msdn.microsoft.com/Forums/en-US/category/sqlserverprerelease

SQL Server “Deanali” Books Online (BOL)
http://msdn.microsoft.com/en-us/library/ms130214(SQL.110).aspx

Technet Wiki: Project Crescent Overview
http://social.technet.microsoft.com/wiki/contents/articles/project-crescent-overview.aspx

Update Log:
7/26/2011 Added Denali CTP3 Adventure Works Sample Databases Readme link

9/25/2011 Added SQL Server DENALI CTP3 Demo VHD download link and description and Technet Wiki Project Crescent overview.

SSIS Foreach File Enumerator returns more files than expected by appending a wildcard (*) to the file mask

While working on a recent project for a customer that involved importing both Excel 97-2003 and Excel 2007/2010 files, I was a little surprised to discover that the Foreach Loop File Enumerator will return both *.xls and *.xlsx files even if you only specify to return *.xls files.

I tested this behaviour with similar results with other file formats as well. During my testing, I created three files:

  1. File_txt.txt
  2. File_txts.txts
  3. File_txtsy.txtsy

See the screenshot of the three test files created below:

Next, I created an SSIS Package with a Foreach Loop Container with Foreach File Enumerator as the enumerator type and  specified *.txt as the file mask. The file names read by the Foreach Loop Container are being assigned to a user variable called varFileName. Then, inside the Foreach Loop Container I added a simple VB Script Task that returns the value of the user variable varFileName inside a message box.

See the Foreach Loop Containter configurations below:

See the VB script inside the Script Task below: 

You would expect only file_txt.txt to be returned, but as it turns out all three files were returned as can bee seen on the screen captures for the message boxes below:

It looks like the Foreach File Enumerator appends an asterisk (*) at the end of the extension portion of the file mask you specify. This means, that specifying *.txt or *.xls is the same as specifying *.txt* or *.xls*. As an additional test, I executed the <dir> command in D.O.S. to see the results of both masks. The two commands executed are shown below along with the results:

Interestingly, we get the same result in D.O.S. if we specify *.txt or *.txt*. I assume then, that the Foreach Loop file enumeration behaviour may be bound to the D.O.S. output of the Operating System. The current version of D.O.S. on my Windows 7 machine is 6.1.7600. With the introduction of support for more than three letter extensions, this little issue might have been overlooked in D.O.S. for the <dir> command.

The only reference I found regarding this issue with SSIS and the For Each Loop Container was by Douglas Laudenschlager (Blog), technical writer for Microsoft on the SQL Server Integration Services documentation team. The blog post where he mentions this as a gotcha can be found here: http://dougbert.com/blogs/dougbert/archive/2008/06/16/excel-in-integration-services-part-1-of-3-connections-and-components.aspx

Douglas correctly expresses,

 There appears to be no way to specify, “Give me .xls but not .xlsx”.

So, if you require to only limit your control flow execution for files with a specific extension, as in Douglas’ example .xls but not .xlsx, then your alternative will be to assign to a separate variable the extension portion of the file name contained in the varFileName variable of my SSIS package above. Once you capture the file extension in a variable, you can use a precedence constraint to restrict further control fow task execution for files of a specific file extension.

In my case, I declared a variable called varFileExtension and added the following line to the existing Script Task in my example:



I can now use a precedence constraint to check if the value contained in my varFileExtension is equal to “txt” as follows:

 By specifying a precedence constraint that evaluates for the exact file extension I’m looking to work with, I can limit the rest of the execution for only these files. Furthermore, if I wanted to have separate control flow logics for files of type “.xls” and “.xlsx” for example, I can split my control flow using two precedence constraints. In such case I could change the code inside my script to include the last 4 characters instead of just three. My two precedence constraint expressions would look something like this then:

  1. For XLS files -> @[User::varFileExtension]==”.xls” (period included since we are assigning the last 4 characters now)
  2. For XLSX files -> @[User::varFileExtension]==”xlsx”

The SSIS package would look something like this now:

 

%d bloggers like this: