Importing data from Informix using ODBC fails!

Question

Post reply

Importing data from Informix using ODBC fails!

Benedikt Ries

Mr or Mrs. 500

Points: 503
More actions
October 31, 2006 at 3:15 am

#367774

Help!
I'm trying to create a simple .dtsx package that imports data to SQL server 2005 from an informix db using an ADO.net ODBC connection. I´m using the Informix ODBC Driver 2.90.TC3. The structure of my SSIS-package is quite simple:
(sqltask:truncate destination table) -> (sqltask:"set isolation to dirty read;" at source) -> (sqltask: "set optimization high;" at source) -> (data flow task)
My data source SQL-Query in the dataflow task is simple and it works great on a Informix test-db where no users are working on the data. But when I test the thing on the working environment, the execution of the package fails, error output is the folowing (sry that part of it is german):
[Quote]
SSIS package "Package.dtsx" starting.
Information: 0x4004300A at Datenflusstask, DTS.Pipeline: Die Phase 'Überprüfung' beginnt.
Information: 0x4004300A at Datenflusstask, DTS.Pipeline: Die Phase 'Überprüfung' beginnt.
Information: 0x40043006 at Datenflusstask, DTS.Pipeline: Die Phase 'Ausführung vorbereiten' beginnt.
Information: 0x40043007 at Datenflusstask, DTS.Pipeline: Die Phase 'Vor der Ausführung' beginnt.
Information: 0x402090DC at Datenflusstask, Flatfileziel [10279]: Die Verarbeitung der Datei 'C:\temp\flatfile_KDstat_Kunden.csv wurde gestartet.
Information: 0x4004300C at Datenflusstask, DTS.Pipeline: Die Phase 'Ausführung' beginnt.
Error: 0xC02090F5 at Datenflusstask, DataReader-Quelle [6976]: 'Komponente 'DataReader-Quelle' (6976)' konnte die Daten nicht verarbeiten.
Error: 0xC0047038 at Datenflusstask, DTS.Pipeline: Die PrimeOutput-Methode in 'Komponente 'DataReader-Quelle' (6976)' hat den Fehlercode 0xC02090F5 zurückgegeben. Die Komponente gab einen Fehlercode zurück, als das Pipelinemodul 'PrimeOutput()' aufgerufen hat. Die Bedeutung des Fehlercodes wird von der Komponente definiert. Der Fehler ist jedoch schwerwiegend, und die Ausführung der Pipeline wurde beendet.
Error: 0xC0047021 at Datenflusstask, DTS.Pipeline: Der Thread 'SourceThread0' wurde mit dem Fehlercode 0xC0047038 beendet.
Error: 0xC0047039 at Datenflusstask, DTS.Pipeline: Der Thread 'WorkThread0' hat ein Signal zum Herunterfahren erhalten und wird beendet. Der Benutzer hat das Herunterfahren angefordert, oder ein Fehler in einem anderen Thread hat dazu geführt, dass die Pipeline heruntergefahren wird.
Error: 0xC0047021 at Datenflusstask, DTS.Pipeline: Der Thread 'WorkThread0' wurde mit dem Fehlercode 0xC0047039 beendet.
Information: 0x40043008 at Datenflusstask, DTS.Pipeline: Die Phase 'Nach der Ausführung' beginnt.
Information: 0x402090DD at Datenflusstask, Flatfileziel [10279]: Die Verarbeitung der Datei 'C:\temp\flatfile_KDstat_Kunden.csv wurde beendet.
Information: 0x40043009 at Datenflusstask, DTS.Pipeline: Die Phase 'Cleanup' beginnt.
Information: 0x4004300B at Datenflusstask, DTS.Pipeline: 'Komponente 'Flatfileziel' (10279)' schrieb 0 Zeilen.
Task failed: Datenflusstask
Warning: 0x80019002 at Package: Die Execution-Methode wurde erfolgreich ausgeführt, aber die Anzahl von ausgelösten Fehlern (5) hat den maximal zulässigen Wert erreicht (1). Deshalb tritt ein Fehler auf. Dieses Problem tritt auf, wenn die Anzahl von Fehlern den in 'MaximumErrorCount' angegebenen Wert erreicht. Ändern Sie den Wert für 'MaximumErrorCount', oder beheben Sie die Fehler.
SSIS package "Package.dtsx" finished: Failure.
[/Quote]
It seems that every SQL-task opens its own connection to the Informix-db. what is bad, because my isolation level is automatically set to "commited" again when a new connection is opened. So I checked the box "retain same connection" at my source connection manager. However, the result is the same. I guess that she SSIS-package opens kind of parallel connections or something.
Ok, so you could say "Why don´t you place your "set isolation to dirty read;"-statement at the beginning of the query in the DataReader source?" The answer is simple: Because the parser throws an error. Don´t ask me why, I don´t know.
Has anyone an idea how to solve that problem?
Thanks in advance!
November 3, 2006 at 8:00 am

This was removed by the editor as SPAM

Viewing 11 posts - 1 through 10 (of 10 total)

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

Benedikt Ries Mr or Mrs. 500 Points: 503 More actions · Answer 1

That´s definitely true

I intend to open a Microsoft support Incident, but I honestly doubt the can solve the problem...

Benedikt Ries Mr or Mrs. 500 Points: 503 More actions · Answer 2

We opened a MS support incident which didn´t give us any positive results so far.

Has anyone experienced a similar problem? I´m glad for every reply.

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 3

i had some trouble with Sybase and my solution was to use an Execute DTS 2000 task to use the ODBC connection. It seems SSIS doesn't want to use it natively.

Benedikt Ries Mr or Mrs. 500 Points: 503 More actions · Answer 4

@SQL Noob: Thanks for your reply.

I also did that:

Creating a DTS-Package and execute it within SSIS. SSIS DOES use ODBC, I can get my data, as long as no user is committed on the INformix-DB table. But that is no clean solution for me.

Actually, now I try to connect via Informix OLE DB driver. I´ll let you know when I come to a solution.

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 5

did you have luck with ODBC on SSIS? I made an ODBC connection but i can't figure out where i can use it. even the wrox book was no help. seems all the sources and destinations don't give the choice to use the ODBC connection

Benedikt Ries Mr or Mrs. 500 Points: 503 More actions · Answer 6

To use you have the ADO.NET-connection, young padawan

You set up a valid ODBC Data source in your ODBC-Administrator. Then, in the BIDS in your SSIS-package, you select "New ADO.NET-connection". Then you select ".NET Providers/ODBC data Provider". Now you should be able to see your ODBC-connection in the drop down-field at "Data source specification".

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 7

can you help a brother out in getting some Informix ODBC drivers? We need to import some data into SQL from Informix and I'm trying to track down the drivers.

Jani Thirumoorthy SSC Journeyman Points: 85 More actions · Answer 8

I know this post is about 4 yrs old. Just wish let you know this is one of the hardest issues (SSIS to Informix connectivity) I have come across. More importantly one of the best solutions that was mentioned (use ADO.Net and connect to ODBC data source) and it worked a treat.

Thanks a lot for your contribution - Benedikt.

Benedikt Ries Mr or Mrs. 500 Points: 503 More actions · Answer 9

You´re welcome!

I´m glad my struggles and the gained knowledge really are useful for someone. Looking back, those months were some of the worst I had considering work :angry: