Handling Slowly Changing Dimensions in SSIS

I had a great time at PASS last week, and had a chance to talk to a lot of different SSIS users. One of the big topics seemed to be Slowly Changing Dimensions – I had a number of people ask for various improvements to the current Slowly Changing Dimension Transform in SSIS, and also…


Something new for SSIS in SQL Server 2008 R2 November CTP

Ok, so it turns my previous post about R2 wasn’t entirely correct. SSIS does have one new feature which appears in the latest SQL Server 2008 R2 CTP. If you open up the ADO.NET Destination UI, you’ll notice a new “Use Bulk Insert when Possible” check box. Previously, the ADO.NET Destination did all of its…


2009 PASS Summit next week

I’m really looking forward to attending PASS next week! I’ll be presenting two separate talks this time around: Maximize Your SSIS Investment with Tuning Tricks and Tips Session Details Maximize the performance of your packages with this set of best practices and data flow tuning techniques. We’ll show you how to improve the operational efficiency…


No new features in for Integration Services in SQL Server 2008 R2

Update 2009-11-12: One minor feature change announced. Have you been holding your breath waiting for Microsoft to announce the new SSIS features for SQL Server 2008 R2? The answer is pretty simple – nothing new is being added for SSIS in R2. There will be some small bug fixes, but these will also be available in a…


SQL PASS 2009

I was just looking at the sessions for the SQL PASS 2009 Summit and was happy to see so many SSIS presentations on the list! Andy Leonard Applied SSIS Design Patterns Steve Simon SQL Server Integration Services and the modern financial institution Davide Mauri Instrumenting, Monitoring and Auditing of SSIS ETL Solutions Brian Knight Loading…


Looking up SSIS HResult / COMException ErrorCode

The method I describe in my post about handling COMExceptions during package generation works if you have control over the package generation code, but sometimes you’ll be using third party libraries, or debugging after the fact. Note, the error codes, symbolic names, and descriptions for all of the SSIS HResults can all be found on…


Debugging a COMException during package generation

You’ll occasionally receive a COMException when you’re programmatically generating SSIS package – typically when dealing with Data Flow components. These COM Exceptions will provide you with an HRESULT (ErrorCode), and no additional information. System.Runtime.InteropServices.COMException (0xC020801C): Exception from HRESULT: 0xC020801C    at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.AcquireConnections(Object pTransaction)    at MyApp.Program.CreatePackage()    at MyApp.Program.Main(String[] args) Ideally the exception you receive…


Speaking for the Vancouver BI PASS Chapter

I’ll be presenting for the Vancouver BI PASS Chapter next Friday, July 10th, at the Microsoft Canada offices. I really enjoyed meeting the Pacific Northwest SQL Server User Group a couple of weeks ago, and I’m looking forward to sharing SSIS with my fellow Canadians in Vancouver. I’ll be going over some common SSIS pitfalls,…


Scripts and assembly caching in 2005

A recent discussion with MVP Andy Leonard about a new property which showed up on the Script Task in SQL Server 2005 SP3 reminded me that I’ve been meaning to blog about how the script task and script component cache their precompiled code at runtime. This behavior has changed in every SP since 2005 RTM,…


Speaking at the PNW SSUG

I’ll be speaking at the Pacific Northwest SQL Server User Group meeting next Wednesday, June 10th. I’ll be giving an updated version of the Avoiding Common Pitfalls in SSIS talk I gave at TechEd a couple of weeks ago. It will be my first time attending a user group meeting, and I’m really looking forward…