Thread: MSSQL7 & PostgreSQL 7.0
Just been playing with SQL Server 7's DTS (Data Transform Service) and thought, "Could SQL load data into Postgres?". The main idea (ok excuse as I didn't fancy doing much work this afternoon), was publishing data on our Intranet (which uses Postgres). Anyhow, loaded the ODBC driver onto the SQL server, played and it only works. I'll write up how to do it tonight (as there are a few gotcha's), but it's one for the docs under "How do I migrate data from SQL7 to PostgreSQL?". Peter -- Peter Mount Enterprise Support Maidstone Borough Council Any views stated are my own, and not those of Maidstone Borough Council.
Yes, major issue. > Just been playing with SQL Server 7's DTS (Data Transform Service) and > thought, "Could SQL load data into Postgres?". > > The main idea (ok excuse as I didn't fancy doing much work this > afternoon), was publishing data on our Intranet (which uses Postgres). > > Anyhow, loaded the ODBC driver onto the SQL server, played and it only > works. I'll write up how to do it tonight (as there are a few gotcha's), > but it's one for the docs under "How do I migrate data from SQL7 to > PostgreSQL?". > > Peter > > -- > Peter Mount > Enterprise Support > Maidstone Borough Council > Any views stated are my own, and not those of Maidstone Borough Council. > > > -- Bruce Momjian | http://www.op.net/~candle pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
> -----Original Message----- > From: Bruce Momjian [mailto:pgman@candle.pha.pa.us] > Sent: 17 May 2000 16:45 > To: Peter Mount > Cc: PostgreSQL Developers List (E-mail) > Subject: Re: [HACKERS] MSSQL7 & PostgreSQL 7.0 > > > Yes, major issue. > > > Just been playing with SQL Server 7's DTS (Data Transform > Service) and > > thought, "Could SQL load data into Postgres?". > > > > The main idea (ok excuse as I didn't fancy doing much work this > > afternoon), was publishing data on our Intranet (which uses > Postgres). > > > > Anyhow, loaded the ODBC driver onto the SQL server, played > and it only > > works. I'll write up how to do it tonight (as there are a > few gotcha's), > > but it's one for the docs under "How do I migrate data from SQL7 to > > PostgreSQL?". > > Feel free to tell me where to go if you think this is a shameless plug(!) but pgAdmin also includes tools for migrating data from any ODBC datasource (as well as ascii text files) to PostgreSQL. I'm open to suggestions if anyone feels there are improvements worth making.... Regards, Dave. -- "If you stand still, sooner or later something will eat you." - James Burke http://www.vale-housing.co.uk/ (Work) http://www.pgadmin.freeserve.co.uk/ (Home of pgAdmin)
I don't think it's a shameless plug at all. However, I think it's sometimes useful to know alternative methods of doing things, especially when you have to work for people who think Microsoft are the best thing since sliced bread ;-) Peter -- Peter Mount Enterprise Support Maidstone Borough Council Any views stated are my own, and not those of Maidstone Borough Council. -----Original Message----- From: Dave Page [mailto:dpage@vale-housing.co.uk] Sent: Thursday, May 18, 2000 9:34 AM To: 'pgsql-hackers@postgresql.org' Subject: RE: [HACKERS] MSSQL7 & PostgreSQL 7.0 Feel free to tell me where to go if you think this is a shameless plug(!) but pgAdmin also includes tools for migrating data from any ODBC datasource (as well as ascii text files) to PostgreSQL. I'm open to suggestions if anyone feels there are improvements worth making.... Regards, Dave. -- "If you stand still, sooner or later something will eat you." - James Burke http://www.vale-housing.co.uk/ (Work) http://www.pgadmin.freeserve.co.uk/ (Home of pgAdmin)
Peter Mount wrote: > > I don't think it's a shameless plug at all. > > However, I think it's sometimes useful to know alternative methods of > doing things, especially when you have to work for people who think > Microsoft are the best thing since sliced bread ;-) > > Peter > > -- > Peter Mount > Enterprise Support > Maidstone Borough Council > Any views stated are my own, and not those of Maidstone Borough Council. > > -----Original Message----- > From: Dave Page [mailto:dpage@vale-housing.co.uk] > Sent: Thursday, May 18, 2000 9:34 AM > To: 'pgsql-hackers@postgresql.org' > Subject: RE: [HACKERS] MSSQL7 & PostgreSQL 7.0 > > Feel free to tell me where to go if you think this is a shameless > plug(!) > but pgAdmin also includes tools for migrating data from any ODBC > datasource > (as well as ascii text files) to PostgreSQL. I'm open to suggestions if > anyone feels there are improvements worth making.... Firstly I have to say its a great bit of work (pgAdmin), however it'd be nice to see an open source tool mirroring the functionality of the DTS. I especially liked the way you could script the mappings between fields, as well as the GUI builder for complex transforms (in fact I absolutely loved that (when it worked :), I had too long on the beta). As well as being able to use pre- and post- conditional actions upon success/failure of certain parts, like emailing, calling other programs etc. Regards, Joe > Regards, > > Dave. > > -- > "If you stand still, sooner or later something will eat you." > - James Burke > http://www.vale-housing.co.uk/ (Work) > http://www.pgadmin.freeserve.co.uk/ (Home of pgAdmin) -- Joe Shevland Principal Consultant KPI Logistics Pty Ltd http://www.kpi.com.au mailto:shevlandj@kpi.com.au
> -----Original Message-----
> From: Joe Shevland [mailto:shevlandj@kpi.com.au]
> Sent: 18 May 2000 09:51
> To: Peter Mount
> Cc: 'Dave Page'; 'pgsql-hackers@postgresql.org'
> Subject: Re: [HACKERS] MSSQL7 & PostgreSQL 7.0
>
> > (as well as ascii text files) to PostgreSQL. I'm open to
> suggestions if
> > anyone feels there are improvements worth making....
>
> Firstly I have to say its a great bit of work (pgAdmin),
Thanks...
> however it'd be
> nice to see an open source tool mirroring the functionality
> of the DTS. I
> especially liked the way you could script the mappings
> between fields, as
> well as the GUI builder for complex transforms (in fact I
> absolutely loved
> that (when it worked :), I had too long on the beta). As well
> as being able
> to use pre- and post- conditional actions upon
> success/failure of certain
> parts, like emailing, calling other programs etc.
Thats an interesting idea. What exactly do you script though? Things like
(pseudo-code of course):
If SOURCETYPE = "AutoNumber" Then DESTTYPE = "int4" DESTDEFAULT = "nextval('record_id')" EXECSQL "CREATE SEQUENCE
record_id"
End If
or have I got the wrong end of the stick completely?
Regards,
Dave.
--
"If you stand still, sooner or later something will eat you." - James Burke
http://www.vale-housing.co.uk/ (Work)
http://www.pgadmin.freeserve.co.uk/ (Home of pgAdmin)
As usual when replying from here, replies prefixed with PM:
--
Peter Mount
Enterprise Support
Maidstone Borough Council
Any views stated are my own, and not those of Maidstone Borough Council.
> > (as well as ascii text files) to PostgreSQL. I'm open to
> suggestions if
> > anyone feels there are improvements worth making....
>
> Firstly I have to say its a great bit of work (pgAdmin),
Thanks...
> however it'd be
> nice to see an open source tool mirroring the functionality
> of the DTS. I
> especially liked the way you could script the mappings
> between fields, as
> well as the GUI builder for complex transforms (in fact I
> absolutely loved
> that (when it worked :), I had too long on the beta). As well
> as being able
> to use pre- and post- conditional actions upon
> success/failure of certain
> parts, like emailing, calling other programs etc.
Thats an interesting idea. What exactly do you script though? Things
like
(pseudo-code of course):
If SOURCETYPE = "AutoNumber" Then DESTTYPE = "int4" DESTDEFAULT = "nextval('record_id')" EXECSQL "CREATE SEQUENCE
record_id"
End If
or have I got the wrong end of the stick completely?
PM: -------------
The scripting is used to convert one or more columns in the source into
a single column in the destination. You have one for each column.
ie:
Source Destination
id ---copy---> id
date -\________\ datetime
time -/
name ---copy---> name
The copy bits aren't scripted but the middle one is.
Here, there's a script handling the conversion of date & time into a
single value.
I have a case of this here, where I use DTS to run dumpacl on our domain
controllers to return the security logs, and pipes the output through a
transform into a table. NT Events have two fields for the date, but I
need one, so the script for the above case is:
Function Main()DTSDestination("datetime") = DTSSource("date")+"
"+DTSSource("time")Main = DTSTransformStat_OK
End Function
Now that was a little bit of VB Script, but it could easily be Perl,
Javascript etc.