Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to Choose the Best Connectors for SSIS Expand / Collapse
Author
Message
Posted Friday, August 24, 2007 1:18 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 19, 2009 3:19 PM
Points: 2, Visits: 3
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/mfrost/3209.asp


Kindest Regards,

Mike Frost

Post #393771
Posted Thursday, August 30, 2007 8:49 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 9:08 AM
Points: 23, Visits: 130

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?

 

Post #395146
Posted Saturday, September 1, 2007 8:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 19, 2009 3:19 PM
Points: 2, Visits: 3
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

Post #395718
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse