SSIS and Netezza: Loading data using OLE DB Destination

In the first post I provided details about the environment I was working in and the tools I was using when loading data into Netezza tables via SSIS. In the second and third post I showed an odd behavior (bug?) in the OLE DB destination when working with Netezza tables. In this forth post, I will share details about how I am configuring the OLE DB Destinations and connection string when pointing to Netezza tables

After installing the Netezza OLE DB provider (v. 5.0.8), I tested the connection to Netezza from outside of SSIS. For that, I used a trick I learned a while back when troubleshooting connectivity issues with Pervasive PSQL - another no so popular database engine. I created a .udl (Universal Data Link) file that allowed me to look over all the Netezza OLE DB provider properties and test the connection. You can find more about how I did it here: http://msdn.microsoft.com/en-us/library/e38h511e(VS.71).aspx

This how the udl file looks like:

TIP: The nice thing about using an /udl file is that you can open the file in a text editor to see the connection string.

  

The Netezza connection string looks like:

Provider=NZOLEDB;Password=MyPassword;User ID=MyID;Data Source=NZipaddress;Initial Catalog=MyDB;Persist Security Info=True;

Once I confirmed that the driver and the permissions of the Netezza user I was using were all squared out, I was ready to create my first package. Since this was a straight copy of data from SQL Server to Netezza, the logic in the package was nothing really exciting. So, let me jump directly to the settings of the OLE DB destination component.

Netezza OLE DB destination settings

I have found the best performance and the less trouble when using the following settings: 

• 
  
  AccessMode = OpenRowSet Using FastLoad 
• 
  
  AlwaysUseDefaultCodePage = True 
• 
  
   FastLoadMaxInsertCommitSize = 0   

Notice that FastLoadMaxInsertCommitSize = 0 is the default selection and something that you would typically change if you are loading data into a SQL Server table instead. But for Netezza destiantions, using a value different than zero seems to degrade performance considerably. Ah! and remeber you have to use the OLE DB destiantion propeties window or the advance editor to select "OpenRowSet Using FastLoad".

  Additionally, you can add the Loggin Level and path parameters to the connection string to create a very, VERY verbose log file, which may come handy when troubleshooting issues:

Logging Level=1;Log Path=c:\LogDir;

 In a subsequent post, I will talk about another issue I am facing when trying to run several of this packages concurrently using a 'parent' package. Hopefully, I will have a solution by the time I post it.

Anyone else loading data into Netezza vis SSIS-OLE DB?. It is amazing the little inforamtion available about it out there.