Kirk Haselden

What's new with Kirk?

Knight_Reign — Sun, 11 Feb 2007 17:37:00 GMT

Well, it's been a while since I last blogged... A lot has changed in terms of my role at Microsoft and the technologies on which I work. Integration Services is still a passion for me, and occasionally I try to help out on the forum and the various emails that I get from folks. I'm even starting to think about the second edition of my SSIS book. More on that later... However, since my last blog I've changed groups and am now working in the Microsoft Business Division in Office Business Platform under Bill Baker in the Office Business Applications group. My focus has been on more broad reaching technologies, not just BI per se, but things like EAI, EII, SCM, BPM, CRM and MDM. Roger Wolter and I recently published a whitepaper on MDM and it can be found here: http://msdn2.microsoft.com/en-us/library/bb190163.aspx I'm also moving this blog. I've always struggled with SQL Junkies. There are a number of technical challenges here and there seems to be no momentum or desire to address the problems. The new blog will be focused on SQL Server topics for sure, but will also cover more general topics such as Office BI, performance management, master data management, data quality, and of course, integration, especially Integration Services. My new blog will be on MSDN here: http://blogs.msdn.com/knight_reign/default.aspx Hope to see you there...

Arctic 1000

Knight_Reign — Wed, 12 Jul 2006 05:18:00 GMT

Well it's done, and what a trip!

I'll let you read about it here, but this was a monumental achievement.

http://www.ryanjordan.com/2006_arctic/2006/07/arctic_traverse.html

Finally, the book is available

Knight_Reign — Tue, 11 Jul 2006 18:26:00 GMT

I'm excited to announce that my book will finally be available this month, on July 21st.

You can see more information about the book here:

http://www.samspublishing.com/bookstore/product.asp?isbn=0672327813&rl=1

It's also available from Amazon.com here:

http://www.amazon.com/gp/product/0672327813/ref=sr_11_1/104-2063177-2650333?redirect=true&%5Fencoding=UTF8

I tried to focus on the basics of Integration Services and give insight into the philosophy and inner workings so that readers could gain a better understanding of Integration Services from the perspective of those who developed the product. It's a sort of "inside out" approach. The book is nicely positioned with the other books available on Integration Services in that it deals more directly and closely with the product. My technical editor was Matt David, who some of you may know as the enormously talented and accurate software engineer behind the dataflow task and other portions of the product. I also received multiple insights and help from other members of the SSIS development team.

Here's a short list of some of the highlights:

Many sample packages that show how to configure tasks and components.
Insider stories about how the product was conceived and developed.
Tips, tricks and gotchas
Gradual introduction that begins with simple descriptions in the early chapters and gets progressively more advanced as you work your way to the last chapters
Performance tuning the dataflow task
Writing professional custom tasks
Writing real world, reusable custom dataflow components
Sample custom tasks including:

Stock template task for starting your own tasks including stock task UI.
Expression Evaluator task for evaluating expressions in workflow as well as assigning values to variables
File encryption task

Sample custom dataflow components including:

JPG EXIF reader source
Data profiler transformation
ADO.Net ODBC and SQL Client Destinations

I hope you find the book helpful and that it increases your understanding of Integration Services and would love to hear from you directly any comments, feedback or suggestions you might have about the book.

Thank you,

Arctic 1000

Knight_Reign — Wed, 07 Jun 2006 14:02:00 GMT

In the "totally unrelated to technology" category...

Roman Dial, Ryan Jordan, and Jason Geck plan to hike 600 miles (1000 Kilometers) in three weeks with no resupply, no hunting, and in the most remote location in the US. Quick! Do the math!

Yes, you're right, that's roughly 30 miles a day.

I did 20 miles in the Mt. Rainier wilderness in one day once. The next day, I was barely able to walk. Granted, I wasn't in very good shape and I was carrying more than I should have. (Pre-Ultralight days). These guys are doing it for 21 days straight, without trails.

Ryan Jordan is one of the most authoritative and staunch allies of ultralight principles and regularly reports on 30+ mile hiking days. Roman Dial and Jason Geck are two well established wilderness athletes having participated in some of the most grueling adventure races in the world.

Yet still, what they're doing is absolutely incredible. Take a look at how far they're going.

Super Bowl, World Cup, World Series, Quidditch World Cup move over!

This is sport!

Universe.Earth.Software.Microsoft.Office.OBA.KirkHaselden

Kimball group webcasts

Knight_Reign — Wed, 24 May 2006 22:38:00 GMT

Webcasts on Data Warehousing and Business Intelligence

Presented by the Kimball Group on June 6, June 20, July 11 and July 20

Over the last two decades, the Kimball Group has pioneered a method for successfully defining and implementing an enterprise data warehouse / business intelligence system. The Kimball Group’s The Microsoft Data Warehouse Toolkit, specifically applies this method to the SQL Server 2005 platform.

Please join us at the upcoming webcasts as the experts from the Kimball Group demonstrate, step-by-step, how the SQL Server 2005 platform solves the tough issues faced in implementing Data Warehouses and Business Intelligence applications.

TechNet Webcast:

Microsoft Business Intelligence (BI) Using the Kimball Method (Level 200)

When: June 6, 2006

9:30 a.m. to 10:30 a.m. Pacific Daylight Time

Presenter: Warren Thornthwaite

Real success in business intelligence (BI) is defined by both short and long-term results. For the short term, you can win with top priority, data quality, appealing to broad interest and high value. Long-term success however, requires constant growth and expansion, as well as a solid, well-designed foundation. Join this webcast to learn how to build a strong, scalable Microsoft BI architecture. Learn about the basic Kimball Method, the data warehousing/BI Lifecycle, the three tracks of the development phase, and many more details and issues you would be hard-pressed to anticipate on your own.

http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032297084&Culture=en-US

TechNet Webcast:

Designing a Scalable Data Warehouse / Business Intelligence (DW/BI) System (Level 200)

When: June 20, 2006

9:30 a.m. to 10:30 a.m. Pacific Daylight Time

Presenter: Joy Mundy

When you are building a data warehouse / business intelligence (DW/BI) system, scale is likely to be a major concern. Either you have a large system now, or you hope to grow to have a large system; or you have a small system but a tight budget, and you want to do more with less. What does large scale even mean? Is it determined by data volume, the number of users, complexity, or something else? What are the most important factors to consider? Join this webcast to learn techniques for addressing these and many other DW/BI issues. The session introduces and explains the Kimball Method lifecycle, and shows how to apply it to a scalable DW/BI system.

http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032297070&Culture=en-US

MSDN Architecture Webcast:

Using SQL Server 2005 Integration Services to Populate a Kimball Method Data Warehouse (Level 200)

When: July 11, 2006

11:00 a.m. to 12:00 p.m. Pacific Daylight Time

Presenter: Joy Mundy

How do you combine the tasks and transforms offered by Microsoft SQL Server 2005 Integration Services (SSIS) into a real extraction, transformation, and loading (ETL) system? In this webcast, we present design patterns for building an application that is maintainable, auditable, and scalable to populate your dimensional Kimball Method data warehouse and Microsoft SQL Server 2005 Analysis Services database. Learn best practices for overall system design, for populating dimension and fact tables, and for populating the audit dimension.

http://msevents.microsoft.com/cui/WebCastEventDetails.aspx?EventID=1032297072&EventCategory=4&culture=en-US&CountryCode=US

TechNet Webcast:

Getting Started with Data Mining (Level 200)

When: July 17, 2006

11:30 a.m. to 12:30 p.m. Pacific Daylight Time

Presenter: Warren Thornthwaite

Join this webcast for a comprehensive overview of data mining from a database development perspective. We begin with a discussion of the business value and uses of data mining, such as prediction and forecasting. Learn how to detect anomalies, and how to recognize scenarios for which Microsoft data mining technology is best suited. Using a typical business-driven approach to data mining, we show how to identify data mining opportunities, and cover the practical elements needed to make it work well, such as data preparation, model building, and validation. We then examine the output, consider different implementation methods, and conclude with recommendations on how to maintain your data mining solution.

http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032297086&Culture=en-US

New SSIS GotDotNet website...

Knight_Reign — Tue, 23 May 2006 14:22:00 GMT

There's a new GotDotNet site dedicated to Integration Services and it already has some cool content.

Check it out!

http://www.gotdotnet.com/codegallery/codegallery.aspx?id=042f5bda-78c6-4c94-a68e-c1917b036db3

Universe.Earth.Software.Microsoft.Office.OBA.KirkHaselden

Writing contents of a variable to a file

Knight_Reign — Mon, 22 May 2006 17:04:00 GMT

In this blog I showed how to read the contents of a file into a variable.

Recently a customer asked how to do the opposite. He needs to query an SQL table with an XML result and write the results to a file.

Here's how:

Drop an Execute SQL Task onto the designer.
Double click to open the task.
Set result set to XML
Set connection type to whatever type you wish to use
Create or use an existing connection, I'm using the AdventureWorks sample database.
Set SQLSourceType correctly. In this case, I used Direct Input
Set the SQLStatement. The following is an example:
1. SELECT CurrencyCode, Name, ModifiedDate
  FROM Sales.Currency
  FOR XML AUTO
Select the Result Set node on the left of the TaskUI
1. Click "Add" and set the Result Name to zero (0)
2. Set the Variable name to the one you want to contain the XML

That's it for the Execute SQL Task.

Now, to get the resulting XML into a file, do the following:

Drop a Script Task onto the designer.
Open the Task UI by double clicking
In the Script node, add the name of the variable that holds the XML you set above in the Execute SQL Task to the ReadOnlyVariables. I used RESULTSXML
Write a snippet of code to write out the contents into a file. I've hardcoded everything to keep it simple. Don't do that in production code.
Public Sub Main()

Dim errorInfo As String = ""

Dim Contents As String = ""

Contents = Dts.Variables("RESULTSXML").Value.ToString()

WriteVariableContents("C:\\TEMP\\resultsxml.xml", Contents)

End Sub

Public Sub WriteVariableContents(ByVal filePath As String, ByVal contents As String)

Dim objWriter As IO.StreamWriter

Try

objWriter = New IO.StreamWriter(filePath)

objWriter.Write(contents)

objWriter.Close()

Catch Ex As Exception

MsgBox(Ex.Message)

End Try

End Sub

That's pretty much it in a nutshell.

HTH

Universe.Earth.Software.Microsoft.Office.OBA.KirkHaselden

BI and EIM at Microsoft

Knight_Reign — Thu, 18 May 2006 12:43:00 GMT

I was excited to read Bill Gates' vision around BI and EIM for the next ten years as articulated in his latest executive mail entry.

It represents a comprehensive, orchestrated and unified strategy for building infrastructure and tools enterprises need in the information age.

I found his comment on the data store silo problem particularly compelling:

"One of the biggest barriers to information access in the enterprise is the fact that data is often stored in so many different repositories. This leads to painfully inefficient processes that force information workers to leave one application, logon to another, find a single piece of data and write it on a piece of paper, and then return to their original application, just to complete a simple task like sending an email to a customer. This is a significant drag on productivity. Microsoft’s goal is to deliver enterprise information access solutions that present information workers with a single, unified way to get at the information they need no matter where it resides without leaving the application they are currently working in so they can make smart decisions and take action with greater speed."

http://www.microsoft.com/mscorp/execmail/

Thanks

Universe.Earth.Software.Microsoft.Office.OBA.KirkHaselden

What's happened to this blog...

Knight_Reign — Mon, 15 May 2006 03:52:00 GMT

