Ashvini Sharma

DTS to SSIS functionality differences and mappings

ashvinis — Tue, 05 Jul 2005 18:24:00 GMT

This came up at a webcast a couple of weeks back and I couldn't find this anywhere. Jim, our King of Migration/Upgrades/Pipeline components, is about to start blogging any minute now, and will have a lot more information on these. In the meantime, I had to get a high level version done for WWPC, so here's the detailed version as well.

There's always harm in making comparisons between DTS and SSIS for folks that don't understand the magnitude of changes because this might imply a trivial renaming of features and tweaks to others... well, in the interest of helping those more familiar with the products make the mapping, here's a take on how folks did things in DTS and how to do the same in SSIS:

Design Time Debugging

DTS: Messageboxes in ActiveX scripts.

SSIS: MessageBoxes in Script tasks (); Breakpoints in script task code thanks to VSA; visualizers in data flow; Better error messages; Error Flows

Location independent package initialization

DTS: Dynamic Properties + ActiveX scripts.

SSIS: Configurations + Property Expressions.

Loops

DTS: ActiveX scripts.

SSIS: Built in Loop construct including predefined helper enumerations for looping over files, tables, roles, etc.

Restartability

DTS: ActiveX scripts

SSIS: Control flow checkpoints support built into the product. Data Flow can be simulated by breaking data flow into separate control flow tasks that provide checkpoint functionality by writing state out to raw files, SQL tables, etc.

Deployment

DTS: Tips on SQLDTS.com

SSIS: Deployment wizard; support in dtutil.exe for batch files.

Operational Security

DTS: Package Password, SQL Security, Agent Service Account.

SSIS: Package Password, numerous other encryption options, SQL Security, additional Roles, proxies.

Extensibility Points

DTS: Tasks, Transforms (hard)

SSIS: Tasks, Transforms, Source Adapters, Destination Adapters, Log Providers, Loop enumerators, Connection Managers.

Data Transformations and Cleansing

DTS: Limited set of transformations + ActiveX

SSIS: Extensive list of transforms, High performance data flow engine, domain independent cleansing transforms from Microsoft Research, error flows.

BI Functionality

DTS: AS/DM Tasks

SSIS: AS/DM Tasks; ability to push data directly to AS and DM without landing; Reports based on packages; Shared metadata between AS/RS/IS projects.

Any others that should be included? Anyone else done this mapping already?

regards

SSIS: How to do Upserts

ashvinis — Wed, 15 Jun 2005 19:34:00 GMT

This one came up earlier today. How would one do Upsert in the pipeline?

Lets break down the Upsert (Insert if no row currently exist, Update if it does) into two parts:

1. Find out whether a row should be inserted or updated in the table.

2. Perform the insert or update.

For #1, various options are available. Perhaps the first thing to consider would be a lookup transform. The caching mode would depend on whether you can fit everything in memory (Full), the reference set is too big and there're repeated keys you need to lookup (Partial), or the reference set is too big and there are no repeated keys (None). The SCD transform logically uses the no cache lookup internally.

Another way some folks have done this detection is to use an outer join and use 'Null' keys.

For #2, the inserts are straightforward.. just push them out to a destination using either OLEDB or SQL depending on your destination type, and using fast load/table locking if appropriate.

For Updating existing rows, you have several options. The most straight forward is to use an OLEDB Command destination that sends one SQL statement per row coming in. This is what the SCD configures. That might be okay if you've only got a small number of updates, however if you've got a large set, I prefer landing this set to a temporary table and then doing Set based updates in a following Execute SQL task. This was briefly described here.

Deletes can be handled the same way as Updates... land the keys to a separate table and blitz through the deletions using a set based SQL operation.

Any other best practices you've found in your usage?

WAITFOR

ashvinis — Tue, 14 Jun 2005 15:43:00 GMT

A generally useful part of your toolkit, I just found out about the new version of the WAITFOR statement in SQL Server 2005. BOL says it

