I was helping a friend of mine to debug performance issue with his SSIS package. The package was pretty simple – he needed to read data, shard it using some partitioning scheme, then insert the results into 8 SQL destinations. The hashing algorithm used for partitioning was rather complex for conditional...

I got a follow up question to my old post regarding enumerating rows in SSIS buffer , that suggested using following code to process rows in custom SSIS transform: while (buffer.NextRow()) {   // do something with the row } Here is the question: buffer.NextRow() moves the pointer forward, so following...

Can SSIS Lookup do what this user wants it to do? I have a problem with a lookup output, I get this warning: The Lookup transformation encountered duplicate reference key values when caching reference data. I know what it is, but I don't like to avoid this warning, I'd like to get all the rows...

If you execute SSIS packages from custom applications, you own the application and thus you are responsible for configuring .NET runtime properly to get the maximum performance. .NET configuration is usually performed using .exe.config files, so it is a just matter of providing good config file. How...

I've got an interesting question/statement about event handlers: Tasks fire the same EH at the same time. My understanding is all EHs fire at the same time (Parallel). If I understand the question correctly, the package has an event handler that can handle multiple events , and these events fire...

Currently SQL Business Intelligence Development Studio (BIDS) and all the project types (AS, IS and RS) live in Visual Studio 2005. So don't try to open a solution that contains IS project in VS 2008 yet. What about final SQL 2008 - now that Visual Studio 2008 is released - what are the plans for BIDS...

How can one deploy packages programmatically? Here is the original question - Is it possible to deploy a package programmatically? We have an application which has a work flow for approval of object. If the object (ssis package) is approved by the concerned authority it has to be deployed to sql...

A recent commenter suggested running SSIS using SQL/CLR: Just an idea on how to do this that may be a bit easier than any of the methods covered. IF you were to write a CLR procedure which accepts a string as its parameter. The string passed in would be the xml definition of a package (either loaded...

Often, I see people reporting "zzz fails if I'm not admin". Do I have to be machine admin to use SSIS? Well, absolutely no. Most SSIS tasks can be performed without being admin at all. Actually, I develop on Vista machine and I very rarely need to elevate to admin status . Then why would you get...

One of the mechanisms that SSIS data flow engine utilizes to achieve high performance is “back pressure”. Let’s consider a simple package with a source and destination. What happens if the source is fast and destination is slow? Say source is huge local raw file and destination is a remote relational...

With first Katmai (SQL Server 2008) CTP out, I think it is time to blog about some performance and scalability improvements in this release. I'll assume readers are familiar with SSIS data flow performance concepts, if not make sure you've read these two articles: http://www.microsoft.com/technet...

Runying Mao and Len Wyatt did a great study of SSIS performance, and posted some results: Getting Optimal Performance with Integration Services Lookups http://blogs.msdn.com/sqlperf/archive/2007/04/24/getting-optimal-performance-with-integration-services-lookups.aspx OLEDB Source with Views...

The SSIS Script Task and Script Component editors don't provide functionality (available in full VS) to add a reference to unmanaged COM components. Can this be done at all? Well, yes, although lack of GUI option in VSA (which SSIS uses for editing and debugging scripts) makes it a bit complex. We...

I was forwarded a question about SSIS Script Component on 64-bit, I think the answer may benefit others as well: Currently we are referencing a 32-bit third party component in a script component and running the SSIS package in 32 bit mode on a X64 bit server. Recently, the third party vendor has...

All the samples in MSDN show how use SSIS API from C# or VB.NET code. Is it possible to do this from C++ code? The answer is yes, if you know COM it should be quite easy for you, and the COM API is very close to .NET API (I think .NET API is nicer due to rich type system and properties). Note that...

I got several questions asking what is the best way to run SSIS packages programmatically. One question is about running SSIS from a .NET 1.1 application (SSIS uses .NET 2.0). Another about running package remotely "Do I really have to write an ASP.net app just to run a package on the server?" There...

Debugging support for SSIS packages is probably one of the best features of the SSIS 2005 designer, but sometimes you may find that "Start Debugging" (F5) and "Start without Debugging" (Ctrl-F5) commands are grayed out. Why? The reason might be obvious to developers who worked with Visual Studio,...

If you like SSIS, and want to work in SQL BI team, we are hiring . http://blogs.msdn.com/birecruiting/

This is a common question at SSIS forum, so I decided to post an answer here to save myself from re-typing it again and again :) Symptoms : you run a package and get an error either "product level is insufficient ..." or "The task ... cannot run on this edition of Integration Services. It requires...

By 'where package is running' I mean what CPU and memory resources are used, and where the SSIS needs to be installed. This simple question surprisingly often confuses users, as there are many options to store packages and many options to run it. The answer is very short - inside the application that...