Well, it's been a while since I've blogged. And for good reason. The last six months, and in particular, the last three have been extremely busy and difficult. For the most part, if I was doing any writing, it was on my forthcoming book. Yes, it's done. Now it's simply a matter of letting the publisher machinery churn. I suppose, like anyone who has ever written a book, especially a technical reference, I've had some real disappointments, some trying times and some highs. I won't bore you with them all, but just to say that writing a technical reference is extremely difficult. The writing isn't so hard, in fact, it was pretty easy. I just had to poor out my brain into the chapters. The difficult part is the time and the things you have to give up to make it happen. In the time I took to write the book I missed essentially 2.5 child years out of my children's lives. That's the tough part.

On the bright side, I truly believe that I've been able to provide some real value to readers. Time and time again I've seen questions about the product that are answered by the book. There are roughly 40 sample packages, 7 custom tasks in various degrees of completeness including the expression task that let's you modify the value of a variable with an expression and an encryption task with a stock task UI that let's you encrypt/decrypt text files, 4 custom components including a JPEG metadata extractor source adapter, data profile transform and two ADO.NET destination adapters. There are two configuration utilities. One for editing configurations in a package directly and one for bulk editing a configuration in multiple packages simultaneously. All have the source code available. There are some especially helpful chapters on configurations, tuning the dataflow task, SSIS security and writing custom components. Although the original concept and table of contents for the book was much more expansive, I believe that the final results address the lionshare of topics that people really want to know. I hope you enjoy the book and that it helps you get the most out of Integration Services. You can pre-order the book here:

http://www.amazon.com/gp/product/0672327813/ref=sr_11_1/002-5741340-2320813?%5Fencoding=UTF8

Another factor in my recent recording reticence is a role change. I've taken a position in the Office Business Applications group. This is a great opportunity to help continue the emphasis on Business Intelligence at Microsoft. While my primary focus has been Integration Services for the last 5 years, this new role will allow me to spread my wings a bit and impact a wider range of products. My current project is not announced and probably won't be for some time, however I will be focusing more broadly on the full Microsoft BI Suite of tools and future entries you find here will likely reflect that. I still have a number of SSIS blogs that I want to get around to, but I'll also be tackling other subjects such as BSM, BPM, UDM, AS, MDM, EIM and other such TLAs.

Well, that's enough blathering from me for one night.

Thanks,

Universe.Earth.Software.Microsoft.Office.OBA.KirkHaselden

New SSIS blogger

Knight_Reign — Wed, 18 Jan 2006 18:23:00 GMT

Daniel Read has a nice blog and is starting to blog a bit about Integration Services.

Catch the main blog here:

http://www.developerdotstar.com/community/

And the SSIS related blogs here:

http://www.developerdotstar.com/community/taxonomy/term/58

Dan seems to be digging down into the bowls of IS and finding some interesting things.

Take a look.

Universe.Earth.Software.Microsoft.SQLServer.IS.KirkHaselden

My book has a cover now...

Knight_Reign — Mon, 16 Jan 2006 19:44:00 GMT

The book actually has a cover now.

http://www.amazon.com/gp/product/0672327813/ref=sr_11_1/102-4542354-5215343?%5Fencoding=UTF8

Universe.Earth.Software.Microsoft.SQLServer.IS.KirkHaselden

Connecting to a Remote SSIS Server

Knight_Reign — Thu, 05 Jan 2006 17:48:00 GMT

So how do you connect to a remote SSIS Server? I will show examples based on SSIS 2005 SP1 bits on Windows XP SP2, Windows 2003 Server SP1 and Windows 2000 SP4. As an SSIS client for this document I will use SQLWB but the same apply for other clients.

In order to connect to a remote SSIS Server using SQLWB one need to type the name of the server and click the connect button. For this release the Browse window doesn’t support browsing for remote SSIS servers. Also, the values on the advanced page of connection dialog will have no affect on the connection.

Access Is Denied Error

If you are getting the dreaded “Connect to SSIS Service on machine <MachineName> failed: Access is denied” error when you connect, check the security settings on the server machine as follows:

Windows 2003 Server (SP1) and Windows XP (SP2)

- If the user running under non-admin account it needs to be added to Distributed COM Users group

- Run %windir%\system32\Com\comexp.msc to launch Component Services

- Expend Component Services\Computers\My Computer\DCOM Config

- Right click on MsDtsServer node and choose properties

- In MsDtsServer Properties dialog go to Security page

- Configure your settings as described bellow

- Restart SSIS Service

In the Security page we are interested in “Launch and Activation Permissions” section. Click Edit button to see “Launch Permissions” dialog.

“Launch Permissions” dialog allows you to configure SSIS server access per user/group. In the bottom of the dialog you can select:

- Local / Remote Launch permissions if you allow to a user/group to start service locally or remotely.

- Local / Remote Activation permissions if you allow to a user/group to connect to SSIS server locally or remotely.

Here are few examples of SSIS server configurations:

Enable Remote Access

By default low privileged users can only connect to SSIS Server on the local machine when the service already started. It is shown by the fact that only Local Activation checked for Machine\Users group. To grant the user permission connect to the running server remotely – check remote activation.

Control Who Can Start the Service

Normally SSIS Server can be started by a member of Administrators group either locally or remotely. It can be started either using conventional ways or by calling a method on an enabled SSIS service.

In some situations you may want to share these rights with low privileged users or revoke these rights from an administrator. It can be done by checking/un-checking Local/Remote Launch check boxes.

Windows 2000 SP4

- Run dcomcnfg.exe

- On Applications page of “Destributed COM Configuration Properties” dialog select MSDTSServer and click Properties button

- Select Security page

- Configure your settings as described bellow

- Restart SSIS Service

Security settings on Win2K are a little deferent then on Win2K3 or WinXP. There are 2 separate dialogs to configure Access Permissions and Launch Permissions. Unfortunately you can’t distinguish between remote and local access. By default on this platform any members of Machine/Users group have remote/local access and launch permissions.

Best Practice to Manage Remote Users for a SSIS Server

We recommend creating one or several windows groups (Distributed SSIS Users, etc) that you can use to control remote access using steps described above. You can also use the same groups to control access to packages stored in SSIS Store.

Unfortunately groups can’t be used when you need to add a user to Distributed COM Users group. Users should be added to the group one by one.

Local User Access to a Remote SSIS Server

If you are running under a local user account on your client machine, you can still access remote SSIS server under the following conditions:

- The machine running remote SSIS server should have an account with exactly same name and password as the one you are running under.

- The account should be configured to access SSIS server as described above.

Delegation Problems

SSIS 2005 doesn’t support scenario when a client, SSIS Server and underlying SQL server running on 3 different machines.

This scenario can emerge when you change default MsDtsSrvr.ini.xml to point to a remote SQL server (for example when you implement a shared SQL Store or want to separate SQL Server and SSIS Server for security reasons).

This post was provided by Nick Berezansky from the Integration Services team.

Thanks,

Universe.Earth.Software.Microsoft.SQLServer.IS.NickBerezansky

Universe.Earth.Software.Microsoft.SQLServer.IS.KirkHaselden

December BOL Update

Knight_Reign — Thu, 05 Jan 2006 03:00:00 GMT

http://www.microsoft.com/downloads/details.aspx?FamilyID=be6a2c5d-00df-4220-b133-29c1e0b6585f&DisplayLang=en

Universe.Earth.Software.Microsoft.SQLServer.IS.KirkHaselden

Getting in touch with the team

Knight_Reign — Wed, 04 Jan 2006 16:53:00 GMT

Folks are using Integration Services and seem to be having a lot of fun with it while building great solutions. That's great. It's a sign that we've hit the sweet spot. Occasionally, folks have problems, but the list of problems seems to be relatively finite, static and at this point well known. The knowledge for how to work around these snags is also fairly well known in the Integration Services community. Which brings me to the point of this post.

While the SSIS team loves to have customer touch. (We lead the entire SQL Server organization statistically) There are limits to the amount of time our little team can spend directly interacting with customers. Lately, we've been getting a lot of requests for conference calls and personal requests to "help write a package" or "solve a package issue" etc.

If it is a last resort, and you have a high priority problem that you really cannot find the answer to any other way, occasionally we're able to help out. Of course, we always want to hear about any bugs you find and/or ways to improve the product. But, directly contacting team members should not be your first avenue for finding solutions. Let me list some of the resources you can use to get exceptional support.

The forum is the place for feedback and support. Most of the Integration Services team visits this site at least once a week. I know some of us visit at least once a day.

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=80&SiteID=1

The following link is to the product feedback page where you can make suggestions for how to improve the product. Suggestions entered here go directly into our bug database.

http://lab.msdn.microsoft.com/productfeedback/default.aspx

This is the Integration Services team presence MSDN site where you'll find a wealth of resources updated regularly.

http://msdn.microsoft.com/SQL/bi/integration/default.aspx

If you're tempted to send mail directly to one of the SSIS team, please make sure you've tried these resources first.

Chances are, you'll get a better reception and response going through one of these channels where there are a lot more people who stand ready to assist.

Thanks,
K

Self Modifying Packages in SSIS?

Knight_Reign — Sat, 31 Dec 2005 05:46:00 GMT

Yeah, thought that might get your attention. :)

First, packages cannot modify themselves during execution. There is no package pointer passed to the tasks any longer, so you can't traverse the package object model with the script task any longer. That is, you can't traverse the package object model for package in which the script task resides. You CAN however open and modify other packages, including those that the parent package is about to execute with the Execute Package task. This is the same model as self modifying packages in DTS, except it's safer because you're not attempting to change the package as it is running.

Here's the script from a chapter of my book that shows you how to modify a Transfer Objects Task to move some tables. There is no error handling code for clarity, bla bla bla. The usual caveats apply, check for errors, handle exceptions.

The Script

Imports System.Collections.Specialized

Public Sub Main()

Dim application As Microsoft.SqlServer.Dts.Runtime.Application = New Application()

Dim packagename As Object = Dts.Connections("Tables").AcquireConnection(Nothing)

Dim package As Microsoft.SqlServer.Dts.Runtime.Package = application.LoadPackage(packagename.ToString(), Nothing)

Dim th As TaskHost

th = package.Executables("TablesToMove")

Dim sc As StringCollection = New StringCollection()

sc.Add(Dts.Variables("Tables1").Value.ToString())

sc.Add(Dts.Variables("Tables2").Value.ToString())

th.Properties("TablesList").SetValue(th, sc)

application.SaveToXml(packagename, package, Nothing)

Dts.TaskResult = Dts.Results.Success

End Sub

This is some quick and dirty code that uses the package object model to open the child package and modify it.

Table Names

In the parent package there are a couple of variables I use, Tables1 and Tables2, to change the table I want to move. Now, you can change this how you want. Stick them all in a comma delimited string if you want and parse them out in script or stick them in a variable one-by-one with a ForEach Nodelist Enumerator. It doesn't really matter how you get the table names into the script.

The Package Connection

The script uses the same Connection Manager as the Execute Package task to find the name of the package to open and modify.

Modifying the Package

Now the property type for the Transfer Objects Task for the TableList is a StringCollection. So, I create a StringCollection in script and populate it. Then I open the child package, the one that will be moving tables, and set the property on the Transfer Objects Task and save the package again.

Here's the parent package with the script task and the Execute Package task.

This is the script task UI. Notice the variables that I'm referencing.

Finally, this is the child package, pretty simple. Has just the Transfer Objects task.

Notice that the tables selected to be moved in the Transfer Objects Table are the same as those specified in the variables on the parent package.

Easy right!

Universe.Earth.Software.Microsoft.SQLServer.IS.KirkHaselden