Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to Choose the Best Connectors for SSIS


How to Choose the Best Connectors for SSIS

Author
Message
Mike Frost-458139
Mike Frost-458139
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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

Tim Brown-465757
Tim Brown-465757
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 144

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
Mike Frost-458139
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search