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 12»»

Importing data from Informix using ODBC fails! Expand / Collapse
Author
Message
Posted Tuesday, October 31, 2006 3:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 8:37 AM
Points: 9, 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!
Post #319250
Posted Friday, November 03, 2006 8:00 AM
SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, May 18, 2007 3:36 PM
Points: 10,039, Visits: 1
No one has responded to this topic yet. Even if you don't have a complete answer, the original poster will appreciate any thoughts you have!
Post #320302
Posted Monday, November 06, 2006 7:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 8:37 AM
Points: 9, Visits: 2
That´s definitely true
I intend to open a Microsoft support Incident, but I honestly doubt the can solve the problem...
Post #320625
Posted Wednesday, November 15, 2006 3:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 8:37 AM
Points: 9, 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.
Post #322971
Posted Wednesday, November 15, 2006 7:23 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 03, 2014 12:46 PM
Points: 1,413, Visits: 4,531
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.

https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]
Post #323025
Posted Thursday, November 16, 2006 12:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 8:37 AM
Points: 9, 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.
Post #323336
Posted Thursday, November 16, 2006 7:21 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 03, 2014 12:46 PM
Points: 1,413, Visits: 4,531
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

https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]
Post #323425
Posted Thursday, November 16, 2006 8:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 8:37 AM
Points: 9, 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".
Post #323478
Posted Wednesday, December 06, 2006 9:59 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 03, 2014 12:46 PM
Points: 1,413, Visits: 4,531
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.

https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]
Post #328410
Posted Tuesday, August 10, 2010 8:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 04, 2012 6:50 AM
Points: 21, 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.
Post #966749
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse