Kirk Haselden

What's new with Kirk?

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

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

K

Finally, the book is available

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,

K 

 

 

 

 

 

Arctic 1000

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

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...

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

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:

1. Drop an Execute SQL Task onto the designer.
2. Double click to open the task.
3. Set result set to XML
4. Set connection type to whatever type you wish to use
5. Create or use an existing connection, I'm using the AdventureWorks sample database.
6. Set SQLSourceType correctly. In this case, I used Direct Input
7. Set the SQLStatement. The following is an example:
   1. SELECT     CurrencyCode, Name, ModifiedDate
      FROM          Sales.Currency
      FOR XML AUTO
8. 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:

1. Drop a Script Task onto the designer.
2. Open the Task UI by double clicking
3. 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
4. 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.

5. 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

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...

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

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...

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

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 Adm