I am migrating from one SQL-based help desk system to a competing one. I have
a few hundred tickets to migrate to the new system. What is the proper tool
for migrating data in this case, where the databases are different? For
example, I want to be able to tell the tool that TicketID in tblTickets in
SourceDB maps to ObjectID in tblObject in DestinationDB. Of course, ticket
rosters, actions, departments, etc. complicate it. I need a tool that will
let me redo this a couple of times--once or twice for my trial runs, and once
again for my final migration.

The title "Data Transformation Services" sounds promising, but I understand
that tool has been deprecated.

I am running SQL Server 2005 Express, but I do have a copy of SQL Server
2008 Standard that I was planning to install in a couple of months, but I am
looking for a fast method with a shallow learning curve in order to get this
done in a day or two.

bnick22 (bnick22 (AT) discussions (DOT) microsoft.com) writes:
The successor to DTS is SQL Server Integration Services. This component
does not come with Express, but it comes with Standard.

Whether it is a good tool for the task, I don't know - I've never used
SSIS myself.

I have however worked a great deal of migrating data from competing
system to our system, and we have used nothing but stored procedures.
In one way or another, we have gotten the data into SQL Server tables,
or in files we have been able to bulk load to SQL Server. These migration
projects have been considerable undertakings, as we are talking about
business-critical applications.

What I have found the most difficult task is to fully understand the
meanings of the columns in the source system, the competitor's
documentation being very scarce. This can be no easier when you are
exporting to a foreign system, at least not if you care about the
customer getting its data over correctly.

Once you have the concept mapping, there is a lot of boring coding work
to do. One tip: in this case, don't feel ashamed for running cursors
rather than using set-based. You may encounter all sorts of funny logic,
like if a ticket was opened on a Tuesday afternoon, is should be marked
blue in the target system. After all, that is easier to code with
variables, at least if you are in a hurry. Since the job is only to run
a few times, you can proabably accept execution times on a couple of
hours. In fact, in one migration project I worked, the execution time
for the initial load of transactions was six days!


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

It really sounds like SSIS (for SQL Server 2005 and 2008) and DTS (for SQL
2000) might be a good fit. The good thing about it is that once you create
the SSIS package to transfer your data, you can save it and rerun it. You can
even schedule it to run using SQL Server Agent on regular basis (SQL Server
Agent also does not come with SQL Server Express edition). But just as Erland
Sommarskog has mentioned SSIS does not come with the Express edition.

Just as Erland Sommarskog has mentioned, using stored procedure is also
another alternative. I have done this approach myself as well and has been
worked pretty well.

Some other alternative would be using BCP or Bulk Insert to transfer the
data over. Just some additional tools that you can probably use.

In all of these (SSIS, stored procedures, BCP, or Bulk Insert), knowing the
database tables, relationship and constraints on the source and destination
database would be the key. Other than that expects a lot of trial and error
in the beginning.

By the way, you might want to also ask the vendor of your new helpdesk
system to see if they have develop tools or stored procedures that can be use
to export the data (join their user forums if they exists). Normally
(although not always) they have already thought of that and have develop a
process that you can use to import your existing information into their
system. It might save you from some time and troubles. Since in most cases
aside from getting the data transfer over you have to make sure that the data
complies with the new helpdesk system's application business logic.



"bnick22" wrote: