LONG: How to migrate data from MS-SQL7 to PostgreSQL 7.0
От | Peter Mount |
---|---|
Тема | LONG: How to migrate data from MS-SQL7 to PostgreSQL 7.0 |
Дата | |
Msg-id | 1B3D5E532D18D311861A00600865478C70C616@exchange1.nt.maidstone.gov.uk обсуждение исходный текст |
Список | pgsql-hackers |
This is how to get MS-SQL7 to copy data (either whole tables, or from queries) into PostgreSQL. There are other ways of doing this (like pgaccess), but this describes how to get the SQL server itself to do the job. The key to this is Microsoft's DTS (Data Transformation Services). This is (for once) a useful utility that allows you to transfer & transform data from one source to another. The beauty of it, is that the data can come from any source, to any destination, which is what we will use here. Prerequisites: MS-SQL7.0 running under NT4 Server, SP6APostgreSQL ODBC driver 6.40.00.07 installed on the SQL server.PostgreSQL 7.0 Finalrunning under Linux, SuSE 6.3 Step 1: If it doesn't exist, create the database on the PostgreSQL server. Then on the NT box, create an ODBC Data Source. Set the Database, Server, User Name & Password fields as normal. Under Driver, uncheck ReadOnly, and check Parse Statements and Unknowns as LongVarChar. Under Advanced, clear ReadOnly, but under OID Options, check Show Column and Fake Index. NB: You may not need to do all of the above (other than ReadOnly :) ) but they are the settings that worked for me. Step 2: Open SQL Server Enterprise Manager, and expand the source SQL server. Right click Data Transformation Services, and select New Package. You should be presented with the DTS Package editor. Now, under the Data menu, select Microsoft OLE DB Provider for SQL Server. Select the authentication scheme and select the source database. Back under the Data menu, select Other Connection. Select the Data Source created in Step 1. Step 3: Click once the SQL server icon, then while holding down the Control key, click the PostgreSQL icon. Then under Workflow, select Add Transform. An arrow should appear showing the direction the data will flow. Double click the arrow to show the Transformation properties. Under the Source tab, you have two choices. Either select a table name, in that case you are copying an entire table, or select SQL Query, and enter a select statement to limit or customise the data. Under the Destination tab, select the table you want to copy into. Now this is where we have a problem. If the destination table doesnt exist, DTS has a Create Table button. However, I couldn't get it to create the table correctly. This was because it wraps the table name and the field names within double quotes. Postgresql then creates the table, but it interprets the quotes to mean "don't convert the names to lowercase". Later, when DTS does the copy, it doesn't include the quotes, so postgres duely lowercases the names, and basically it fails. So, when using the Create Table facility, manually lowercase the table and column names before clicking OK to create the table. Also, make sure the column types match. It seems that DTS loves the first column to be an int4, even if the source isn't. Now, under the transformation tab you should see each column from the source (on the left) connected by an arrow to a destination column (on the right). Now, here you can change which one you want it to copy to, or even write a short script to convert, merge a column, etc. Normally you can leave it asis, but the script capability is really useful. Once you are ready, save the package, then click go. You should then see a nice little animation of some cogs mangling your data, and if all's well you should get notification that it's completed. Errors: When errors occur, double click the entry that failed, and it displays the error message. The most common ones are where a transformation failed. Usually this is caused by a wrong data type on the destination table, or if using a script on a column, it's not converting properly. -- Peter Mount Enterprise Support Maidstone Borough Council Any views stated are my own, and not those of Maidstone Borough Council.
В списке pgsql-hackers по дате отправления: