Partied with Microsoft today

I attended the Microsoft technology roadshow today. After being paged out at 4:30 due to some I/O nonsense related to statistics updates which I stayed up until 6:30 for trying to prevent happening again, I made my way to downtown Phoenix.

Arrived just in time to see the new nastyness that is windows 7/server 2008 R2 licensing.

Sharepoint 2010

Looks nice. Ajaxified. Working in Firefox with a silverlight plugin installed I would assume. Although they do also claim it works in smartphones so maybe it is just ajax. Sharepoint = pricey.

BI/Insight

This is the real magic. Excel is nothing to laugh at. Excel 2007 has some nice features when it comes to connecting to alternate data sources such as analysis services. I have some plans to work with this tech in the future. 2010 takes it further and adds a dash of manageability which actually making things look good. When I say good, I mean Apple good…almost. They even have little carousels for browsing reports.

They have improved manageability of user created content by leaps and bounds. Users can publish Excel docs. Other users can create reports based on those docs. If usage of the base Excel doc starts to accelerate, you will know it and can act accordingly by taking over the management of the ‘application’. That’s what I’m talking about Napoleon.

A hotshot consultant showed us how he did a break out/data driven report in reporting services. I was hoping for something a little more impressive from this ‘prodigy’, as his boss referred to him.

I see ‘self-service’ BI as a bit of a pipe dream. MSFT is putting the framework in place so that if you users manage to figure out this mess of tech, you’ll at least know what is bombing on your servers.

Also, Gemini has been officially dubbed PowerPivot. Reminds me a bit of Cognos Power Play.

Seeing all the connections for all SSIS packages on your server

So you have a mess of packages on your server and you’d like to know what they connect to without going into each package? Yeah, me too.

Create a visual studio c# console application with Visual Studio (express will probably work).

Paste this code in:

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime;
using System.Text.RegularExpressions;
using System.IO;

namespace SSISPackageInspector
{


public static class CsvWriter
{
private static StreamWriter _writer = new StreamWriter(@"c:\temp\ssis_list.csv");

public static StreamWriter Writer
{
get { return _writer; }
}
}




class Program
{

static void Main(string[] args)
{
string outfile = null;
string folder = null;
string server = null;


if (args.Length == 0)
{
System.Console.WriteLine("Please enter a numeric argument.");
Console.Read();
return;
}
else
{
int i = 0;
foreach(string s in args)
{
if (args[i].Substring(0,2)==@"\o")
{
outfile = args[i].Substring(2);
}

if (args[i].Substring(0, 2) == @"\f")
{
folder = args[i].Substring(2);
}

if (args[i].Substring(0, 2) == @"\s")
{
server = args[i].Substring(2);
}

i++;
}

}


if (outfile != null)
{



}
//Look up All package names for a server
PrintDirectoryContents(folder,server);

if (outfile != null)
{
// CsvWriter.Close();
}
Console.Read();
}


static void PrintDirectoryContents(string path, string server)
{


string sqlFolder;
string sqlServer;

Application ssisApplication;
PackageInfos sqlPackages;

sqlServer = server;

ssisApplication = new Application();


sqlFolder = path;
sqlPackages = ssisApplication.GetDtsServerPackageInfos(sqlFolder, sqlServer);
if (sqlPackages.Count > 0)
{
Console.WriteLine("Packages stored in " + path);
foreach (PackageInfo sqlPackage in sqlPackages)
{
Console.WriteLine(sqlPackage.Name);
Console.WriteLine(" " + sqlPackage.Flags);

if (sqlPackage.Flags.ToString() == "Package")
{
GetConnections(@"\" + sqlFolder,sqlPackage.Name,server);

}

if (sqlPackage.Flags.ToString() == "Folder")
{
PrintDirectoryContents(path + @"\" + sqlPackage.Name,server);
}

}
}
}


static void GetConnections(string path,string pkgname,string server)
{

// Create a package, and retrieve its connections.
Application mApplication = new Application();

//Package pkg = mApplication.LoadPackage(@"C:\temp\DBA_Repository_Load.dtsx", null);
Package pkg = mApplication.LoadFromDtsServer(path + @"\" + pkgname , server, null);
Connections conns = pkg.Connections;

//Create the Enumerator for each connection
ConnectionEnumerator myEnumerator = conns.GetEnumerator();
Console.WriteLine("------" + pkg.Name.ToString() + "----------");

while ((myEnumerator.MoveNext()) && (myEnumerator.Current != null))
{
string myLine = null;
myLine += server + "," + path + "," + pkg.Name.ToString() + "," + myEnumerator.Current.Name;

Regex r = new Regex("([^=;]*)=([^;]*)");

MatchCollection mc = r.Matches(myEnumerator.Current.ConnectionString);

Console.WriteLine("Conn Name:" + myEnumerator.Current.Name.ToString());

string s = myEnumerator.Current.ConnectionString.ToString();
if (s.Length > 0)
{
if (s.Substring(0, 2) == @"\\")
{
Console.WriteLine(" " + myEnumerator.Current.ConnectionString);
myLine += "," + myEnumerator.Current.ConnectionString;

}
}

foreach (Match m in mc)
{
if (m.Groups[1].ToString() == "Data Source" || m.Groups[1].ToString() == "Initial Catalog")
{
if (m.Groups[1].ToString() == "Data Source")
{
Console.WriteLine(" Server:" + m.Groups[2]);
myLine += "," + m.Groups[2];
}
}

if (m.Groups[1].ToString() == "Initial Catalog")
{
Console.WriteLine(" Database:" + m.Groups[2]);
myLine += "," + m.Groups[2];
}
}

CsvWriter.Writer.WriteLine(myLine);
//Console.WriteLine(myLine);


}

Console.WriteLine("------End Package-------");


}
}

}

Go to project > properties > debug > start options

Enter these parameters

\sMyServer \fmsdb\mydirectory \oc:\temp\ssis_list.csv

The first is your SSIS server you’d like to connect to.

The second is your ssis directory to process – you can just use msdb and do the whole shebang.

The final is your output file. Duh.

You can now press play in VS and see the console window spin by. Look for your file in the directory you specified. You can do all kinds of spiffy stuff to packages using .net.

More information on running and managing packages programmatically is available from the good folks at MSFT.

DTUtil

Constantly using ‘Save a Copy As..’ in BIDS to save a SSIS package to the server annoys me to no end. Does MSFT actually use this crap?

DTUTIL.exe is a tool that comes in your 90\DTS\binn folder. Check this out:

C:\Program Files\Microsoft SQL Server\90\DTS\Binn>

dtutil /En SQL;subfolder\packagename;5 /Fi “C:\
Documents and Settings\me\My Documents\Visual Studio 2005\Projects\ProjectName\blah\
PackageName.dtsx” /DestServer devserver

note: For the first option (/En), the subfolder does not need to include the MSDB folder which you will see when browsing SSIS.

Paste that into your cmd window and you’ll be saving with two keystrokes. Your options may change, but with the help of the online docs, you’ll be happier.

If dtutil isn’t recognized you aren’t in the proper directory. You can also add the binn directory to your PATH variable. Find the PATH variable and add “;C:\Program Files\Microsoft SQL Server\90\DTS\Binn” to the end of the string. Note the semicolon at the start.