May 18, 2011 at 5:11 am
Hello,
I've got an OLD informix system that I need to grab data from via odbc as part of an ssis job.
the odbc driver for informix only supports odbc 2.0, not 3.0
the package always fails with:
ERROR [01000] [Microsoft][ODBC Driver Manager] The driver doesn't support the version of ODBC behavior that the application requested (see SQLSetEnvAttr).
So I'm assuming that SSIS wants odbc 3 and as such the copy is not even commencing.
I dont know what to do! Please help!
Thanks
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
May 18, 2011 at 6:49 am
for info the same error comes up when trying to access as a linked server.
php odbc uses this DSN on this box without issue.
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
May 18, 2011 at 12:01 pm
Wow, Informix, ODBC 2.0, PHP...say hi to Fred Flintstone for me 😀
SSIS (it's really .NET under the covers) is going to make those calls to the driver asking for metadata and I don't think you can change that.
<me>
<speculating>I can't speak for your Informix drivers specifically but in general (did I just say that) newer data access drivers are backward compatible to older versions of the platforms they connect to. Maybe you could try a newer version of the Informix driver that conformed to the ODBC 3.0 spec and can also connect to your older Informix database instance.</speculating>
</me>
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 19, 2011 at 2:02 am
alas I had feared this to be the case...
Unfortunately there IS NO newer driver! the version of informix is so old that IBM do not release a driver for it and I have had to use a proprietary 3rd party driver (costing some £300 I might add!)
<me>
<feeling dejected>
back to the drawing board...
</feeling dejected>
</me>
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
May 19, 2011 at 7:05 am
Sorry to hear that.
This may sound wonky and will be a bit slower than direct connections from SSIS but given the situation I figured I would throw some mud at the wall. What about:
1) use PHP as a shim: create a PHP web service that talks to Informix that SSIS can consume
or
2) work through flat files: draw flat files from Informix using whatever native bulk export tool they provide (they provide one right?) and have SSIS call that bulk export tool to get a flat file and then start from there loading the flat file
^^^ Awesome use of XML too BTW 😉
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 19, 2011 at 7:12 am
Yea there are options to consider.
Unfortunately unloading data to flat files is a no go flat files are all pipe delimited, not text qualified and users type pipes in free text. derr. also being a sco 5.0.7 system the htfs file system cannot handle files > 2gb in size so some tables will fail to unload anyway.
the PHP option is the best one available if I can't get the direct odbc running properly but I fear that will be unbelievably slow. as it is, this process DOES work on other servers so I might be able to fix it yet...
Thanks
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
May 19, 2011 at 7:28 am
Are you seriously wanting to run 2GB datasets through SSIS as a part of a regularly scheduled job or are we talking about a migration here? If that ODBC driver does not support paging out data in a stream as it comes available (some platforms/drivers have to build the entire dataset in memory before anything is returned to the ODBC consumer) then I think you could have issues anyway. Have you ever pulled that much data at one time out of this Informix instance before?
I am thinking that having a PHP web service proxy 2GB of data as an XML document for SSIS is doomed-from-the-start. With that much data I rescind my PHP shim idea unless you use offsets to only nibble off small batches of data until you have brought everything over you wanted.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 19, 2011 at 7:40 am
this process has been in place for - oh, i dont know - 5 years now.
we pull over 8GB every night.
thats since it was trimmed down - used to be 11GB!
turns out it was a firewall issue - it's working now so I just have to sort out a licensing issue and all will be well!
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
May 19, 2011 at 7:43 am
Wait, glad you got it sorted, but what was a firewall issue? What did you do about the original SSIS error? Or are you sticking with some existing process?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 19, 2011 at 7:49 am
the ssis error said that the odbc link did not support the required odbc level of behaviour or some such words to that effect.
The source of the error was a communication link failure from the driver to the server and the driver appears to report errors in a non odbc 2.x compliant manner.
this new server that the job is running on was not included in the correct firewall group.
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
May 19, 2011 at 8:39 am
Wow, that's neat! I have seen the error you originally posted on different forum sites but don't ever remember seeing a resolution where the original poster traced it back to a firewall or networking issue. Did you simply have to open the Informix-specific ports between the server running SSIS and the server hosting Informix? I am only asking so that this post may eventually help someone else with the same issue.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 19, 2011 at 8:49 am
Yep, thats exactly what it was. The informix driver (Esker's tunodbc200.ifx) works over port 5371 outbound, then seems to get a response on ports 2000-2010 and then back again on 47500-47510
<?xml version=\"1.0\" ?>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:dat="http://lmgtfy.com">
<soapenv:Header />
<soapenv:Body>
<dat:person name="ben">
<dat:problem category="odbc driver">
Fixed - was a firewall issue
</dat:problem>
</dat:person>
</soapenv:Body>
</soapenv:Envelope>
look at me go! my XML is getting more advanced now!
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
May 19, 2011 at 9:24 am
So much for the .NET object underneath SSIS bubbling up the actual error message from the failed network call. Thanks for posting back...hopefully it helps someone else.
PS nod towards the Google-nugget in your XML 😀
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply