The SQL Server Side of Me (en-us)

How to use a list of values in Excel as filter in a query

Luca Zavarella — Tue, 12 Jun 2012 13:27:25 GMT

It often happens that a customer provides us with a list of items for which to extract certain information. Imagine, for example, that our clients wish to have the header information of the sales orders only for certain orders. Most likely he will give us a list of items in a column in Excel, or, less probably, a simple text file with the identification code:

As long as the given values are at best a dozen, it costs us nothing to copy and paste those values in our SSMS and place them in a WHERE clause, using the IN operator, making sure to include the quotes in the case of alphanumeric elements (the database sample is AdventureWorks2008R2):

SELECT *
FROM Sales.SalesOrderHeader AS SOH
WHERE SOH.SalesOrderNumber IN (
		'SO43667'
		,'SO43709'
		,'SO43726'
		,'SO43746'
		,'SO43782'
		,'SO43796')

Clearly, the need to add commas and quotes becomes an hassle when dealing with hundreds of items (which of course has happened to us!). It’d be comfortable to do a simple copy and paste, leaving the items as they are pasted, and make sure the query works fine.

We can have this commodity via a User Defined Function, that returns items in a table. Simply we’ll provide the function with an input string parameter containing the pasted items. I give you directly the T-SQL code, where comments are there to clarify what was written:

CREATE FUNCTION [dbo].[SplitCRLFList] (@List VARCHAR(MAX))
RETURNS @ParsedList TABLE (
	--<	Set the item length as your needs	 
	Item VARCHAR(255)
	)
AS
BEGIN
	DECLARE
		--<	Set the item length as your needs
		@Item VARCHAR(255)
		,@Pos BIGINT

	--< Trim TABs due to indentations
	SET @List = REPLACE(@List, CHAR(9), '')
	--< Trim leading and trailing spaces, then add a CR\LF at the end of the list
	SET @List = LTRIM(RTRIM(@List)) + CHAR(13) + CHAR(10)
	--< Set the position at the first CR/LF in the list
	SET @Pos = CHARINDEX(CHAR(13) + CHAR(10), @List, 1)

	--< If exist other chars other than CR/LFs in the list then...	
	IF REPLACE(@List, CHAR(13) + CHAR(10), '') <> ''
	BEGIN
		--< Loop while CR/LFs are over (not found = CHARINDEX returns 0)
		WHILE @Pos > 0
		BEGIN
			--< Get the heading list chars from the first char to the first CR/LF and trim spaces
			SET @Item = LTRIM(RTRIM(LEFT(@List, @Pos - 1)))

			--< If the so calulated item is not empty...
			IF @Item <> ''
			BEGIN
				--< ...insert it in the @ParsedList temporary table
				INSERT INTO @ParsedList (Item)
				VALUES (@Item)
					--(CAST(@Item AS int))	--< Use the appropriate conversion if needed
			END

			--< Remove the first item from the list...
			SET @List = RIGHT(@List, LEN(@List) - @Pos - 1)
			--< ...and set the position to the next CR/LF
			SET @Pos = CHARINDEX(CHAR(13) + CHAR(10), @List, 1)
				--< Repeat this block while the upon loop condition is verified
		END
	END

	RETURN
END

At this point, having created the UDF, our query is transformed trivially in:

SELECT *
FROM Sales.SalesOrderHeader AS SOH
WHERE SOH.SalesOrderNumber IN (
		SELECT Item
		FROM SplitCRLFList('SO43667
SO43709
SO43726
SO43746
SO43782
SO43796') AS SCL)

Convenient, isn’t it? You can find the script DBA_SplitCRLFList.sql here.

Bye!!

How to update all the SSIS packages’ Connection Managers in a BIDS project with PowerShell

Luca Zavarella — Tue, 05 Jun 2012 10:16:00 GMT

During the development of a BI solution, we all know that 80% of the time is spent during the ETL (Extract, Transform, Load) phase. If you use the BI Stack Tool provided by Microsoft SQL Server, this step is accomplished by the development of n Integration Services (SSIS) packages. In general, the number of packages made in the ETL phase for a non-trivial solution of BI is quite significant.

An SSIS package, therefore, extracts data from a source, it "hammers" :) the data and then transfers it to a specific destination. Very often it happens that the connection to the source data is the same for all packages. Using Integration Services, this results in having the same Connection Manager (perhaps with the same name) for all packages:

The source data of my BI solution comes from an Helper database (HLP), then, for each package tha import this data, I have the HLP Connection Manager (the use of a Shared Data Source is not recommended, because the Connection String is wired and therefore you have to open the SSIS project and use the proper wizard change it...). In order to change the HLP Connection String at runtime, we could use the Package Configuration, or we could run our packages with DTLoggedExec by Davide Mauri (a must-have if you are developing with SQL Server 2005/2008). But my need was to change all the HLP connections in all packages within the SSIS Visual Studio project, because I had to version them through Team Foundation Server (TFS).

A good scribe with a lot of patience should have changed by hand all the connections by double-clicking the HLP Connection Manager of each package, and then changing the referenced server/database:

Not being endowed with such virtues :) I took just a little of time to write a small script in PowerShell, using the fact that a SSIS package (a .dtsx file) is nothing but an xml file, and therefore can be changed quite easily. I'm not a guru of PowerShell, but I managed more or less to put together the following lines of code:

