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!

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/

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.

Thanks to my friend, Doug, we now know the top list of SSIS knowledgebase articles! Thanks Doug!

http://dougbert.com/blogs/dougbert/archive/2009/03/17/find-the-top-knowledge-base-articles-for-ssis.aspx

Just a quick tip… If you’re having issues debugging the Execute Process Task, ensure that you have a variable created and mapped to each of the process output properties of the Execute Process Task. That is, the StandardOutputVariable and the StandardErrorVariable properties. The StandardOutputVariable will capture any standard output of the process (the text output normally displayed on the screen). The StandardErrorVariable will capture any error output from the specified process.

Doing the above and then setting an OnPostExecute breakpoint on the Execute Process Task, you can use the Locals window to inspect the value of the variables after the process was executed, but before SSIS clears out their values once you’re done debugging.

I know, this isn’t “breaking” news or anything, but what is new is the white paper detailing how Microsoft was able to achieve this record breaking speed using SSIS. Check it out below as its a very interesting read, and it may help generate some new ideas for your implementations.

http://blogs.msdn.com/sqlperf/archive/2009/03/03/an-etl-world-record-revealed-finally.aspx

From the SSIS development team, Matt Masson has posted a few blog posts on how to use the SSIS API. The posts use references to the 2008 version of SSIS, but to modify them for 2005 requires a simple change in most cases – Upgrading custom SSIS 2005 components to 2008.

You can find the blog posts here: http://blogs.msdn.com/mattm/archive/2008/12/30/samples-for-creating-ssis-packages-programmatically.aspx

Also, there is a post on using a new API framework for SSIS 2008, titled EzAPI: http://blogs.msdn.com/mattm/archive/2008/12/30/ezapi-alternative-package-creation-api.aspx

I haven’t looked into the EzAPI yet, but it certainly sounds interesting.

Let me know what you think about the new posts and if you’d like to see any thing else from the dev team and I’ll pass it along.