"Blocks the execution of a batch, stored procedure, or transaction until a specified time or time interval is reached, or a specified statement modifies or returns at least one row."

http://www.juliankuiters.id.au/article.php/go-home-with-waitfor

SSIS: Practical perf debugging - baselining

ashvinis — Tue, 14 Jun 2005 14:36:00 GMT

We had a lot of interest in SSIS at TechEd. One of our fervent users, Thomas from Denmark, asked how to further optimize his ~500K rows/second package. This is a commonly asked question and one of the first thing we need to have a discussion around getting a baseline perf for your environment. Steps:

0. Make sure you have enough memory for package execution. No use worrying about perf if you're swapping buffers out to disk.

1. Get the following baseline:

a. Source->RowCount throughput

b. Source->Destination throughput

c. For existing packages, replace Destinations with RowCount.

Once we have these 3 numbers, we have a good idea about some bounds for the performance. Given these,

2. Incrementally add group of components to see effect on wall clock.

3. Optimize slow components for resources available. E.g. if you have more CPUs available, utilize them.

If you find interesting performance characteristics, let us know!

SSIS: Using InfoPath XML Files in SSIS XMLSource Adapter

ashvinis — Wed, 25 May 2005 21:54:00 GMT

Thanks to Wenyang for writing all this up! Just posting it on her behalf.

Summary

InfoPath forms can be saved to XML, these XML Files can later be used in SSIS XMLSource adapter to pull out the data in tables and columns. However, there are some common problems you may meet in these scenarios. This article describes how to work around these potential problems. The issues mentioned in this article is not only specific to InfoPath files, it can also be referenced in other similar situations as well.

1 Stripping out multiple spaces

XMLSource does not support multiple namespaces. For example, for a XML saved from InfoPath, it will typically look like

<?xml version="1.0" encoding="UTF-8" ?>

<?mso-infoPathSolution PIVersion="1.0.0.0" solutionVersion="1.0.0.1" name="urn:schemas-microsoft-com:office:infopath:oob:ExpenseReportDomestic:1033" productVersion="11.0.6357" ?>

<?mso-application progid="InfoPath.Document"?>

<exp:expenseReport xmlns:exp="http://schemas.microsoft.com/office/infopath/2003/sample/ExpenseReport" xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD" xmlns:xhtml="http://www.w3.org/1999/xhtml" xml:lang="en-us">

<exp:employee>

<exp:name>

<exp:prefix />

<exp:givenName />

<exp:middleName />

<exp:surname />

<exp:suffix />

…

Note the multiple namespaces specified. When we use this file for XMLSource adapter, and ask the XMLSource to infer a schema for the xml file by pressing “Generate XSD…” button in the UI, an error will pop up saying

“Unable to infer the XSD from the XML file. The XML contains multiple namespaces.”

Work around:

Unless the multiple namespaces in the above xml make real differences in your scenario, you can always try to use only one single namespace to work around the problem. There are two options

Manually remove the unnecessary namespaces specified on the XML file.
Use a SSIS XMLTask XSLT to strip out namespaces.

XMLTask supports an operation called XSLT, in which users can specify a XML Source, a XSL style sheet source, and then execute to apply the style sheet on the XML source to get a file in new format. We can use a style sheet to get rid of multiple namespaces from the XML file.

I have a sample xsl style sheet which can be used to strip out all namespaces(use this only in case when multiple namespaces don’t make sense to your scenario)

<?xml version="1.0" encoding="utf-8" ?>

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:output method="xml" indent="no" />

<xsl:template match="/|comment()|processing-instruction()">

<xsl:copy>

<xsl:apply-templates />

</xsl:copy>

</xsl:template>

<xsl:template match="*">

<xsl:element name="{local-name()}">

<xsl:apply-templates select="@*|node()" />

</xsl:element>

</xsl:template>

<xsl:template match="@*">

<xsl:attribute name="{local-name()}">

<xsl:value-of select="." />

</xsl:attribute>