$LeftDelimiterString = "Initial Catalog="
$RightDelimiterString = ";Provider="
$ToBeReplacedString = "AstarteToBeReplaced"
$ReplacingString = "AstarteReplacing"
$MainFolder = "C:\MySSISPackagesFolder"

$files = get-childitem "$MainFolder" *.dtsx `
      | Where-Object {!($_.PSIsContainer)}

foreach ($file in $files)
{
      (Get-Content $file.FullName) `
            | % {$_ -replace "($LeftDelimiterString)($ToBeReplacedString)($RightDelimiterString)", "`$1$ReplacingString`$3"} `
| Set-Content $file.FullName;
}

The script above just opens any SSIS package (.dtsx) in the supplied folder, then for each of them goes in search of the following text:

Initial Catalog=AstarteToBeReplaced;Provider=

and it replaces the text found with this:

Initial Catalog=AstarteReplacing;Provider=

I don’t enter into the details of each cmdlet used. I leave the reader to search for these details.

Alternatively, you can use a specific object model exposed in some .NET assemblies provided by Integration Services, or you can use the Pacman utility:

Enjoy! :)

P.S. Using TFS as versioning system, before running the script I checked out the packages and, after the script executed succesfully, I checked in them.

BIDS Helper 1.6 Beta released!

Luca Zavarella — Mon, 02 Apr 2012 12:42:00 GMT

A new version of this fantastic tool, used by Microsoft BI Stack developers, is out. Now it’s compatible with SQL Server 2012.

In this post, Teo Lachev gives us more details.

This is the project’s Home.

Enjoy!

New release of "OLAP PivotTable Extensions"

Luca Zavarella — Mon, 06 Feb 2012 11:20:16 GMT

For those who are not familiar with this add-in, the OLAP PivotTable Extensions add features of interest to Excel 2007 or 2010 PivotTables pointing to an OLAP cube in Analysis Services. One of these features I like very much, is to know the MDX query code associated with the pivot used at that time in Excel:

You can find all the details here:

http://olappivottableextend.codeplex.com/

It was recently released a new version of the add-in (version 0.7.4), which does not introduce any new features, but fixes a significant bug:

Release 0.7.4 now properly handles languages but introduces no new features. International users who run a different Windows language than their Excel UI language may be receiving an error message when they double click a cell and perform drillthrough which reads: "XML for Analysis parser: The LocaleIdentifier property is not overwritable and cannot be assigned a new value". This error was caused by OLAP PivotTable Extensions in some situations, but release 0.7.4 fixes this problem.

Enjoy!

How to import in BIDS more than one SSIS package in one shot!

Luca Zavarella — Thu, 02 Feb 2012 20:27:14 GMT

Have you ever wanted to add more than one Integration Services existing package (e.g. 20 packages) in a SSIS project? Well, you may suppose that an Open Dialog supports multiple files selection to import more than one file at a time ...

BIDS Open Dialog doesn’t allow this, you can just select a single file! Hence the loss of valuable time spent to import the packages one at a time.

Few days ago I learned a trick that solves the problem, thanks to this post by Matt Masson.

Just copy all the packages to import from Windows Explorer (Ctrl + C):

Then just right click on the SSIS Packages folder of the Integration Services project and make a simple Past (CTRL + V):

So “auto-magically” you’ll have all those packages imported in your Integration Services project!!

What can I say... this feature was well hidden!

Let’s keep informed with “Data Explorer”

Luca Zavarella — Sat, 10 Dec 2011 12:39:20 GMT

At Pass Summit 2011 a new project was announced. It’s a Microsoft SQL Azure Lab and its codename is Microsoft “Data Explorer”. According to the official blog (http://blogs.msdn.com/b/dataexplorer/), this new tool provides an innovative way to acquire new knowledge from the data that interest you. In a nutshell, Data Explorer allows you to combine data from multiple sources, to publish and share the result. In addition, you can generate data streams in the RESTful open format (Open Data Protocol), and they can then be used by other applications. Nonetheless we can still use Excel or PowerPivot to analyze the results.

Sources can be varied: Excel spreadsheets, text files, databases, Windows Azure Marketplace, etc.. For those who are not familiar with this resource, I strongly suggest you to keep an eye on the data services available to the Marketplace:

https://datamarket.azure.com/browse/Data

To tell the truth, as I read the above blog post, I was tempted to think of the Data Explorer as a "SSIS on Azure" addressed to the Power User. In fact, reading the response from Tim Mallalieu (Group Program Manager of Data Explorer) to the comment made to his post, I had a positive response to my first impression:

“…we originally thinking of ourselves as Self-Service ETL. As we talked to more folks and started partnering with other teams we realized that would be an area that we can add value but that there were more opportunities emerging.”

The typical operations of the ETL phase ( processing and organization of data in different formats) can be obtained thanks to Data Explorer Mashup. This is an image of the tool:

The flexibility in the manipulation of information is given by Data Explorer Formula Language. This is a formula-based Excel-style specific language:

Anyone wishing to know more can check the project page in addition to aforementioned blog:

http://www.microsoft.com/en-us/sqlazurelabs/labs/dataexplorer.aspx

In light of this new project, there is no doubt about the intention of Microsoft to get closer and closer to the Power User, providing him flexible and very easy to use tools for data analysis. The prime example of this is PowerPivot.

The question that remains is always the same: having in a company more Power User will implicitly mean having different data models representing the same reality. But this would inevitably lead to anarchical data management... What do you think about that?

A new version of SQL Treeo released

Luca Zavarella — Wed, 09 Nov 2011 08:34:40 GMT

A new SQL Treeo update is available at http://www.sqltreeo.com.

Lot of bugs fixed so now it seems to be a stable add-on for SSMS.

The full change log is available here:

http://www.sqltreeo.com/wp/new-version-of-sql-treeo-ssms-productivity-add-in-was-released/

So, update your SSMS clients :)

Running your SSMS client as a domain user even if you’re not in a domain

Luca Zavarella — Mon, 29 Aug 2011 20:46:58 GMT

I wonder if it is possible to use the SQL Server Management Studio (SSMS) client on my machine with a specific domain user when my machine wasn’t in that domain. In fact, many developers use some SSMS add-ons installed on their machine (with appropriate licenses), which greatly simplify their daily work.

For example, I’m a Red Gate SQL Prompt addicted , so it’d be convenient for me to work on customers’ SQL Server instances with this tool. After reading Davide Mauri’s post, a friend and collegue of mine, I created a batch file in order to specify a domain and a user for SSMS:

@echo off
echo ***************************************
echo *** Run SSMS 2008 R2 as domain user ***
echo ***************************************
echo.

set /P user="Type the domain\username: "

C:\Windows\System32\runas.exe /netonly /user:%user% "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"

Then, you can create on your desktop a shortcut to the file batch previously developed and you can also change the shortcut icon, using the same SSMS icon (get it from the Ssms.exe file). Now if you double-click on the shortcut, you can set domain and user for the SSMS client on-the-fly:

So enjoy using your “personal” SSMS client on your preferred domain

Custom Folders in SSMS Object Explorer? Yes, we can!

Luca Zavarella — Sun, 28 Aug 2011 15:09:14 GMT

When you have a huge objects’ number in SSMS Object Explorer, you often get lost in finding items. So it’d be useful to catalog those objects in folders, in order to follow an application’s logical layer subdivision, for example.

There is a fantastic add-in for SSMS that helps us to do that:

http://www.sqltreeo.com

The developer of this add-in has written a related post in his blog:

http://www.sqltreeo.com/wp/dowload-free-ssms-add-in-to-create-own-folder-for-database-objects/

So another useful tool to add to our SQL Server toolbox

Online Introduction to Relational Databases (and not only) with Stanford University!

Luca Zavarella — Sat, 20 Aug 2011 13:05:00 GMT

How many of you know exactly the definition of "relational database"? What exactly the adjective "relational" refers to? Many of you allow themselves to be deceived, thinking this adjective is related to foreign key constraints between tables. Instead this adjective lurks in a world based on set theory, relational algebra and the concept of relationship intended as a table.

Well, for those who want to deep the fundamentals of relational model, relational algebra, XML, OLAP and emerging "NoSQL" systems, Stanford University School of Engineering offers a public and free online introductory course to databases.

This is the related web page:

http://www.db-class.com/

The course will last 2 months, after which there will be a final exam. Passing the final exam will entitle the participants to receive a statement of accomplishment.

A syllabus and more information is available here.

Happy eLearning to you!