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?".

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, Pennsylvania 19026

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

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.

-----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)