How to Choose the Best Connectors for SSIS

  • Mike Frost-458139

    SSC Enthusiast

    Points: 130

    Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/mfrost/3209.asp


    Kindest Regards,

    Mike Frost

  • Tim Brown-465757

    Old Hand

    Points: 327

    Mike, one thing we've found in dealing with the MS and IBM OLE DB providers for DB2:  the MS provider doesn't support BLOB or CLOB columns.  The IBM does, but it requires every CHAR field to be converted from NVARCHAR.

    Also, we seem to have trouble with the OLE DB connectors staying connected in our packages.  They burp for strange reasons, we go in and 'reconnect' the connectors (edit them and do a test connection) and then they work flawlessly.  Any suggestions?

     

  • Mike Frost-458139

    SSC Enthusiast

    Points: 130

    Hi Tim,

    These are great questions. In response to your comment about Microsoft's and IBM's OLE providers, I don't know whether these behaviors are inherent limitations of those providers or something requiring a patch or setting to resolve. If I had to guess, I would go with the former based on what I have read and heard about these providers, but the truth is I don't know for sure.

    With regards to your question on OLE DB connections "burping" and needing to be refreshed, I can think of a couple of explanations. You didn't mention whether the connections are idle for a period of time before the connections go stale. If that is the case, and the OLE DB providers you are using have some sort of default idle connection timeout value set, then that would explain the behavior. Look for a setting that specifies the connection timeout and try setting it to zero (to never time out the connection) or a value high enough to ensure that the issue doesn't occur as frequently as it does now.

    The database server you are connecting to may be set to timeout idle connections as well. Some data connectivity drivers and providers offer a "keep alive" setting to resolve this issue which periodically "pings" the database during periods of application inactivity to keep the database from terminating the connection. See if the provider you are using offers such a setting and try enabling it.

    Another possibility is that there is some sort of network blip within your environment that is invalidating the connections. This is more likely to happen if the connections are made over a WAN or via tunnelling into a corporate network but wouldn't explain this behavior if it is happening consistently.

    My final suggestion is to give DataDirect's Connect64 for SSIS providers for DB2 a try and see if you get any different results for either of the two behaviors you described. Particularly in the area of the data type mapping, you may experience better results.

    http://www.datadirect.com/products/ssis/index.ssp

    Full disclosure: I am the product manager for this product so I am hardly an unbiased voice on the matter. That said, all of DataDirect's SSIS providers can be downloaded and tested for free, so you can evaluate this for yourself rather than simply taking my word for it.

    Let me know if you have any other questions or feedback and thank you for your comments!


    Kindest Regards,

    Mike Frost

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply