This is the second post of my SSIS & Netezza series. In an earlier post I provided details about the environment I was working in. At this time, I would like to make a quick note about an interesting behavior in the OLE DB Destination when pointing to a Netezza table. When you open the OLE DB destination editor, there are no “fast load” options under the data access mode property.
For those not familiar with this property, using ‘fast load’ tells SSIS to attempt the load in batches of rows – typically 10k row batches – as opposed of doing row-by-row load/commit operations, that in general yields better performance. Now, the odd thing is that if you look into the properties pane of the destination component, then you see 2 extra options for ‘fast load’.
This means that after you set everything else up in inside of the destination editor, you also have to visit the windows pane of the component to setup the access mode property. Annoying, isn’t it? But wait there is more. If you ever have to open the destination component editor again, the access mode property will get reset to the default value ‘Open Rowset’, so you have to go to the properties window of the component one more time to select the fast load option.
I get the same behavior in SSIS 2005 and SSIS 2008 R2 when using the the Netezza OLE DB driver 5.0.8
I also report this as a bug in the MSFT SQL Server connect site. You can add your notes or vote to get this issue fixed - Assuming it's an SSIS issue - here: SSIS: Fast Load option missing from Netezza OLE DB destination
Are you using SSIS to load Netezza databases? if so, how are you doing it and what issues have you had to work around?
Are you using SSIS to load Netezza databases? if so, how are you doing it and what issues have you had to work around?
Hello Rafael,
ReplyDeleteI know this post is from a long while ago but I am also working with Netezza and SSIS (2008 R2). I find for most things this setup works really, really well but I have seen some issues.
1) I mentioned the exact bug you mentioned here to IBM during the Vegas Big Data convention. They'd "never heard of it" and were "going to look at it". I've not heard anything since.
So yes - I've seen the exact same thing and have been bitten by it (very slow performance).
2) My latest struggle is something we hit early on but now really need to solve. Googling around is how I found your post. I'm trying to pass an input parameter to a Netezza SQL task (or DFT). Using their OLE driver and SSIS 2008 R2 - I see no way to do it and am having difficulty coming up with any workaround.
Furthermore - I guess NZ-SQL has no concept of dynamic SQL. I could build a SQL statement outside of SQL task (in SSIS). If I could pass a string to the SQL task and execute the command that is within the string, I could build my own parameters but NZ-SQL doesn't seem to have the concept of dynamic-SQL.