</xsl:template>

</xsl:stylesheet>

The way to use this style sheet is: In SSIS designer, add a XMLTask, choose XSLT operation, then point “Source” to your XML source file and “SecondOperand” to your XSL file(“File Connection” as the Type can be used so as to conveniently pointing to files in both cases), set “SaveOperationResult” to true to obtain the XSLT operation result. Then use this result in XMLSource in the downstream dataflow task.

Note: <?mso-infoPath…> will be ignored by XMLSource, so it’s ok to leave it there.

2 Wrap up the fields in a single layer XML source

An extreme example of using InfoPath xml source is when the form has only one single level, no hierarchy. In other words, originally the XML file is something like

<?xml version="1.0" encoding="UTF-8"?>

<?mso-infoPathSolution solutionVersion="1.0.0.2" productVersion="11.0.6357" PIVersion="1.0.0.0" href="file:///c:\infofile.xsn" name="urn:schemas-microsoft-com:office:infopath:Info1:-myXSD-2005-04-27T19-26-55" ?>

<?mso-application progid="InfoPath.Document"?>

<my:myFields xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2005-04-27T19:26:55" >

<my:FirstName>Wenyang</my:FirstName>

<my:LastName>Hu</my:LastName>

<my:PhoneNumber>425-123-4567</my:PhoneNumber>

</my:myFields>

XMLSource will think all fields under the root – “FirstName”/”LastName”/”PhoneNumber” as tables, instead of columns which belong to a same table. That’s not going to work.

Work Around

The way to work around this is, after removing the unnecessary namespaces, add a pair of tags to wrap the fields. So change the above to

<?xml version="1.0" encoding="UTF-8"?>

<FirstName>Wenyang</FirstName>

</myFields>

</myTable>

Then the columns will be retrieved from XMLSource successfully - there will be an output called “myFields” and “FirstName”/”LastName”/”PhoneNumber” will be the output columns of that output, as expected.

3 Deal with the fields with missing values

<?xml version="1.0" encoding="utf-8" ?>

<name>

</name>

<city>Redmond</city>

</address>

…

<email>johns@hotmail.com</email>

</employee>

</expenseReport>

It’s common that the InfoPath users to leave certain fields in a form blank, like the “prefix” and “givenName” fields in “name” above. Then in XMLSource, if the users ask the XMLSource to infer a XSD, the inferred schema won’t set the data types for those fields, then a bug in XMLDataReader will cause all those fields be treated as tables. That’s very wrong.

Work Around

Before getting the fix for the bug, work arounds for this problem include

1) Give values for all fields.

2) 1) sometime is not acceptable. In that case, ask the XMLSource to infer a XSD file first, then manually edit that XSD file, add “type="xs:string” to that element.

For example, change

<xs:element minOccurs="0" name="prefix"/> to

<xs:element minOccurs="0" name="prefix" type="xs:string>

Will solve this problem.

SSIS: Downloading a file over HTTP

ashvinis — Wed, 25 May 2005 21:46:00 GMT

A question came up on an internal alias about how can one download a file over HTTP. Here's a script that does that, should be trivial to wrap it inside a task if you forsee doing this over and over again.

I can't get the colors to show up on the post, though.

Imports System.Net

Public Sub Main()

Dim myWebClient As WebClient

Dim remoteUri As String

Dim localFileName As String

Dim fireAgain As Boolean

Try

myWebClient = New WebClient()

' get the context from variables

remoteUri = CStr(Dts.Variables("RemoteUri").Value)

localFileName = CStr(Dts.Variables("LocalFileName").Value)

' tell the user what we're downloading where

Dts.Events.FireInformation(0, String.Empty, String.Format("Downloading '{0}' from '{1}'", localFileName, remoteUri), String.Empty, 0, fireAgain)

' do the actual download

myWebClient.DownloadFile(remoteUri, localFileName)

Dts.TaskResult = Dts.Results.Success

Catch ex As Exception

' post the error message we got back.

Dts.Events.FireError(0, String.Empty, ex.Message, String.Empty, 0)

Dts.TaskResult = Dts.Results.Failure

End Try

End Sub

Integration Services @ TechEd U.S.

ashvinis — Tue, 17 May 2005 20:50:00 GMT

Kamal and I are going to be presenting three sessions between us on Integration Services at the TechEd in a few weeks. Of course no SSIS conference would be complete without Donald who will have a couple of surprises for attendees. In addition to the formal talks we've also got some time set aside to chat in the Cabanas.

The titles are (paraphrasing):

- Overview of data warehousing support in SSIS

- Manageability and operations view of SSIS

- Performance and scalability with SSIS

If you're attending either this TechEd or plan to attend other TechEds this year (since they share content from the US based one), let us know what you'd like to see or if there're specific areas you'd like us to cover. The slides are due this Friday, so you can hit the iron while it's hot!

Hope to see quite a few of you there!

ash

BI Webcasts

ashvinis — Wed, 11 May 2005 23:03:00 GMT

Sudi (from SQL Marketing) wanted more folks to know about the upcoming BI webcasts from MSDN and TechNet. Here's a link and description of the series:

http://www.microsoft.com/events/series/sqlserverbi.mspx

Your business intelligence (BI) applications need to integrate seamlessly into your overall application infrastructure to deliver real competitive advantage. Are you thinking about how to leverage your existing investment in BI and without sacrificing the ability to extend and add cutting-edge functionality? Is developing BI applications and managing them across multiple data platforms and tools becoming a complex and costly venture? Microsoft SQL Server 2005 is around the corner, delivering its enterprise-class BI platform with rich and powerful integration, analysis, and reporting functionality.

Tune in to this series as we show you how to:

Take advantage of high-availability features in SQL Server 2005 BI
Increase SQL Server 2005 BI application performance using the new scalability features
Use the single management shell and new features to easily manage and deploy BI applications
Extend the functionality of SQL Server 2005 BI applications using the .NET Framework
Develop rich applications on top of SQL Server 2005 BI platform using ADOMD.net, XML and the new management object model
Add reporting functionality to any application by easily embedding Report Controls in your applications
Use the SQL Server data mining to plug in your own data mining algorithms

Through interactive presentations and end-to-end scenario demonstrations, this series provides you with the knowledge you need to develop and implement easy-to-manage, adaptive BI application architecture. The powerful SQL Server 2005 BI platform will help you keep pace with the evolving needs of your organization.

Bonus: Attend any live webcast in this series to qualify to win a Portable Media Center (official rules) pre-loaded with our best webcasts.

WinDays

ashvinis — Tue, 26 Apr 2005 07:34:00 GMT

Yesteday, Thierry, a colleague from the Analysis Services team and I arrived to Opatija. The flight was great, it was the first time I tried noise cancelling headphones and they just rock! That and the experience of coming here kept us with about 3 hours of sleep in 36 hours or so.

One thing I love about Croatians is that they're just really passionate about ... well... almost everything from what I can see. They love their country, their hot chocolate, their ties (did you know that's a Croatian contribution to the world?), their food, their sports, their islands, and they absolutely love technology! There're about 200 speakers here for WinDays, and over a thousand attendees. Plus a few hundred support staff. That's pretty neat for a country with a population less than half of New Delhi, where I'm originally from. There're also folks who're taking others into production with SSIS, a newsflash which is always welcomed.

Our driver was pretty passionate about his job of delivering us from Zagreb (the capital) to the supposedly-sunny Opatija. It was raining cats and dogs yesterday, and the driver was doing ~100mph on the highways. I kept hinting by asking Thierry (who had me for an airbag since he was conveniently sitting in the back), ”hey, why don't we drive this fast in Seattle during the rain?” and “what does a speed limit of 80 mean?“ but perhaps should have asked the dude to cut back on the caffeine instead. At one point he said “The rain is good, the police are not on the roads”. well... hello! There's a reason they're not on the roads, dude: it's because they don't want to break their necks chasing maniacs! At one point on a toll checkpoint a policeman asked us to pull over, but that was just because of a spot check to make sure the car wasn't stolen. At another point the car underwent hydroplaning at which point the guy got a hint for a little while. Finally the traffic in Rijeka throttled his passion.

Aside from that excitement, things have been pretty stable. I finally got a chance to focus on my presentations and have just now started working on the demos. Lets see how it goes. The sun is trying to peak out today, and Thierry and I finally found a quiet place to sit and focus that had internet connectivity and reduced levels of tobacco smoke. It was interesting to see a loaded cigarette lighter as a swag in the conference handbag. No wonder it's so hard for Marin (the dev manager of Analysis Services and a Croat) to give up smoking.

that's it for now. Wish me luck for tomorrow.

You want to sing? Or do you want to have a beer? How about a job on the SQL team?

ashvinis — Fri, 22 Apr 2005 01:05:00 GMT

Today, my ever helpful colleague Bob provided a language lesson in Serbian which I'm passing along in case you find yourself stressing out in the ex-Yu area.

In a previous post I had written

Ja bih peva

I thought peva was the same in Russian and Serbian. What I forgot was that the 'e' is not pronounced as 'ee' but rather as the 'e' in bed. So, Bob said it should either be :

Ja bih pevao

which means "I want to sing", or

Ja bih pivo

which means "I want a beer".

The correct answer depends on how much one has had to drink, I guess. Unless you're Sergei, one of our extremely talented Technical Leads, who once gave us a memorable experience by singing a Russian Opera while standing on a table when we had met a really hard internal deadline. Heh heh heh heh. I think I have that somewhere on my pocket pc! That guy (besides being the youngest employee at Microsoft at one point) is such a great sport besides being just a gem of a guy.

This reminds me of the way folks say the name of my boss. Most folks that don't know Hindi pronounce his name as 'Kmäl' (pronunciation key), which in Hindi means 'magic'. I say 'Kml' which means the flower Lotus.

I didn't know how much one could appreciate being around interesting people with so many varied backgrounds until I joined Microsoft. You just use a different part of the brain that would otherwise likely stay dormant. I now know how to curse really well (at the screen, of course) in Serbian/Croatian and Romanian, say a few words in Spanish (and something that'll get your ass kicked in parts of Mexico), how to say thank you in Chinese (Mandarin), how to tell written Korean from Chinese and Japanese, some quite interesting stories about Scotland thanks to Donald, and how similar some words in Hindi are to Persian. While the grammar of Serbian is hard, I've been intimated by Gaelic for now. All the 'kh's remind me of Arabic that I got a chance to learn for a couple of years growing up, which, BTW, I get to read once in a while thanks to Mohammed. Maybe when I go to Scotland.

One of our colleagues from my previous team of Analysis Services, for example, comes around lunch time and says the word 'lunch' or 'food' in the native language of the person whose office he's passing, inviting them to join the lunch train. Once in a while I do hear English!

Which brings me to the final part of this rambling post that I'm writing while installing VS & SQL. Aside from all this interesting stuff, we do write software once in a while. Some pretty good one at that. The SQL team has several openings up on the career website. If you're up for a challenge or just want to try your luck out like I did, drop us a resume. We've got a couple of positions in the SSIS team as well... for both dev and test. And if you're really good, positions could be created for you. But, that's another story.

bok!

SSIS: Mysterious hangs during execution

ashvinis — Tue, 12 Apr 2005 00:15:00 GMT

Folks have been running into issues recently about package hanging for mysterious reasons. Here's a simple checklist:

1. Check (using either FileMon or the SSIS Perf counter on Buffers Spooled or other disk related perf counters) to see if the disk is busy doing something like spooling buffers.

2. Check to see if readers & writers into your database have caused a deadlock. sp_who and sp_lock are your friends. I usually don't have to go to the DMVs to get any more information.

3. In a rare case (and rarer everyday now), bumping up the EngineThreads property alleviates the issue. If that works, please please please open a bug so that we can make sure it's resolved. We'll need the package and the execution plan.

That's it for now!

ja bih peva

SSIS: DefaultBufferMaxRows and DefaultBufferSize

ashvinis — Wed, 06 Apr 2005 23:26:00 GMT

There're two properties on the Data Flow task that have piqued enough interest to cause this post. Note that while I'm describing numbers below, the SSIS team reserves the right to change them in response to our performance testing. There're no secrets here, most of this information is exposed plainly and some other (like MaxBufferSize) can be retrieved easily as well. This post hopefully puts it together.

First the basics:

DefaultMaxBufferRows: maximum number of rows in a buffer. Defaults to 10,000.

DefaultBufferSize: maximum size of the buffer (in bytes). Defaults to 10MB.

Now, how do these inter-relate? I.e. how many rows will go down a path in a buffer?

Well, first of all that depends on the type of buffer and what columns will be in it. For the purposes of this post, assume that one execution tree has one type of a buffer. This means in my previous post's data flow task, there will be three types of buffers. Moving from one execution tree to another means a new buffer type and that means memcpy, something that users should typically avoid. However, getting an execution tree could also mean the scheduler might allocate a different thread for components on this execution tree, so you might get more CPUs being utilized. More on that when we discuss execution plans.

Back to the buffer sizes. Lets take one execution tree (#1) that goes from Flat file -> Data Convert -> Lookup and ends up going into the Sort. Note that the buffer type is the same even though in my case I'm creating new columns in Data Convert and bringing in new columns from the reference table in the Lookup. Why? Because memcpy just for reformatting memory is bad for performance so we try to do as little of that as possible. That means the underlying buffer that the Flat File produces has some extra columns that are not filled in until downstream components get a chance. There're also some optimizations the pipeline does to reuse columns in the buffer when possible like when a multicast is producing two outputs, both of which have a derived column that produce a column of the same type. So, it's not a trivial exercise to find out what columns are on a buffer but hopefully you can get a ballpark. Lets say this is RowSize in a buffer.

A few other variables should be called out here.

MaxBufferSize (not the DefaultMaxBufferSize). This cannot be changed externally. Currently it's set to 100MB but that might change. The reason for this maximum is to avoid wastage of memory where most of the buffer is empty. You'll get an error if you attempt to increase DefaultMaxBufferSize to more than this.

MinBufferSize. This is the minimum size of the buffer and currently has the granularity of VirtualAlloc. On my machine, for example, this is exposed in the following BufferSizeTuning LogEvent:

Rows in buffer type 13 would cause a buffer size less than allocation minimum, which is 65536 bytes. There will be 1365 rows in buffers of this type.

So, I've got a MinBufferSize of 64K.

Given these, the pipeline initialization code (that creates buffer types and sets metadata about them such as what columns and how many rows) then gets busy to calculate the buffer sizes.

If the size of one buffer (RowSize * DefaultMaxBufferRows) will exceed DefaultMaxBufferSize, the number of rows will be reduced to keep the memory requirements predictable. Note that one row cannot span multiple buffers, so in a rare case there'd be a huge buffer with just one row.

If the size of one buffer will be less than MinBufferSize, the number of rows will be increased to at least match MinBufferSize which is the allocation granularity to maximize memory utilization.

If the size of one buffer is between the Min and Max, the memory for a buffer will be a multiple of the allocation granularity that most closely fits the number of rows requested. This is again to maximize memory utilization. The number of rows will be adjusted a little to fit the allocated space.

Drop me a note if you play around these and have some interesting information to share, or better still, write/present on it. I'm also interested in hearing about what further information you need to gain insights into perf tuning the pipeline.

later!

SSIS: 'Log Events' and Pipeline events

ashvinis — Wed, 06 Apr 2005 22:31:00 GMT

I was about to write about data flow buffer size configuration properties but need to get this one out first so that we can refer to it.

The Data Flow task (internally and in the object model also called the 'Pipeline') logs some pretty interesting information that describe the internal scheduler.

To see these messages, one way is:

1. In a package you want to get detailed pipeline logs on, select the 'Logging...' menu option on the control flow.

2. Check the Data Flow task of interest on the tree on the left. Switch over to Details and select the following events:

BufferSizeTuning; PipelineExecutionTrees; PipelineExecutionPlan; PipelineInitialization

3. Run the package and launch the 'Log Events' window from the Control Flow context menu again.

You'll see a new tool window that'll pop up and it will get filled with some events.

BufferSizeTuning

Example:

Rows in buffer type 3 would cause a buffer size greater than the configured maximum. There will be only 2440 rows in buffers of this type.

Next post will talk about what this means.

PipelineExecutionTrees

Example:

begin execution tree 0
   output "Aggregate Output 1" (779)
   input "Data Conversion Input" (793)
   output "Data Conversion Output" (795)
   input "OLE DB Destination Input" (827)
end execution tree 0
begin execution tree 1
   output "Flat File Source Output" (1087)
   input "Data Conversion Input" (958)
   output "Data Conversion Output" (959)
   input "Lookup Input" (117)
   output "Lookup Output" (118)
   input "Sort Input" (470)
end execution tree 1
begin execution tree 2
   output "Sort Output" (471)
   input "Multicast Input 1" (668)
   output "Multicast Output 2" (672)
   input "OLE DB Destination Input" (643)
   output "Multicast Output 3" (674)
   input "Aggregate Input 1" (776)
end execution tree 2

We've discussed execution trees in the past... they start from a source or a component with asynchronous output and go on until a destination or another asynchronous output. Looks like I have 3 execution trees in my package.

PipelineExecutionPlan

The plan for my package is too large to paste here. This was hinted at in an earlier post when describing EngineThreads. Soon...

PipelineInitialization

Example:

No temporary BLOB data storage locations were provided. The buffer manager will consider the directories in the TEMP and TMP environment variables.

These are typically information messages about the directories and other defaults the pipeline will use during its execution.

Next up, DefaultBufferMaxRows and DefaultBufferSize.

Unpivot or Why I love Books Online

ashvinis — Thu, 31 Mar 2005 02:39:00 GMT

OK, I have a confession to make. I thought I knew what Unpivot did. Heck, I've shown demos of it for a few years now. Today, Jason wanted to build a pretty cool package for survey results and I realized I didn't understand how unpivot worked anymore. I think I did when I built the demo, but today I don't.

The survey was on SQL BI and had columns such as:

CustomerName, email, Company, Question1_Answer1, Question1_Answer2, Question2_Answer1, Question2_Answer2, ...

Basically all of the questions had 2 answers and there were about 70 questions. (If you filled out one of such surveys, thank you for spending the time!) What Jason wanted was something like this:

CustomerName, email, Question, Answer1, Answer2

I.e. introduce a new column called Question that would contain “Question1, Question2, etc.” one per row and maintain only two columns for all the answers.

Fortunately Wenyang confirmed that I needed to use an Unpivot. I managed to play around with SSIS to get what we wanted because it's so easy to try something.

But now I'm sitting here trying to understand what's the “Pivot Value“, and what's the “Data Value“? I started trying to understand what Unpivot is by searching the net. MsnSearch said Unpivot is the opposite of Pivot. Great, thanks. Trying to go through ANSI SQL-92's index looking for Unpivot/Pivot/cross tab, and then through several SQL books. Mr. Celko finally confirmed I'm not a smartie. Thanks, buddy. At least I have a head full of hair, grey as they might be.

Finally (and I'm kicking myself why I didn't do it sooner) what really helped was the BOL topic on the Unpivot transformation. The example makes is simple, and it makes sense. What else is there? It breaks down the operation into “Destination Column“ and “Pivot Key Value“, concepts that I understand.

Following the grid in BOL, added two rows - "Destination Column" and "Pivot Key Value" to my scratch paper:

Destination Column			A1	A2	A1	A2
Pivot Key Value			Q1	Q1	Q2	Q2
Column Name	Customer	Email	Question1_Answer1	Question1_Answer2	Question2_Answer1	Question2_Answer2
Data Records	Ashvini	as@somewhere.com	1	2	3	4

That was easy.

The only other thing I need is the name of the column where the Destination Column Values would end up. Well, that's "Question".

Given this information, what do the terms in the UI ("Data Column" and "Pivot Value") mean? Well, "Pivot Value" is the "Pivot Key Value" in the grid above. And "Data Column" is the column name that will contain the data, which is the "Destination Column" in the grid above. At the bottom of the Unpivot UI is the single edit box for the name of the new column which will contain the "Pivot Value"s, which in our case is "Question".

So, why are the names in the UI not the same in the docs? Ironically, I had a hand in that. The new names made sense when Bob (the UI author) and I were discussing them because we understood the feature in and out. Having revisited the description, the names make sense to me again. The description that Unpivot is the opposite of Pivot also makes more sense. I haven't picked up Celko's book again, though. Serves him right for being an elitist. <g>

While we'll try to fix this discrepancy, what helped most was not the names but the description in BOL. Last year I got a chance to spend a week at Product Support understanding how they work and got reports of customers saying our BOL is the best in the industry. With all the attention on features we tend to loose focus on how much good information BOL provides. The docs folks have been a part of the team through the beginning of the product cycle and worked their butts off. Thanks to them I can go home with a lighter head knowing I finally understand something that was bugging me for half the day today.

Thank you guys!

Hvala,

Laku Noc

Errors in visualizers

ashvinis — Wed, 30 Mar 2005 14:33:00 GMT

Dobro jutro,

Kako ste vi?

In recent CTP builds folks are seeing some cells that contains “<Missing Lineage ID>” when using visualizers in a data flow task.

This is a common enough issue that we should address it head on to help folks from wasting their time.

The cause of this error is that due to the optimizations that the data flow engine does internally some data is not available on certain paths even though the designer tells you it is. The “<Missing Lineage ID>” message is the data flow engine way of reporting that the designer asked to see data for a column that's not available.

To explain the workaround for now, lets use a simple layout:

Source -> Derived Column -> SomeComponent

Lets say the Source is producing two columns: 'FirstName' and 'LastName'. The Derived Column then creates 'Name' out of the incoming columns and the SomeComponent then uses the Name column only.

If you add a visualizer on the path between Derived Column and SomeComponent, 'Name' would show up just fine but 'FirstName' and 'LastName' would have the “<Missing Lineage ID>“ error because they're not used by Destination.

So, currently you can only see columns that're used by the component that's getting the data after the visualizer.

One workaround, then, is to use the Advanced Editor for editing the next component (SomeComponent in this case) and adding all columns to the Input Columns collection of that component's input which is receiving the data from the visualizer. Note that some components' validation might warn that unnecessary columns are added to the component while some other components such as some destinations manage their input columns collection tightly and will not allow such tinkering.

Another workaround is to add a UnionAll between the Derived Column and Destination that automatically syncs all the columns. That, however, has the performance penalty of causing memory copies through the UnionAll.

In this scenario, perhaps the best workaround would be to look at the data in the destination itself.

We hope to address this very soon because visualizers are one source of productivity boosts you get from using SSIS.

zdravo

PS. You might be wondering why I'm sprinkling my posts with bits of Croatian/Serbian. Well, in preparation of my trip to WinDays in Croatia (http://www.microsoft.com/croatia/windays/sadrzaj.aspx), I and my 4 year old son have been trying to learn a bit of the language and practicing with a few of our colleagues from ex-Yu. While my wife also joins in the fun, our 4 month old blows bubbles instead. Slacker.