SSIS Talk

When using a Merge or Merge Join component in a data flow, your incoming data is required to be sorted. While it may be easy to drop a Sort component into your data flow, it may make more sense to perform the sort in your source query (if you are using a source such as an OLE DB Source component).

If you decide to use an ORDER BY in your source and want to tell the SSIS Data Flow that your data is sorted, follow the below steps:

(more…)

There are many, many resources out on the ‘Net regarding SSIS and the Lookup component and what each of its cache modes are and how to implement them in your own package. This is going to be a technical post, for those of you interested in what each cache mode does behind the scenes.

(more…)

Matt Masson of the SSIS team has announced publicly that there are no new features for SSIS being released in SQL Server 2008 R2. Think that’s bad news? Think again - the team is hard at work on the next release (the term I’ll use for now) and based on some discussions I’ve been involved in, it sounds promising. SSIS is only going to get better and more competitive, so stay tuned!

There seems to be some misinformation going around about how SQL Server works with ordered data, especially with how the data is *physically* stored, even with a clustered-indexed table.

Please see this post for more details on SQL Server ordering: http://blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx

To add to that list a bit:

• Updates are (almost) the same as select queries, internally, in that updates are turned into selects internally, which means the ordering rules of selects are also at play
• Updates can have parallelism, meaning that updates do not have to preserve update order
• Update order can’t be guaranteed because clustered indexes can be rebuilt with a different order
• Ordering generally happens at the operator level within a query plan; not at the top
• Unless you’ve seen it in BOL or in a MS Blog post, don’t assume anything with respect to SQL Server ordering - even if your queries operate on a given order today!

The PASS team has assembled a unique opportunity for those wishing to expand their SQL Server knowledge, be it in database administration, business intelligence (including, of course, SSIS), blogging, etc…, can attend one or more sessions (out of a total of 24) for free by registering here: http://24hours.sqlpass.org/

No matter where in the world you are, there should be a session available that will appeal to your knowledge craving noggin, so don’t hesitate, sign up today.

The session is officially on Sept 2nd, 2009, but depending on your time zone, it may be Sept 1st or 3rd, so please be sure to check out their time zone information.

It would seem that the DATAllegro acquisition is complete as Microsoft has shipped its first technology preview of the project code-named “Madison” to ten customers for their feedback.

Madison looks to be quite the offering, allowing for rapid growth of a data warehouse architecture because of its “appliance” model as well as integration with existing SQL 2008 instances, minimizing time-to-market. Customers have reported loading over a terabyte of data within an hour using Madison and over 1.5 terabytes of query processing per minute.

When you have a chance, stroll on over to the Madison Web page to read more about it: http://www.microsoft.com/sqlserver/2008/en/us/madison.aspx

For those of you that wish to execute maintenance plans on a server and do not wish to have SSIS installed on said server, you will run into issues if you are on an RTM version of 2008 (version 10.0.1600).

The specific error message is “The SQL Server Execute Package Utility requires Integration Services to be installed by one of these editions of SQL Server 2008: Standard, Enterprise, Developer, or Evaluation. To install Integration Services, run SQL Server Setup and select Integration Services. The package execution failed. The step failed.”

This constraint has been lifted in Service Pack 1 and Cumulative Update 3, each. See: http://support.microsoft.com/kb/961126/

Starting with SQL Server 2005 RTM and through the latest SQL Server 2008 build at the time of this writing, there is a pretty nasty bug with respect to the COUNT(expression) function and its rule that when using an expression, it filters out NULLs in its count results.
(more…)

I just wanted to help dispell the rumors that some have about the Bulk Insert task in SSIS. This task is NOT marked for deprecation. It will be around for quite a while. At this time, rest assured that if you are using the Bulk Insert task, you will be supported for quite some time.

Please visit http://www.codeplex.com/SQLSrvIntegrationSrv/ for a nice collection of SSIS samples ranging from XML destinations to BizTalk integrations to EzAPI. Please be sure to visit the Integration Services CodePlex site if you haven’t already.