June 2012 Blog Posts

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

12 June 2012

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? SmileYou can find the script DBA_SplitCRLFList.sql here.

Bye!!

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

05 June 2012

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.