Search results matching tags 'sql server integration services' and 'dataflow'

Dataflow mechanics [SSIS]

jamiet — Tue, 25 Oct 2011 07:48:00 GMT

Once upon a time I blogged at http://consultingblogs.emc.com/jamiethomson but that ended in August 2009 when I left EMC. There is a lot of (arguably) valuable content over there however certain events in the past leave me concerned that that content is not well cared for and I don't have any confidence that it will still exist in the long term. Hence, I have taken the decision to re-publish some of that content here at SQLBlog so over the coming weeks and months you may find re-published content popping up here from time-to-time.
This is the second such blog post in which I discuss the internals of the SSIS Dataflow. The first post in this series can be found at [SSIS] OnPipelineRowsSent.

During my activity on the SSIS forum I've noticed that much of the content is in regard to the dataflow task and that's not a surprise given that its the most useful tool in the SSIS box and also the most complex. This post is me brainstorming some of the stuff that I know about the dataflow and hopefully it proves useful to some of you.

Buffer Architecture. If I'm ever interviewing you for a job as a SSIS developer you can lay a lot of money to say that I'll ask you to tell me what a buffer is. Buffers are fundamental to the dataflow - they are what the dataflow uses to move data around. A buffer is essentially an area of memory and by default consists of approximately 10000 rows (usually slightly less than that) and that's why when you execute a dataflow within BIDS the row counts on the data paths go up in approximate increments of 10000. Part of performance tuning a SSIS dataflow is about manipulating various properties until you find the optimum number of rows in each buffer and you can read more (much more) about that here.
Dataflows contain components which are generally categorised into synchronous and asynchronous. The most definitive description of these is that the output from a synchronous component uses the same buffer as the input; asynchronous components create a new buffer for their output. All source adapters are asynchronous components, all destination adapters are synchronous. Synchronous components are generally quicker than asynchronous components.
Asynchronous components are further categorised as partially-blocking or fully-blocking. Fully-blocking components require all rows from upstream before they put any data into the output; partially-blocking components will start to output data before they receive all upstream rows.
Execution trees. Each asynchronous component creates what is called an execution tree in the dataflow. In SSIS 2005 (but not in later versions) each execution tree uses one execution thread so another part of performance tuning is to fully utilise all processors on your hardware. Read more here.
OnPipelineRowsSent. All executables in a SSIS package throw events and one of the events throws by the dataflow is OnPipelineRowsSent. When a component outputs a buffer of data then it throws a OnPipelineRowsSent event and thus enables us to know how many rows each component has processed. When you execute a dataflow within the development environment (aka BIDS) these events are consumed and are used to change the rowcounts that you see increasing as more rows are processed.
Spooling. I said earlier that all buffers are a space in memory but of course memory is finite so if there is more data in the pipeline than can fit in memory then buffers will get spooled to disc. The location on disc is defined by BLOBTempStoragePath & BufferTempStoragePath. Spooling will severely impact dataflow performance so avoid if possible.
A lot of people ask if its possible to remove columns from the dataflow once they have finished using them. For example, if columns called [FirstName] & [LastName] are concatenated together to make [FullName] its likely that those two columns won't be needed anymore. The simple answer though is no. Once the data is in memory it would be an overhead to remove the data and "squeeze" the buffer up to make it slower which is why those columns still appear downstream. This is nothing to be concerned about - its highly highly unlikely they are heavily impacting performance. Of course, if an asynchronous component is encountered then a new buffer will be created on the output and the unrequired columns will (probably) be removed. This issue is further discussed here.
Following on from the previous point...its intuitive to think that columns that begin at a component don't exist prior to the data being processed by that component. In fact that's not true. Prior to dataflow execution the execution plan for a dataflow is determined and it is at that point that all columns are defined and thus created (i.e. space is set aside in memory). So, all columns that will be used in a buffer exist even before the buffer gets any data.
The datatypes of columns in the dataflow are different from datatypes used for SSIS variables. To this day I don't understand why the SSIS team opted to use different datatypes in the control flow and data flow and I hope this changes one day. (UPDATE: SSIS Development Manager Jeff Bernhardt addresses this issue in A potted SSIS history via Connect.)
The stock components (i.e. those provided out-of-the-box) are mostly written in native code (XML Source and the Script Component are exceptions to this rule). SSIS provides a .Net API that enables you and I to build our own components and hence it is tempting to think that these custom components won't work as quickly as stock components. This is probably true but really the difference is negligible. The majority of the work (validation, memory management, buffer editing etc...) is done by native code so you're not going to suffer severe performance problems by implementing custom components.
The BLOB data types (i.e. DT_TEXT, DT_NTEXT, DT_IMAGE) can severely impact dataflow performance so try and avoid them if you can.
Raw files can be used to pass data from one dataflow to another - even if those dataflows are in different packages. Raw files have a proprietary file format that is essentially a match of the data in memory and hence reading to and writing from them is extremely quick. People often seem reticent to place data into raw files but I don't hesitate to recommend using them if you need to.
There is an important property on each component output called IsSorted. A lot of people think that setting this property to TRUE will cause the data in that output to be sorted. That's not true - this property only informs the dataflow engine that the data is sorted, nothing more. If you set this property to TRUE and the data is not sorted then you will probably be creating problems for yourself later on (for example a downstream Merge Join component will not fail but it won't produce the correct results either).
Source and destination adapters maintain external column collections which are used to store the metadata of the external data sinks that those adapters connect to. There are two reasons for this as far as I can determine. Firstly to enable offline development (a big criticism of SSIS's predecessor DTS was that offline development wasn't possible) and secondly to enable the dataflow to validate itself. More information here.
Although it appears in BIDS as though the data in a buffer "moves" from one component to another that isn't actually the case. Data in a buffer doesn't actually move about in memory. My fellow MVP Phil Brammer (blog | twitter) once used an analogy of cars travelling on a road to describe this. The buffers are analogous to cars on the road and milestones along the road are analogous to the components. Instead of thinking of the cars moving along the road to reach the milestones, think of the cars as being stationery and the road moving along underneath the cars.
Back pressure is an important concept in an SSIS dataflow. Backpressure occurs when a dataflow is producing data to a destination faster than the destination can consume it (a common phenomenon when inserting into a relational database table) - this creates contention further back down the dataflow, hence the term "backpressure". Michael Entin (one of the original developer geniuses that built the dataflow engine) talks more about back pressure at SSIS Backpressure Mechanism.

I'll probably add to this post over time as new things occur to me. In the meantime if you want a more detailed description of how the dataflow works then Kirk Haselden's book has a whole chapter devoted to it. You can also pose questions in the comments although I'd urge you to post questions to the SSIS forum where more people will be available to answer and where your question may already have been answered.

@Jamiet

[SSIS] OnPipelineRowsSent event

jamiet — Fri, 19 Aug 2011 11:48:00 GMT

Once upon a time I blogged at http://consultingblogs.emc.com/jamiethomson but that ended in August 2009 when I left EMC. There is a lot of valuable content over there however certain events in the past leave me concerned that that content is not well cared for and I don't have any confidence that it will still exist in the long term. Hence, I have taken the decision to re-publish some of that content here at SQLBlog so over the coming weeks and months you may find re-published content popping up here from time-to-time.
This is the first such blog post in this series in which I talk about the little-known OnPipelineRowsSent event.

An interesting discussion today on the SSIS Forum made me realise that perhaps not all SSIS developers are aware of the OnPipelineRowsSent event in SSIS and how valuable it can be, certainly when debugging data-flows. This isn't all that surprising because Books Online is very sparse in this area. Hence I thought it would make a useful blog topic.

OnPipelineRowsSent is a type of event in SSIS which means the information that it provides can be logged by whatever log provider you happen to be using. In order to understand the information that OnPipelineRowsSent provides it is critical to understand the buffer architecture of the SSIS data-flow.

To explain that VERY simply, a buffer is an area of memory that contains rows of data as they pass through the pipeline. Each data-path in the data-flow will contain one of more buffers and each of those buffers (except for the last one) will contain the same number of rows. If you want to understand more about the buffer architecture of the data-flow then Kirk Haselden's book has a chapter devoted wholly to it.

Here is what Books Online DOES say about OnPipelineRowsSent:

OnPipelineRowsSent

Reports the number of rows provided to a component input by a call to the ProcessInput method. The log entry includes the component name.

In slightly plainer english that means that for every buffer of data that is consumed by a component, you get a log entry. So, if you have (e.g.) a Derived Column Component that receives 105000 rows and the size of the buffer is 10000 rows, you'll get 11 OnPipelineRowsSent log entries for that component (there will only be 5000 rows in the last buffer). Basically it enables you to know how many rows are passing through each data-path in the data-flow.

The available information is the same as you get for any eventhandler. i.e.

event
computer
operator
source
sourceid
executionid
starttime
endtime
datacode
databytes
message

and here is a portion of a logfile containing those records:

OnPipelineRowsSent,CGOJATHOMSON,INT\Jamie.Thomson,Data Flow Task,{EB25C41A-EFE2-4902-9610-99C0309A3B59},{33B4926F-54A9-4E00-9B74-92720A2786E0},08/03/2007 20:49:56,08/03/2007 20:49:56,0,0x,Rows were provided to a data flow component as input. : : 1228 : Merge Join Output : 1225 : Union All 1 : 1226 : Union All Input 1 : 9936
OnPipelineRowsSent,CGOJATHOMSON,INT\Jamie.Thomson,Data Flow Task,{EB25C41A-EFE2-4902-9610-99C0309A3B59},{33B4926F-54A9-4E00-9B74-92720A2786E0},08/03/2007 20:49:56,08/03/2007 20:49:56,0,0x,Rows were provided to a data flow component as input. : : 1475 : Union All Output 1 : 1470 : Sort 3 : 1471 : Sort Input : 9936
OnPipelineRowsSent,CGOJATHOMSON,INT\Jamie.Thomson,Data Flow Task,{EB25C41A-EFE2-4902-9610-99C0309A3B59},{33B4926F-54A9-4E00-9B74-92720A2786E0},08/03/2007 20:49:56,08/03/2007 20:49:56,0,0x,Rows were provided to a data flow component as input. : : 1228 : Merge Join Output : 1225 : Union All 1 : 1226 : Union All Input 1 : 2085
OnPipelineRowsSent,CGOJATHOMSON,INT\Jamie.Thomson,Data Flow Task,{EB25C41A-EFE2-4902-9610-99C0309A3B59},{33B4926F-54A9-4E00-9B74-92720A2786E0},08/03/2007 20:49:56,08/03/2007 20:49:56,0,0x,Rows were provided to a data flow component as input. : : 1475 : Union All Output 1 : 1470 : Sort 3 : 1471 : Sort Input : 9936
OnPipelineRowsSent,CGOJATHOMSON,INT\Jamie.Thomson,Data Flow Task,{EB25C41A-EFE2-4902-9610-99C0309A3B59},{33B4926F-54A9-4E00-9B74-92720A2786E0},08/03/2007 20:49:56,08/03/2007 20:49:56,0,0x,Rows were provided to a data flow component as input. : : 1475 : Union All Output 1 : 1470 : Sort 3 : 1471 : Sort Input : 2085
OnPipelineRowsSent,CGOJATHOMSON,INT\Jamie.Thomson,Data Flow Task,{EB25C41A-EFE2-4902-9610-99C0309A3B59},{33B4926F-54A9-4E00-9B74-92720A2786E0},08/03/2007 20:49:56,08/03/2007 20:49:56,0,0x,Rows were provided to a data flow component as input. : : 2121 : Sort Output : 2113 : Merge Join 2 : 2114 : Merge Join Left Input : 9936

The important stuff is what comes in the last field, the message so let's break that down. Here is an example message:

Rows were provided to a data flow component as input. : : 1030 : OLE DB Source Output : 1025 : Sort 2 : 1026 : Sort Input : 9972

And here's what it is constituted of:

Rows were provided to a data flow component as input. - That's consistent in each message, so to be honest they could have left it out
1030 - ID of the data-path providing the buffer
OLE DB Source Output - Name of the data-path providing the buffer
1025 - ID of the component receiving the buffer
Sort 2 - Name of the component receiving the buffer
1026 - ID of the input receiving the buffer
Sort Input - Name of the input receiving the buffer
9972 - Number of rows in the buffer

As you can see, this information will enable you to determine exactly how many rows are output from each component. Thus, if you are not receiving the expected number of rows at a destination this event will help you to deduce where you are losing them from.

You may not know this but you actually encounter OnPipelineRowsSent every time you run a data-flow within the SSIS Designer in BIDS. Notice how the designer shows the number of rows passing through the pipeline and through each component:

How does the designer know how many rows are passing through the data-flow? Simple! Its because the SSIS Designer consumes the OnPipelineRowsSent event that the executing package "throws" up and then presents that information visually as the numbers that you see flashing in front of your eyes as the data-flow executes.

I think that just about covers the simple stuff around OnPipelineRowsSent. If you have any questions then post them here as a comment.

@Jamiet

Why is there no CROSS JOIN component in SSIS?

jamiet — Fri, 08 Jul 2011 09:02:00 GMT

I saw an interesting question on the SSIS MSDN forum yesterday where someone was asking how to do a cartesian product (i.e. a CROSS JOIN) in a SQL Server Integration Services (SSIS) dataflow. Links were provided to various articles that cover this (see Performing a Cross Join (Cartesian Product) in SSIS which explains how you can fool the Merge Join component into doing a CROSS JOIN) after which the original poster replied saying:

Such a long procedure to perform a simple cartesian product. SSIS must really improve from this point of view.

Well apparently others agree because there are two requests on Connect for a Cross Join component (161154 & 127215) - both have been closed with status "Won't Fix". I don't know why this is but I can speculate.

Think about what is being asked for here. A CROSS JOIN operation potentially requires masses of memory (and would even more so for SSIS given that we are typically processing large data volumes). CROSS JOINs work in SQL Server because we have this helpful little thing called [tempdb] and there is no analogous mechanism in SSIS (save for disk spooling). SSIS is deliberately not optimized for mass data spooling - it is optimized for processing small batches of data at a time before passing them off to some destination.

I suspect this is why there is no Cross Join component to go alongside the Merge Join component. The capabilities to achieve a CROSS JOIN operation exist (see link above) but they want you to be deliberate about it - they're not going to give you enough rope to hang yourself with by providing a component that could bring the machine of an unwitting developer to its knees.

As I said this is speculation so don't assume this is the real reason for there being no Cross Join component.

What are your thoughts? Assuming its true is this justification enough for there being no Cross Join component? Should there be one? Do you even need one? Let me know in the comments.

@jamiet

Merge Join component sorted outputs [SSIS]

jamiet — Mon, 31 Jan 2011 19:40:00 GMT

One question that I have been asked a few times of late in regard to performance tuning SSIS data flows is this:

Why isn’t the Merge Join output sorted (i.e.IsSorted=True)?

This is a fair question. After all both of the Merge Join inputs are sorted, hence why wouldn’t the output be sorted as well? Well here’s a little secret, the Merge Join output IS sorted! There’s a caveat though – it is only under certain circumstances and SSIS itself doesn’t do a good job of informing you of it.

Let’s take a look at an example. Here we have a dataflow that consumes data from the [AdventureWorks2008].[Sales].[SalesOrderHeader] & [AdventureWorks2008].[Sales].[SalesOrderDetail] tables then joins them using a Merge Join component:

Let’s take a look inside the editor of the Merge Join:

We are joining on the [SalesOrderId] field (which is what the two inputs just happen to be sorted upon). We are also putting [SalesOrderHeader].[SalesOrderId] into the output. Believe it or not the output from this Merge Join component is sorted (i.e. has IsSorted=True) but unfortunately the Merge Join component does not have an Advanced Editor hence it is hidden away from us. There are a couple of ways to prove to you that is the case; I could open up the package XML inside the .dtsx file and show you the metadata but there is an easier way than that – I can attach a Sort component to the output. Take a look:

Notice that the Sort component is attempting to sort on the [SalesOrderId] column. This gives us the following warning:

Validation warning. DFT Get raw data: {992B7C9A-35AD-47B9-A0B0-637F7DDF93EB}: The data is already sorted as specified so the transform can be removed.

The warning proves that the output from the Merge Join is sorted!

It must be noted that the Merge Join output will only have IsSorted=True if at least one of the join columns is included in the output.

So there you go, the Merge Join component can indeed produce a sorted output and that’s very useful in order to avoid unnecessary expensive Sort operations downstream. Hope this is useful to someone out there!

@Jamiet

P.S. Thank you to Bob Bojanic on the SSIS product team who pointed this out to me!

Always use dtexec.exe to test performance of your dataflows. No exceptions.

jamiet — Wed, 12 Jan 2011 21:51:00 GMT

Earlier this evening I posted a blog post entitled Investigation: Can different combinations of components effect Dataflow performance? where I compared the performance of three different dataflows all working to the same overall goal. I wanted to make one last point related to the results but I thought it warranted a blog post all of its own.

Here is a screenshot of one of the dataflows that I was testing:

Pretty complicated I’m sure you’ll agree. Now, when I executed this dataflow in the test it was executing in ~19seconds however in that case I was executing using the command-line tool dtexec. I also tried executing inside the BIDS development environment and in that case it took much longer – 139seconds. That’s more than seven times as long.

The point I want to make is very simple. If you are testing your dataflows for performance please use dtexec. Nothing else will suffice.

@Jamiet

UPDATE: Matt Masson from the SSIS team has posted a great blog post explaining exactly why dtexec is quicker. Go read it at Why does my package run slower in BIDS than DTEXEC?

Investigation: Can different combinations of components affect Dataflow performance?

jamiet — Wed, 12 Jan 2011 20:28:00 GMT

Introduction

The Dataflow task is one of the core components (if not the core component) of SQL Server Integration Services (SSIS) and often the most misunderstood. This is not surprising, its an incredibly complicated beast and we’re abstracted away from that complexity via some boxes that go yellow red or green and that have some lines drawn between them.

Example dataflow

In this blog post I intend to look under that facade and get into some of the nuts and bolts of the Dataflow Task by investigating how the decisions we make when building our packages can affect performance. I will do this by comparing the performance of three dataflows that all have the same input, all produce the same output, but which all operate slightly differently by way of having different transformation components.

I also want to use this blog post to challenge a common held opinion that I see perpetuated over and over again on the SSIS forum. That is, that people assume adding components to a dataflow will be detrimental to overall performance. Its not surprising that people think this –it is intuitive to think that more components means more work- however this is not a view that I share. I have always been of the opinion that there are many factors affecting dataflow duration and the number of components is actually one of the less important ones; having said that I have never proven that assertion and that is one reason for this investigation.

I have actually seen evidence that some people think dataflow duration is simply a function of number of rows and number of components. I’ll happily call that one out as a myth even without any investigation!

The Setup

I have a 2GB datafile which is a list of 4731904 (~4.7million) customer records with various attributes against them and it contains 2 columns that I am going to use for categorisation:

[YearlyIncome]
[BirthDate]

The data file is a SSIS raw format file which I chose to use because it is the quickest way of getting data into a dataflow and given that I am testing the transformations, not the source or destination adapters, I want to minimise external influences as much as possible.

In the test I will split the customers according to month of birth (12 of those) and whether or not their yearly income is above or below 50000 (2 of those); in other words I will be splitting them into 24 discrete categories and in order to do it I shall be using different combinations of SSIS’ Conditional Split and Derived Column transformation components. The 24 datapaths that occur will each input to a rowcount component, again because this is the least resource intensive means of terminating a datapath.

The test is being carried out on a Dell XPS Studio laptop with a quad core (8 logical Procs) Intel Core i7 at 1.73GHz and Samsung SSD hard drive. Its running SQL Server 2008 R2 on Windows 7.

The Variables

Here are the three combinations of components that I am going to test:

One Conditional Split - A single Conditional Split component CSPL Split by Month of Birth and income category that will use expressions on [YearlyIncome] & [BirthDate] to send each row to one of 24 outputs.

This next screenshot displays the expression logic in use:
Derived Column & Conditional Split - A Derived Column component DER Income Category that adds a new column [IncomeCategory] which will contain one of two possible text values {“LessThan50000”,”GreaterThan50000”} and uses [YearlyIncome] to determine which value each row should get. A Conditional Split component CSPL Split by Month of Birth and Income Category then uses that new column in conjunction with [BirthDate] to determine which of the same 24 outputs to send each row to. Put more simply, I am separating the Conditional Split of #1 into a Derived Column and a Conditional Split.

The next screenshots display the expression logic in use:

DER Income Category

CSPL Split by Month of Birth and Income Category
Three Conditional Splits - A Conditional Split component that produces two outputs based on [YearlyIncome], one for each Income Category. Each of those outputs will go to a further Conditional Split that splits the input into 12 outputs, one for each month of birth (identical logic in each). In this case then I am separating the single Conditional Split of #1 into three Conditional Split components.

The next screenshots display the expression logic in use:

CSPL Split by Income Category

CSPL Split by Month of Birth 1& 2

Each of these combinations will provide an input to one of the 24 rowcount components, just the same as before. For illustration here is a screenshot of the dataflow containing three Conditional Split components:

As you can these dataflows have a fair bit of work to do and remember that they’re doing that work for 4.7million rows.

I will execute each dataflow 10 times and use the average for comparison. I foresee three possible outcomes:

The dataflow containing just one Conditional Split (i.e. #1) will be quicker
There is no significant difference between any of them
One of the two dataflows containing multiple transformation components will be quicker

Regardless of which of those outcomes come to pass we will have learnt something and that makes this an interesting test to carry out. Note that I will be executing the dataflows using dtexec.exe rather than hitting F5 within BIDS.

The Results and Analysis

The table below shows all of the executions, 10 for each dataflow. It also shows the average for each along with a standard deviation.

All durations are in seconds.
I’m pasting a screenshot because I frankly can’t be bothered with the faffing about needed to make a presentable HTML table.

It is plain to see from the average that the dataflow containing three conditional splits is significantly faster, the other two taking 43% and 52% longer respectively. This seems strange though, right? Why does the dataflow containing the most components outperform the other two by such a big margin? The answer is actually quite logical when you put some thought into it and I’ll explain that below.

Before progressing, a side note. The standard deviation for the “Three Conditional Splits” dataflow is orders of magnitude smaller – indicating that performance for this dataflow can be predicted with much greater confidence too.

The Explanation

I refer you to the screenshot above that shows how CSPL Split by Month of Birth and salary category in the first dataflow is setup. Observe that there is a case for each combination of Month Of Date and Income Category – 24 in total. These expressions get evaluated in the order that they appear and hence if we assume that Month of Date and Income Category are uniformly distributed in the dataset we can deduce that the expected number of expression evaluations for each row is 12.5 i.e. 1 (the minimum) + 24 (the maximum) divided by 2 = 12.5.

Now take a look at the screenshots for the second dataflow. We are doing one expression evaluation in DER Income Category and we have the same 24 cases in CSPL Split by Month of Birth and Income Category as we had before, only the expression differs slightly. In this case then we have 1 + 12.5 = 13.5 expected evaluations for each row – that would account for the slightly longer average execution time for this dataflow.

Now onto the third dataflow, the quick one. CSPL Split by Income Category does a maximum of 2 expression evaluations thus the expected number of evaluations per row is 1.5. CSPL Split by Month of Birth 1 & CSPL Split by Month of Birth 2 both have less work to do than the previous Conditional Split components because they only have 12 cases to test for thus the expected number of expression evaluations is 6.5 There are two of them so total expected number of expression evaluations for this dataflow is 6.5 + 6.5 + 1.5 = 14.5.

14.5 is still more than 12.5 & 13.5 though so why is the third dataflow so much quicker? Simple, the conditional expressions in the first two dataflows have two boolean predicates to evaluate – one for Income Category and one for Month of Birth; the expressions in the Conditional Split in the third dataflow however only have one predicate thus they are doing a lot less work. To sum up, the difference in execution times can be attributed to the difference between:

MONTH(BirthDate) == 1 && YearlyIncome <= 50000
and
MONTH(BirthDate) == 1

In the first two dataflows YearlyIncome <= 50000 gets evaluated an average of 12.5 times for every row whereas in the third dataflow it is evaluated once and once only. Multiply those 11.5 extra operations by 4.7million rows and you get a significant amount of extra CPU cycles – that’s where our duration difference comes from.

The Wrap-up

The obvious point here is that adding new components to a dataflow isn’t necessarily going to make it go any slower, moreover you may be able to achieve significant improvements by splitting logic over multiple components rather than one. Performance tuning is all about reducing the amount of work that needs to be done and that doesn’t necessarily mean use less components, indeed sometimes you may be able to reduce workload in ways that aren’t immediately obvious as I think I have proven here.

Of course there are many variables in play here and your mileage will most definitely vary. I encourage you to download the package and see if you get similar results – let me know in the comments. The package contains all three dataflows plus a fourth dataflow that will create the 2GB raw file for you (you will also need the [AdventureWorksDW2008] sample database from which to source the data); simply disable all dataflows except the one you want to test before executing the package and remember, execute using dtexec, not within BIDS.

If you want to explore dataflow performance tuning in more detail then here are some links you might want to check out:

Any comments? Let me know!

@Jamiet

Don’t turn the dataflow into a cursor [SSIS]

jamiet — Tue, 31 Aug 2010 21:22:00 GMT

I saw a thread on the SSIS forum today that went something like this:

I have the following dataset:

AccountNo Date DailyMovement

00000001 28/08/2010 10

00000001 29/08/2010 5

00000001 30/08/2010 7

00000002 28/08/2010 8

00000002 29/08/2010 6

for which I want to compute a running total per [AccountNo] & [Date] like so:

AccountNo Date DailyMovement RunningTotal

00000001 28/08/2010 10 10

00000001 29/08/2010 5 15

00000001 30/08/2010 7 22

00000002 28/08/2010 8 8

00000002 29/08/2010 6 14

How do I do that using a script component?

That last comment:

How do I do that using a script component?

is a fairly common question. People assume that if a calculated value is dependant on prior rows then a script component needs to be involved because that running total needs to be tracked somewhere, but that isn’t necessarily the case. Ask yourself, how would you do this if the data were residing in a database table, would you do this?:

SELECT '00000001' AS [AccountNo], CONVERT(date,'20080828') AS [Date],10 AS [DailyMovement]            
INTO   #balances            
UNION  ALL            
SELECT '00000001' AS [AccountNo], CONVERT(date,'20080829') AS [Date],5 AS [DailyMovement]            
UNION  ALL            
SELECT '00000001' AS [AccountNo], CONVERT(date,'20080830') AS [Date],7 AS [DailyMovement]            
UNION  ALL            
SELECT '00000002' AS [AccountNo], CONVERT(date,'20080828') AS [Date],8 AS [DailyMovement]            
UNION  ALL            
SELECT '00000002' AS [AccountNo], CONVERT(date,'20080829') AS [Date],6 AS [DailyMovement]            
            
SELECT [AccountNo]            
,      [Date]            
,      [DailyMovement]            
,      (   SELECT  SUM(DailyMovement)            
           FROM    #balances b2            
           WHERE   b1.[AccountNo] = b2.[AccountNo]            
           AND     b1.[Date] >= b2.[Date])            
FROM   #balances b1;

OK that works, and here’s a screenshot to prove it:

But really, would any of you actually do this? Hopefully not, the use of the correlated subquery has simply turned what should be a nice set operation into a cursor-in-disguise (something I have talked about before) because that subquery will be getting executed for every row in the table. Instead you could run the following on that same dataset:

SELECT b1.[AccountNo] , b1.[Date] , b1.[DailyMovement] , SUM(b2.[DailyMovement]) AS [RunningTotal] FROM #balances b1 INNER JOIN #balances b2 ON b1.[AccountNo] = b2.[AccountNo] AND b1.[Date] >= b2.[Date] GROUP BY b1.[AccountNo],b1.[Date],b1.[DailyMovement];

and you get the same result but with a much more amenable execution plan (execute with SET STATISTICS IO ON if you don’t believe me)!

The same principle applies in a SSIS dataflow. Often there is no need to resort to a script component, the same result can be achieved using some smart dataflow design. You can use, for example, the same approach as in the second SQL query (above) using a combination of SSIS’ Sort, Aggregate, Merge Join and Conditional Split components. That is not to say that one will be quicker than the other but at least you’ll have something that is more intuitive and arguably more maintainable. Of course if performance gain is your primary goal then the correct advice is, as always, “test and measure, test and measure, test and measure”!!!

Ask yourself “If I could, how would I solve this using T-SQL?” and see if that influences your dataflow design at all. Invariably script components should be the last choice, not the first!

@jamiet

SSIS gotcha: Beware of multiple outputs from a synchronous script component

jamiet — Sun, 04 Jul 2010 20:43:12 GMT

A few days ago I was alerted to a peculiarity of SSIS’s Script Component that I believe people need to be aware of. Its a peculiarity that can rear its head when your script component is synchronous and has multiple outputs. Here’s an example of a dataflow that contains such a component:

Some things to note about this dataflow that you can’t tell from this screenshot:

The source component will create a 1-row,1-column dataset with the value “1” in it
Even though it has three outputs “SCR Send data to multiple outputs” is still a synchronous component*

*If you don’t understand how a synchronous component can have multiple outputs and/or have never heard of an Exclusion Group then you should go and read my blog post from 2005 Multiple outputs from a synchronous script transform – the blog post you’re reading right now won’t make much sense without it! If you have ever used the Multicast component then it shouldn’t surprise you that a synchronous component can have multiple outputs – because that’s a pretty good definition of what the Multicast component does!

Here’s the important code inside “SCR Send data to multiple outputs”:

The code:

increments the incoming value and puts it into Output0
increments the value again and puts it into Output1
increments the value again and puts it into Output2

Given that out starting value is “1” you might expect that our three outputs would contain the values “2”, “3” & “4” respectively but in fact that is not the case. Here is what we actually see in those three outputs when we execute the dataflow:

Each of our outputs has the same value “4”. Why is that? The trick is in understanding something fundamental about synchronous components, they only ever output the same number of rows as are input. The fact that in the data flow above it appears as though three rows have been output is simply an illusion that is best explained by former SSIS Development Manager Kirk Haselden who left the following comment on my blog post that I linked to earlier:

The additional or duplicate rows are an illusion. The Dataflow Task actually tracks what buffer columns and rows are visible to the downstream transforms, but doesn't copy any buffers or rows. It simply "exposes" them with row and column views.
Truly, the synchronous outputs only send the same number of columns* as they receive on their inputs.
- Kirk Haselden, 15th March 2006
*This is a typo. Kirk meant to say “rows” not “columns”

In our case the script component has simply incremented the same value three times and we are looking at that same incremented value of “4” in each of our outputs.

I have produced a short video that demonstrates this behaviour using the dataflow pictured above. Embedding videos here on SQLBlog is however a fiddly experience so for ease I’ll just direct you to view it on Vimeo at Multiple Outputs from a synchronous script component.

Hope this helps. Any questions please put them in the comments.

@JamieT

Inequality joins, Asynchronous transformations and Lookups : SSIS

jamiet — Fri, 14 May 2010 21:13:00 GMT

It is pretty much accepted by SQL Server Integration Services (SSIS) developers that synchronous transformations are generally quicker than asynchronous transformations (for a description of synchronous and asynchronous transformations go read Asynchronous and synchronous data flow components). Notice I said “generally” and not “always”; there are circumstances where using asynchronous transformations can be beneficial and in this blog post I’ll demonstrate such a scenario, one that is pretty common when building data warehouses.

Imagine I have a [Customer] dimension table that manages information about all of my customers as a slowly-changing dimension. If that is a type 2 slowly changing dimension then you will likely have multiple rows per customer in that table. Furthermore you might also have datetime fields that indicate the effective time period of each member record.

Here is such a table that contains data for four dimension members {Terry, Max, Henry, Horace}:

Notice that we have multiple records per customer and that the [SCDStartDate] of a record is equivalent to the [SCDEndDate] of the record that preceded it (if there was one). (Note that I am on record as saying I am not a fan of this technique of storing an [SCDEndDate] but for the purposes of clarity I have included it here.)

Anyway, the idea here is that we will have some incoming data containing [CustomerName] & [EffectiveDate] and we need to use those values to lookup [Customer].[CustomerId]. The logic will be:

Lookup a [CustomerId] WHERE [CustomerName]=[CustomerName] AND [SCDStartDate] <= [EffectiveDate] AND [EffectiveDate] <= [SCDEndDate]

The conventional approach to this would be to use a full cached lookup but that isn’t an option here because we are using inequality conditions. The obvious next step then is to use a non-cached lookup which enables us to change the SQL statement to use inequality operators:

Let’s take a look at the dataflow:

Notice these are all synchronous components. This approach works just fine however it does have the limitation that it has to issue a SQL statement against your lookup set for every row thus we can expect the execution time of our dataflow to increase linearly in line with the number of rows in our dataflow; that’s not good.

OK, that’s the obvious method. Let’s now look at a different way of achieving this using an asynchronous Merge Join transform coupled with a Conditional Split. I’ve shown it post-execution so that I can include the row counts which help to illustrate what is going on here:

Notice that there are more rows output from our Merge Join component than on the input. That is because we are joining on [CustomerName] and, as we know, we have multiple records per [CustomerName] in our lookup set. Notice also that there are two asynchronous components in here (the Sort and the Merge Join).

I have embedded a video below that compares the execution times for each of these two methods. The video is just over 8minutes long.

View on Vimeo

For those that can’t be bothered watching the video I’ll tell you the results here. The dataflow that used the Lookup transform took 36 seconds whereas the dataflow that used the Merge Join took less than two seconds. An illustration in case it is needed:

Pretty conclusive proof that in some scenarios it may be quicker to use an asynchronous component than a synchronous one. Your mileage may of course vary.

The scenario outlined here is analogous to performance tuning procedural SQL that uses cursors. It is common to eliminate cursors by converting them to set-based operations and that is effectively what we have done here. Our non-cached lookup is performing a discrete operation for every single row of data, exactly like a cursor does. By eliminating this cursor-in-disguise we have dramatically sped up our dataflow.

I hope all of that proves useful. You can download the package that I demonstrated in the video from my SkyDrive at http://cid-550f681dad532637.skydrive.live.com/self.aspx/Public/BlogShare/20100514/20100514%20Lookups%20and%20Merge%20Joins.zip

Comments are welcome as always.

@Jamiet

Destination Adapter Comparison : SSIS Video Nugget

jamiet — Tue, 02 Mar 2010 12:11:00 GMT

In this, my latest video nugget, I demonstrate and compare three ways that you can insert data into a SQL Server table from the SSIS DataFlow:

OLE DB Destination without Fast Load
OLE DB Destination with Fast Load
SQL Server Destination

The demo peels back the covers to see how these three methods actually operate against the database. The video is embedded below however at the time of writing we are still having issues with getting videos to show up on SQLBlog meaning that the video may appear too small to view hence in the meantime I’ll refer you to view the video on Vimeo at http://vimeo.com/9806121 (it is 7m19s long).

If after watching this you want to know any more then you may glean some useful information from my blog post SSIS: Destination Adapter Comparison from August 2006 or feel free to post any questions in the comments below and I’ll do my best to answer them!

Hope this is useful!

@Jamiet

DER Income Category


CSPL Split by Month of Birth and Income Category

CSPL Split by Income Category


CSPL Split by Month of Birth 1& 2

AccountNo	Date	DailyMovement
00000001	28/08/2010	10
00000001	29/08/2010	5
00000001	30/08/2010	7
00000002	28/08/2010	8
00000002	29/08/2010	6