SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Importing data from Informix using ODBC fails!


Importing data from Informix using ODBC fails!

Author
Message
Benedikt Ries
Benedikt Ries
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 2
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):

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.

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!
Benedikt Ries
Benedikt Ries
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 2
That´s definitely true
I intend to open a Microsoft support Incident, but I honestly doubt the can solve the problem...
Benedikt Ries
Benedikt Ries
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 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
alen teplitsky
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2696 Visits: 4666
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
Benedikt Ries
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 2
@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
alen teplitsky
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2696 Visits: 4666
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
Benedikt Ries
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 2
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
alen teplitsky
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2696 Visits: 4666
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
Jani Thirumoorthy
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 107
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
Benedikt Ries
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 2
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
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