RE: Getting data from IDS 11.70 to a Microsoft SQL
Larry asked for advice on regularly copying data from IDS 11.70 on UNIX into a Microsoft SQL Server database. No single fix was needed; respondents offered options: Microsoft DTS packages (GUI-driven, pulls Informix via ODBC, scheduled, with a few datatype mappings such as datetime/interval to watch), commercial/IBM ETL tools like DbMoto, open-source ETL such as Pentaho, or writing custom scripts in Perl/PHP/Python connecting to both sources (one poster uses Perl with unixODBC for near real-time inserts). Larry was still in the design phase, so no final choice is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I am just sending a ping out to see if anyone has had any experience with getting data from an Informix database on a UNIX server and transferring it to a SQL Server database on a regular basis. I am looking for any tools, advice, problems, etc. that anyone might have. Larry
MS DTS package works just fine -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of LARRY SORENSEN Sent: Wednesday, February 23, 2011 11:42 AM To: ids@iiug.org Subject: RE: Getting data from IDS 11.70 to a Microsoft.... [22871] I am just sending a ping out to see if anyone has had any experience with getting data from an Informix database on a UNIX server and transferring it to a SQL Server database on a regular basis. I am looking for any tools, advice, problems, etc. that anyone might have. Larry **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. _____ avast! Antivirus <http://www.avast.com>; : Outbound message clean. Virus Database (VPS): 110223-0, 02/23/2011 Tested on: 2/23/2011 11:45:27 AM avast! - copyright (c) 1988-2011 ALWIL Software.
Paul, Can you elaborate a little more on what that involves and how it would work? Larry > To: ids@iiug.org > From: paul@oninit.com > Subject: RE: Getting data from IDS 11.70 to a Microsoft.... [22872] > Date: Wed, 23 Feb 2011 12:45:42 -0500 > > MS DTS package works just fine > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of LARRY > SORENSEN > Sent: Wednesday, February 23, 2011 11:42 AM > To: ids@iiug.org > Subject: RE: Getting data from IDS 11.70 to a Microsoft.... [22871] > > I am just sending a ping out to see if anyone has had any experience with > getting data from an Informix database on a UNIX server and transferring it > to > a SQL Server database on a regular basis. I am looking for any tools, > advice, > problems, etc. that anyone might have. > > Larry > > **************************************************************************** > *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > _____ > > avast! Antivirus <http://www.avast.com>; : Outbound message clean. > > Virus Database (VPS): 110223-0, 02/23/2011 > Tested on: 2/23/2011 11:45:27 AM > avast! - copyright (c) 1988-2011 ALWIL Software. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
AFAIR it's a gui based transformation thing that takes informix via ODBC and transforms/inserts/updates the MS SQLslower spreadsheet. There are a couple of data types that you might need to map, all are datatime, interval I believe. Then you can just scheduled it to run whenever you want to. I believe it's free -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of LARRY SORENSEN Sent: Wednesday, February 23, 2011 11:52 AM To: ids@iiug.org Subject: RE: Getting data from IDS 11.70 to a Microsoft.... [22873] Paul, Can you elaborate a little more on what that involves and how it would work? Larry > To: ids@iiug.org > From: paul@oninit.com > Subject: RE: Getting data from IDS 11.70 to a Microsoft.... [22872] > Date: Wed, 23 Feb 2011 12:45:42 -0500 > > MS DTS package works just fine > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of LARRY > SORENSEN > Sent: Wednesday, February 23, 2011 11:42 AM > To: ids@iiug.org > Subject: RE: Getting data from IDS 11.70 to a Microsoft.... [22871] > > I am just sending a ping out to see if anyone has had any experience with > getting data from an Informix database on a UNIX server and transferring it > to > a SQL Server database on a regular basis. I am looking for any tools, > advice, > problems, etc. that anyone might have. > > Larry > > **************************************************************************** > *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > _____ > > avast! Antivirus <http://www.avast.com>; : Outbound message clean. > > Virus Database (VPS): 110223-0, 02/23/2011 > Tested on: 2/23/2011 11:45:27 AM > avast! - copyright (c) 1988-2011 ALWIL Software. > > > **************************************************************************** *** > Forum Note: Use "Reply" to post a response in the discussion forum. > **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. _____ avast! Antivirus <http://www.avast.com>; : Outbound message clean. Virus Database (VPS): 110223-0, 02/23/2011 Tested on: 2/23/2011 11:57:18 AM avast! - copyright (c) 1988-2011 ALWIL Software.
There are several tools out there. DbMoto is one, IBM's ETL tools also can do this. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Feb 23, 2011 at 12:41 PM, LARRY SORENSEN <lsorensen25@msn.com>wrote: > I am just sending a ping out to see if anyone has had any experience with > getting data from an Informix database on a UNIX server and transferring it > to > a SQL Server database on a regular basis. I am looking for any tools, > advice, > problems, etc. that anyone might have. > > Larry > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0015175113e63a854e049cf6d527
Oops, that got away from me. You can also use most of the open source ETL packages like Pentaho. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Feb 23, 2011 at 12:57 PM, Art Kagel <art.kagel@gmail.com> wrote: > There are several tools out there. DbMoto is one, IBM's ETL tools also can > do this. > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > IIUG Board of Directors (art@iiug.org) > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any > other organization with which I am associated either explicitly, implicitly, > or by inference. Neither do those opinions reflect those of other > individuals affiliated with any entity with which I am affiliated nor those > of the entities themselves. > > > > > On Wed, Feb 23, 2011 at 12:41 PM, LARRY SORENSEN <lsorensen25@msn.com>wrote: > >> I am just sending a ping out to see if anyone has had any experience with >> getting data from an Informix database on a UNIX server and transferring >> it to >> a SQL Server database on a regular basis. I am looking for any tools, >> advice, >> problems, etc. that anyone might have. >> >> Larry >> >> >> >> ******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> > --0015175113e6ca05e9049cf6d7ce
Thank you for the suggestions. > To: ids@iiug.org > From: art.kagel@gmail.com > Subject: Re: Getting data from IDS 11.70 to a Microsoft.... [22876] > Date: Wed, 23 Feb 2011 12:59:24 -0500 > > Oops, that got away from me. You can also use most of the open source ETL > packages like Pentaho. > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > IIUG Board of Directors (art@iiug.org) > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions and > do not reflect on my employer, Advanced DataTools, the IIUG, nor any other > organization with which I am associated either explicitly, implicitly, or by > inference. Neither do those opinions reflect those of other individuals > affiliated with any entity with which I am affiliated nor those of the > entities themselves. > > On Wed, Feb 23, 2011 at 12:57 PM, Art Kagel <art.kagel@gmail.com> wrote: > > > There are several tools out there. DbMoto is one, IBM's ETL tools also can > > do this. > > > > Art > > > > Art S. Kagel > > Advanced DataTools (www.advancedatatools.com) > > IIUG Board of Directors (art@iiug.org) > > Blog: http://informix-myview.blogspot.com/ > > > > Disclaimer: Please keep in mind that my own opinions are my own opinions > > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any > > other organization with which I am associated either explicitly, implicitly, > > or by inference. Neither do those opinions reflect those of other > > individuals affiliated with any entity with which I am affiliated nor those > > of the entities themselves. > > > > > > > > > > On Wed, Feb 23, 2011 at 12:41 PM, LARRY SORENSEN <lsorensen25@msn.com>wrote: > > > >> I am just sending a ping out to see if anyone has had any experience with > >> getting data from an Informix database on a UNIX server and transferring > >> it to > >> a SQL Server database on a regular basis. I am looking for any tools, > >> advice, > >> problems, etc. that anyone might have. > >> > >> Larry > >> > >> > >> > >> > ******************************************************************************* > >> Forum Note: Use "Reply" to post a response in the discussion forum. > >> > >> > > > > --0015175113e6ca05e9049cf6d7ce > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
You could always write something to copy the data using anything that can connect to both data sources at once. Perl, PHP, Python all could do this quite handily. -D On Feb 23, 2011, at 12:41 PM, LARRY SORENSEN wrote: > I am just sending a ping out to see if anyone has had any experience with > getting data from an Informix database on a UNIX server and transferring it to > a SQL Server database on a regular basis. I am looking for any tools, advice, > problems, etc. that anyone might have. > > Larry > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
Larry, Can you elaborate on how you want this interface to work? I have multiple apps that need to transfer data from Informix to M$Sql Server and have never had an issue with it -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of LARRY SORENSEN Sent: 23 February 2011 18:20 To: ids@iiug.org Subject: RE: Getting data from IDS 11.70 to a Microsoft.... [22877] Thank you for the suggestions. > To: ids@iiug.org > From: art.kagel@gmail.com > Subject: Re: Getting data from IDS 11.70 to a Microsoft.... [22876] > Date: Wed, 23 Feb 2011 12:59:24 -0500 > > Oops, that got away from me. You can also use most of the open source > ETL packages like Pentaho. > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors > (art@iiug.org) > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own > opinions and do not reflect on my employer, Advanced DataTools, the > IIUG, nor any other organization with which I am associated either > explicitly, implicitly, or by inference. Neither do those opinions > reflect those of other individuals affiliated with any entity with > which I am affiliated nor those of the entities themselves. > > On Wed, Feb 23, 2011 at 12:57 PM, Art Kagel <art.kagel@gmail.com> wrote: > > > There are several tools out there. DbMoto is one, IBM's ETL tools > > also can do this. > > > > Art > > > > Art S. Kagel > > Advanced DataTools (www.advancedatatools.com) IIUG Board of > > Directors (art@iiug.org) > > Blog: http://informix-myview.blogspot.com/ > > > > Disclaimer: Please keep in mind that my own opinions are my own > > opinions and do not reflect on my employer, Advanced DataTools, the > > IIUG, nor any other organization with which I am associated either > > explicitly, implicitly, > > or by inference. Neither do those opinions reflect those of other > > individuals affiliated with any entity with which I am affiliated > > nor those > > of the entities themselves. > > > > > > > > > > On Wed, Feb 23, 2011 at 12:41 PM, LARRY SORENSEN <lsorensen25@msn.com>wrote: > > > >> I am just sending a ping out to see if anyone has had any > >> experience with getting data from an Informix database on a UNIX > >> server and transferring it to a SQL Server database on a regular > >> basis. I am looking for any tools, advice, problems, etc. that > >> anyone might have. > >> > >> Larry > >> > >> > >> > >> > ************************************************************************ ******* > >> Forum Note: Use "Reply" to post a response in the discussion forum. > >> > >> > > > > --0015175113e6ca05e9049cf6d7ce > > > ************************************************************************ ******* > Forum Note: Use "Reply" to post a response in the discussion forum. > ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. ________________________________________________________________________ This email has been scanned for all viruses by the MessageLabs Email Security System before entering the GFM Network. If you have any queries please contact Systems Administration or IT. ________________________________________________________________________ ______________________________________________________________________ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email ______________________________________________________________________
We are still in the design phase, but basically we want to be able to copy data from how ever many tables we determine in the planning phase, and copy it to a SQL Server database on a regular basis. That is one of the reasons why I am getting input. I need to see what is feasible and how labor intensive it is going to be. Larry > To: ids@iiug.org > From: noel@gfm.co.uk > Subject: RE: Getting data from IDS 11.70 to a Microsoft.... [22881] > Date: Wed, 23 Feb 2011 17:04:40 -0500 > > Larry, > > Can you elaborate on how you want this interface to work? I have > multiple apps that need to transfer data from Informix to M$Sql Server > and have never had an issue with it > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > LARRY SORENSEN > Sent: 23 February 2011 18:20 > To: ids@iiug.org > Subject: RE: Getting data from IDS 11.70 to a Microsoft.... [22877] > > Thank you for the suggestions. > > > To: ids@iiug.org > > From: art.kagel@gmail.com > > Subject: Re: Getting data from IDS 11.70 to a Microsoft.... [22876] > > Date: Wed, 23 Feb 2011 12:59:24 -0500 > > > > Oops, that got away from me. You can also use most of the open source > > ETL packages like Pentaho. > > > > Art > > > > Art S. Kagel > > Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors > > (art@iiug.org) > > Blog: http://informix-myview.blogspot.com/ > > > > Disclaimer: Please keep in mind that my own opinions are my own > > opinions and do not reflect on my employer, Advanced DataTools, the > > IIUG, nor any other organization with which I am associated either > > explicitly, implicitly, or by inference. Neither do those opinions > > reflect those of other individuals affiliated with any entity with > > which I am affiliated nor those of the entities themselves. > > > > On Wed, Feb 23, 2011 at 12:57 PM, Art Kagel <art.kagel@gmail.com> > wrote: > > > > > There are several tools out there. DbMoto is one, IBM's ETL tools > > > also can do this. > > > > > > Art > > > > > > Art S. Kagel > > > Advanced DataTools (www.advancedatatools.com) IIUG Board of > > > Directors (art@iiug.org) > > > Blog: http://informix-myview.blogspot.com/ > > > > > > Disclaimer: Please keep in mind that my own opinions are my own > > > opinions and do not reflect on my employer, Advanced DataTools, the > > > IIUG, nor any other organization with which I am associated either > > > explicitly, > implicitly, > > > or by inference. Neither do those opinions reflect those of other > > > individuals affiliated with any entity with which I am affiliated > > > nor > those > > > of the entities themselves. > > > > > > > > > > > > > > > On Wed, Feb 23, 2011 at 12:41 PM, LARRY SORENSEN > <lsorensen25@msn.com>wrote: > > > > > >> I am just sending a ping out to see if anyone has had any > > >> experience with getting data from an Informix database on a UNIX > > >> server and transferring it to a SQL Server database on a regular > > >> basis. I am looking for any tools, advice, problems, etc. that > > >> anyone might have. > > >> > > >> Larry > > >> > > >> > > >> > > >> > > > ************************************************************************ > ******* > > >> Forum Note: Use "Reply" to post a response in the discussion forum. > > > >> > > >> > > > > > > > --0015175113e6ca05e9049cf6d7ce > > > > > > > ************************************************************************ > ******* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ************************************************************************ > ******* > Forum Note: Use "Reply" to post a response in the discussion forum. > > ________________________________________________________________________ > This email has been scanned for all viruses by the MessageLabs Email > Security System before entering the GFM Network. If you have any > queries please contact Systems Administration or IT. > ________________________________________________________________________ > > ______________________________________________________________________ > This email has been scanned by the MessageLabs Email Security System. > For more information please visit http://www.messagelabs.com/email > ______________________________________________________________________ > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
Our apps usually involve a http request being recorded into an informix database, off the back of which a record is inserted (using perl and UNIXODBC) into MSSQL, the requests could be transferred using cron but our apps requires real time(ish) responses. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of LARRY SORENSEN Sent: 23 February 2011 22:13 To: ids@iiug.org Subject: RE: Getting data from IDS 11.70 to a Microsoft.... [22882] We are still in the design phase, but basically we want to be able to copy data from how ever many tables we determine in the planning phase, and copy it to a SQL Server database on a regular basis. That is one of the reasons why I am getting input. I need to see what is feasible and how labor intensive it is going to be. Larry > To: ids@iiug.org > From: noel@gfm.co.uk > Subject: RE: Getting data from IDS 11.70 to a Microsoft.... [22881] > Date: Wed, 23 Feb 2011 17:04:40 -0500 > > Larry, > > Can you elaborate on how you want this interface to work? I have > multiple apps that need to transfer data from Informix to M$Sql Server > and have never had an issue with it > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > LARRY SORENSEN > Sent: 23 February 2011 18:20 > To: ids@iiug.org > Subject: RE: Getting data from IDS 11.70 to a Microsoft.... [22877] > > Thank you for the suggestions. > > > To: ids@iiug.org > > From: art.kagel@gmail.com > > Subject: Re: Getting data from IDS 11.70 to a Microsoft.... [22876] > > Date: Wed, 23 Feb 2011 12:59:24 -0500 > > > > Oops, that got away from me. You can also use most of the open > > source ETL packages like Pentaho. > > > > Art > > > > Art S. Kagel > > Advanced DataTools (www.advancedatatools.com) IIUG Board of > > Directors > > (art@iiug.org) > > Blog: http://informix-myview.blogspot.com/ > > > > Disclaimer: Please keep in mind that my own opinions are my own > > opinions and do not reflect on my employer, Advanced DataTools, the > > IIUG, nor any other organization with which I am associated either > > explicitly, implicitly, or by inference. Neither do those opinions > > reflect those of other individuals affiliated with any entity with > > which I am affiliated nor those of the entities themselves. > > > > On Wed, Feb 23, 2011 at 12:57 PM, Art Kagel <art.kagel@gmail.com> > wrote: > > > > > There are several tools out there. DbMoto is one, IBM's ETL tools > > > also can do this. > > > > > > Art > > > > > > Art S. Kagel > > > Advanced DataTools (www.advancedatatools.com) IIUG Board of > > > Directors (art@iiug.org) > > > Blog: http://informix-myview.blogspot.com/ > > > > > > Disclaimer: Please keep in mind that my own opinions are my own > > > opinions and do not reflect on my employer, Advanced DataTools, > > > the IIUG, nor any other organization with which I am associated > > > either explicitly, > implicitly, > > > or by inference. Neither do those opinions reflect those of other > > > individuals affiliated with any entity with which I am affiliated > > > nor > those > > > of the entities themselves. > > > > > > > > > > > > > > > On Wed, Feb 23, 2011 at 12:41 PM, LARRY SORENSEN > <lsorensen25@msn.com>wrote: > > > > > >> I am just sending a ping out to see if anyone has had any > > >> experience with getting data from an Informix database on a UNIX > > >> server and transferring it to a SQL Server database on a regular > > >> basis. I am looking for any tools, advice, problems, etc. that > > >> anyone might have. > > >> > > >> Larry > > >> > > >> > > >> > > >> > > > ********************************************************************** > ** > ******* > > >> Forum Note: Use "Reply" to post a response in the discussion forum. > > > >> > > >> > > > > > > > --0015175113e6ca05e9049cf6d7ce > > > > > > > ********************************************************************** > ** > ******* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ********************************************************************** > ** > ******* > Forum Note: Use "Reply" to post a response in the discussion forum. > > ______________________________________________________________________ > __ This email has been scanned for all viruses by the MessageLabs > Email Security System before entering the GFM Network. If you have any > queries please contact Systems Administration or IT. > ______________________________________________________________________ > __ > > ______________________________________________________________________ > This email has been scanned by the MessageLabs Email Security System. > For more information please visit http://www.messagelabs.com/email > ______________________________________________________________________ > > > ************************************************************************ ******* > Forum Note: Use "Reply" to post a response in the discussion forum. > ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. ________________________________________________________________________ This email has been scanned for all viruses by the MessageLabs Email Security System before entering the GFM Network. If you have any queries please contact Systems Administration or IT. ________________________________________________________________________ ______________________________________________________________________ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email ______________________________________________________________________