SSIS custom component performance tidbit

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 split, so he created…

0

Does buffer.NextRow() skips the first row in a buffer?

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 your code to the letter…

1

Lookup multiple rows?

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…

3

Configuring .NET for running SSIS packages from custom applications

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 do you know what is good? The simplest way…

1

SSIS event handler threading

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 at about the same time. What happens? Well, I…

0

SQL 2008 & VS 2008

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…

5

Deploying packages

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 server. How can this…

0

Don’t run SSIS package using SQL/CLR

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…

0

Do I have to be admin?

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…

1

SSIS Backpressure Mechanism

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 database. If we read…

0