January 24, 2009 at 8:10 pm
Looking for advice on what technology to use to migrate data from a SQL Server 2005 database to SQL Server 2000. We are currently phasing out our old help desk ticketing system in SQL 2005 to a new ticketing system in SQL 2000. The tables in the databases are not identical because the software is from two different vendors. However, the majority of the tables have similar fields. So far, I've created a spreadsheet that lists which fields will map to each other. There will be some slight data type conversions, but the only one I think I will have trouble with is for the notes field. The notes field in the SQL 2005 dbase is text, so I need to convert it to varchar(2000) in the SQL 2000 dbase. I think I'll be able to get away with simply changing the data type from text to varchar(2000) from the GUI. I did so and did some comparisons on a backup copy of the database and I don't see any difference in the text after doing so. I really only have about 3 tables pertaining to the ticketing system to deal with in the migration. I was thinking about simply using the SQL 2005 export wizard to get the 3 tables and data over to SQL 2000. Suggestions?
January 24, 2009 at 10:23 pm
Pat_B Certainly, u can go for SQL Export Wizard, but just make sure to save SSIS package created by Export Wizard. Export Wizard in one of its steps will ask u whether u want to save ur package. Simply, choose file system or sql server where u want to save ur package.
This will be helpful if Export Wizard (most times in my case) comes up with some exception or error. Generally it might come up with Conversion/DataType Error or Cannot Perform Exception. In that case u can use that package thru BIDS so as to configure that package a little more towards success.
January 25, 2009 at 7:16 am
The export wizard worked well for this application. I did save the SSIS package, but I am having trouble locating it. From your post I gather I use BIDS to open the package. I chose a particular server to save it to and I believe I chose the option to save in the GUI (not to a file). Any idea where I might find the saved package in SQL Mgt Studio?
January 25, 2009 at 2:06 pm
Usually I save on File System where u can specify the Path, but again if u saved it in SQL Server, try opening it up from BIDS. Open new ssis prj, then under packages, right click and add existing package, where it will ask u whether from file system or sql server. Choose SQL Server and it will lead u to the default path.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply