Search results matching tags 'ssis' and 'SQL'

SQLSaturday #69 - Philly Love

Mike C — Mon, 07 Mar 2011 01:38:00 GMT

Thanks to the Philly SQL Server User Group (PSSUG) and to everyone who attended SQLSaturday #69 in the City of Brotherly Love yesterday. It was a great event with a lot of great people. My presentations are available for download at the links below:

http://www.sqlsaturday.com/viewsession.aspx?sat=69&sessionid=3333

http://www.sqlsaturday.com/viewsession.aspx?sat=69&sessionid=3334

I just went through my speaker evaluations, and I'm happy to report the response was pretty positive across the board. Having lived in Philly, I know Philadelphians aren't shy about telling how they really feel, so I really appreciate the positive feedback.

For those of you who wrote comments with areas for improvement, rest assured I appreciate the feedback and I'll work your suggestions into future presentations!

Next stop is SQLSaturday #71 in Boston (http://www.sqlsaturday.com/71/eventhome.aspx). Lots of top-notch speakers presenting at this one, and I'm looking forward to learning a little Power Shell from the master, and maybe learn a little bit o' that DBA stuff this time around :)

See you in Boston!

Try-N-Save SSIS Packages

Mike C — Mon, 29 Nov 2010 05:17:00 GMT

The Try-N-Save SSIS Packages from my SSIS Dimensional Data Optimization presentation are available at http://cid-8f7e6c950afc6b3e.office.live.com/self.aspx/.Public/Presentation/TryNSave.zip.

I'm still working on getting the sample database uploaded - even compressed a backup is larger than SkyDrive's upload filesize limit. I'll script it out when I have time (in addition to DDL, there are some tables that need to be prepopulated).

High Performance Dimensional Data Loads With SSIS Presentation

Mike C — Sat, 20 Nov 2010 18:25:00 GMT

Just finished giving the SSIS High-Performance Dimensional Data Load presentation at SQLSaturday #59 NYC. Here are the slides in PDF format. I'll upload the Try-N-Save code and sample data later for attendees to play with.

Thanks to everyone who attended my session and thanks to Melissa D. and NJSQL for putting this together. For those who are interested in Alejandro Mesa's composable DML solution to the problem of Type 2 dimension updates, here's the complete statement from the demo:

INSERT INTO Dim.Geography_Hash
(
CityName, CountyFIPS, CountyName, StateFIPS,
StateUSPS, StateName, ZIP, TimeOffset,
DaylightSavingTime, StartDateID, CurrentFlag, BatchID,
LineageID, CubeInd, SortOrder, Hash
)
SELECT CityName, CountyFIPS, CountyName, StateFIPS,
StateUSPS, StateName, ZIP, TimeOffset,
DaylightSavingTime, StartDateID, CurrentFlag, BatchID,
LineageID, CubeInd, SortOrder, Hash
FROM
(
MERGE INTO Dim.Geography_Hash AS Target
USING Staging.Geography_Hash AS Source
ON Target.ZIP = Source.ZIP
  AND Target.CurrentFlag = Source.CurrentFlag
WHEN MATCHED AND Target.Hash <> Source.Hash
  THEN UPDATE SET CurrentFlag = 'N'
WHEN NOT MATCHED
  THEN INSERT
  (
   CityName, CountyFIPS, CountyName, StateFIPS,
   StateUSPS, StateName, ZIP, TimeOffset,
   DaylightSavingTime, StartDateID, CurrentFlag, BatchID,
   LineageID, CubeInd, SortOrder, Hash
  )
  VALUES
  (
   Source.CityName, Source.CountyFIPS, Source.CountyName, Source.StateFIPS,
   Source.StateUSPS, Source.StateName, Source.ZIP, Source.TimeOffset,
   Source.DaylightSavingTime, Source.StartDateID, Source.CurrentFlag, Source.BatchID,
   Source.LineageID, Source.CubeInd, Source.SortOrder, Source.Hash
  )
OUTPUT $action, inserted.CityName, inserted.CountyFIPS, inserted.CountyName, inserted.StateFIPS,
   inserted.StateUSPS, inserted.StateName, inserted.ZIP, inserted.TimeOffset,
   inserted.DaylightSavingTime, inserted.StartDateID, inserted.CurrentFlag, inserted.BatchID,
   inserted.LineageID, inserted.CubeInd, inserted.SortOrder, inserted.Hash
)
AS T
(
action, CityName, CountyFIPS, CountyName, StateFIPS,
StateUSPS, StateName, ZIP, TimeOffset,
DaylightSavingTime, StartDateID, CurrentFlag, BatchID,
LineageID, CubeInd, SortOrder, Hash
)
WHERE action = 'UPDATE';

As mentioned, the OUTPUT clause on the inner MERGE statement feeds the outer INSERT clause. Next stop is SQLSaturday #61 in DC at the beginning of December.

It's Official - SQLSaturday is Coming to NYC!

Mike C — Sat, 06 Feb 2010 19:59:00 GMT

New Jersey SQL Server User Group (NJSQL) is bringing SQLSaturday #39 to NYC on April 24, 2010! The free all-day training event will be hosted by Microsoft at their Midtown Manhattan offices. The speaker line-up is growing fast—if you'd like to present, visit the event's open call for speakers. This is a free full-day training event, but registration is required to attend. Seating is limited.

Registration, speaker, and sponsorship details are posted at http://www.sqlsaturday.com/39/eventhome.aspx.

"SQL Server MVP Deep Dives" Book Available for Early Access/Pre-Orders

Mike C — Thu, 17 Sep 2009 00:21:00 GMT

The MVP authored book, SQL Server MVP Deep Dives is now available for early access and pre-orders at http://manning.com/nielsen/. The book is divided into 5 sections that cover everything from building a proper relational database to developing BI solutions:

1. Database Design and Architecture
2. Database Development
3. Database Administration
4. Performance Tuning and Optimization
5. BI Development

This book covers a lot of ground with a lot of expert tour guides. Weighing in at 850 pages, it was written, tech-reviewed and edited by dozens of Microsoft MVPs with intimate knowledge of SQL Server. The contributor list includes some of the biggest names in SQL Server: Paul Nielsen, Adam Machanic, Kalen Delaney, Itzik Ben-Gan, Aaron Bertrand, Hugo Kornelis, John Paul Cook, and several others. For me getting a copy of this book will be like getting the opportunity to pick the brains of some of the most knowledgable folks in the industry on a wide variety of topics.

This is already one of my favorite books that I've had the chance to contribute to (I wrote a couple of chapters discussing on SQL Server XML in general and SQL Server XQuery specifically) because all of the royalties go to a charity that helps children who are war victims!

So go get the book, learn what the pros know, and do your good deed for the day -- all at the same time!

You can get more info on SQL Server MVP Deep Dives at Manning Publications: http://manning.com/nielsen/ and at Amazon http://www.amazon.com/SQL-Server-MVP-Deep-Dives/dp/1935182048/.

SSIS: living in a parallel world? Not yet...

AlbertoFerrari — Sun, 14 Sep 2008 19:57:00 GMT

Today I come into a nice bug (misfeature?) of SSIS 2008 and I'd like to share it with you. Let's start from the beginning:

One of the keys to make something speedy is to parallelize it. In TableDifference I read from two inputs, compare them and produce several outputs for new, deleted, updated rows and so on. It is the classical "produce/consumer" paradigm that works fine in parallel, creating a consumer thread that compares rows and different threads to produce them.

During the porting of the component in SQL 2008 I discovered that my code does not work as expected. It reads all the lines, then terminates abnormally without producing a single row of output. It took me a couple of hours and several coffes to discover the change in the way SSIS handles asyncronous components causing my bug.

In order to make the problem easier to understand (for microsoft connect too), I produced the same behaviour with a much simpler script component. Imagine to have a script transformation that needs to make some very complex work on each row. You will end up with something like this:

    public override void Input0_ProcessInputRow(Input0Buffer Row) {
        Output0Buffer.AddRow();
        Output0Buffer.LineID = VeryComplexFunction(Row);
        if (Row.EndOfRowset ()) {
           OutputBuffer.SetEndOfRowset ();
        }
    }

But, if you have a 64 CPU computer. You can imagine to detach many threads, each one handling just one very complex function, in this way you will produce a massive parallel computation. Wow! The solution to get maximum speed would be this:

    public override void Input0_ProcessInputRow(Input0Buffer Row) {
        Thread T = new Thread(SendAsyncRow);
        T.Start(Row.EndOfRowset());
    }

    public void SendAsyncRow(Object O) {
        Output0Buffer.AddRow();
        Output0Buffer.LineID = VeryComplexFunction();
        if ((bool)O == true) {
            Output0Buffer.SetEndOfRowset();
        }
    }

You detach a separate thread for each row, the thread will compute the very complex function and send it to the output buffer as soon as it is ready. In order to make SSIS understand when everything is finished, you signal SetEndOfRowset in the last thread.

Don't start crying, this code will never work, the SetEndOfRowset is called on the last read line and not on the last produced one, but this is just pseudocode to understand the topic, don't blame me for inconsistencies. :) Let's go on with the main topic.

Under SSIS 2005 this code (in VB, of course) would work fine as the engine detects the end of processing when it receives the EndOfRowset on the outputs. In SSIS 2008 the engine detects the end of the processing as soon as the last call to ProcessInputRow has finished. So, in our case, after the last thread has been detached SSIS believes that the processing is finished and calls the cleaning up of the component, completely ignoring both the presence of separate threads running and our williness to not terminate the component because we know that we need some more time.

The result? As with any other parallel bug you will sometime get a crash, on the same inputs sometime finish corectly, sometime get an error... the nightmare of any programmer.

If you want to try it by yourself, cut and paste this code to a transformation script in a data flow that process some lines ( rows 10/20 lines are enough):

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

using System.Threading;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent {

    bool useThreads = false;

    /// <summary>
    /// 
    /// Processes input rows
    /// 
    /// </summary>
    /// <param name="Row"></param>
    public override void Input0_ProcessInputRow(Input0Buffer Row) {
        Thread T = new Thread(SendAsyncRow);

        if (useThreads) {
            T.Start(Row.EndOfRowset());
        } else {
            SendRow();
        }
    }

    /// <summary>
    /// 
    /// Sends a row in sync mode
    /// 
    /// </summary>
    private void SendRow() {
        // Thread.Sleep(100);
        Output0Buffer.AddRow();
        Output0Buffer.LineID = VeryComplexFunction();
    }

    /// <summary>
    /// 
    /// Sends a row in async mode
    /// 
    /// </summary>
    /// <param name="O"></param>
    public void SendAsyncRow(Object O) {
        Thread.Sleep(100);
        Output0Buffer.AddRow();
        Output0Buffer.LineID = VeryComplexFunction();
        if ((bool)O == true) {
            Output0Buffer.SetEndOfRowset();
        }
    }

    /// <summary>
    /// 
    /// Not very complex... but it's a demo!
    /// 
    /// </summary>
    /// <returns></returns>
    private int VeryComplexFunction() {
        return 10;
    }
}

using Serial mode (useThreads se to false) everything works fine. Using parallel mode (useThreads set to true) SSIS will crash and produce no output at all. The bug in TableDifference was very similar to that, I needed to handle the wait for termination of the consumer process during the ProcessInput function.

It is not the first time I discover inconsistencies in the way SSIS handles parallelism, in order to try to make it easier to write code (using 2008 mode there is no need to call SetEndOfRowset and your program will run fine if you forget it) the architects of SSIS introduce a behavior that will make better and correct code crash. If you believe this is a problem, vote here, anyway... be aware of it, it mighe be useful in the future. :)

Mantaining order in SSIS flow, problems with Merge

AlbertoFerrari — Tue, 10 Apr 2007 06:48:26 GMT

In a previous post I spoke about the advantages of having sorted flows in SSIS to greatly speed up data insertion using fastload. The need to have a sorted flow brings some severe problems to the SSIS programmer that he need to be aware of and that IMHO Microsoft should address with a future implementation of SQL Server.

Let’s have a look at the problem.

In the image you can see a very typical SSIS data flow where you need to manage the error flow of a lookup component and go on with the processing. Even if you can’t see it from the picture, think that Sample Source will produce a sorted output of several millions rows and we want to insert into TestTable with the same sorting.

This task works fine but it has a big problem: the Union All component will lose the sorting of the flow as it will handle data from both its input in an unordered way. This is not a bug, Union All has its behaviour by design. Still we have a problem and we know that sorting several millions rows after the Union All component is not an option for memory consumption.

In SSIS you have another component, Merge, that will kindly maintain the order of its inputs, so you can change your package this way:

Everything will work fine until you have a package that should handle several millions rows where only a very few of them (say 1.000) will enter the lookup error output flow.

In such a situation Merge will start caching ALL the rows from its first input (the row that correctly matched lookup) until something will come from its second input (rows coming from the Derived Column task). The problem is that SSIS will NOT call the ProcessInput method of the Derived Column task until its buffer reaches a certain amount of rows (normally 10.000 or a number like it) and this will not happen because only 1.000 rows will enter the Derived Column path. In such a situation Merge will start consuming memory and will fill up all the available memory really fast leading to crashes and/or very poor performances.

Even this behavior of both Merge and SSIS is “by design”, so we cannot complain it. But it could be easily solved setting the max number of rows of the buffer in the Derived column to 1 in order to call ProcessInput immediately (if I know what kind of data I will read I can easily set up values that make my SSIS package run faster). Unfortunately there is no such kind of option in SSIS so, at present, the problem cannot be solved.

Needless to say, even if this is a problem, you can try to make your sorted package run without any merge component and you will be able to maintain the sort of the flow but this is a really big limitation in expressivity of your ETL algorithm so awareness of the problem is mandatory to successfully complete your ETL process.