SSIS in SQL 2005 and DTS in SQL 2000

Question

SSIS in SQL 2005 and DTS in SQL 2000

larry-807549

Grasshopper

Points: 21
More actions
July 8, 2008 at 9:24 am

#194833

I have a new SQL 2005 Server for MS Dynamics GP and for CRM 4. I cannot or do not want to change any data structures for fear of breaking CRM. Some of the data, however, I would like to export/transform and periodically synchronize to an existing SQL 2000 Server which has been a workhorse as an ODBC datasource for several Access front-ends and more importantly as the datasource for a Cold Fusion website. I also have many DTS packages which work well on the SQL 2000 Server, so I want to retain all of that while simply "pushing" from the new SQL 2005 Server into the old SQL 2000 Server. I cannot create a connection from 2000 to 2005, but I did create a connection from 2005 to 2000. Is the best way to periodically send updated "master" data from 2005 to 2000 by using SSIS, or is some type of replication better?

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

I think the answer depends on how "in sync" the databases need to be. If the data in the SQL 2005 database needs to moved to the SQL 2000 database as soon as possible after a transaction, transactional replication would be appropriate. If lag-time doesn't matter so much, you could create an SSIS package and schedule it to run as often as you need.

Greg

larry-807549 Grasshopper Points: 21 More actions · Answer 2

Thanks, Greg. Some lag time is OK. I could synchronize every 30 minutes or so. For transactional replication, it might be better, but I was not sure if I could do that when I need to transform.

The source data on the SQL 2005 Server, for example, might be a field called Name (nvarch(160)) or AccountNumber (nvarchar(20)) in table dbo.AccountBase (these coming from CRM 4). The destination may be equivalent fields called AlphabeticalName or MemberCode in table dbo.tblMembers on the SQL 2000 Server. I know I will have to modify the destination tables to match the field definitions. Is it possible to set up transactional representation to handle these types of situations?

Example, in CRM 4, a user changes data in table dbo.AccountBase.Name from "Restaurant A" to "Restaurant B". I then need to get that change from the SQL 2005 Server to the SQL 2000 Server in table dbo.tblMembers.AlphabeticalName.

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 3

Michael Earl-395764

SSC Guru

Points: 53873

July 8, 2008 at 11:03 am

#839656

rem

Greg Charles SSC-Forever Points: 45403 More actions · Answer 4

Sorry, I missed your transformation requirement. Unfortunately, transformable subscriptions is a deprecated feature in SQL 2005 replication. That and the fact that some lag time is acceptable, makes SSIS you best choice IMO.

Greg

larry-807549 Grasshopper Points: 21 More actions · Answer 5

Thanks very much, Greg. I'll get my head into SSIS and push the stuff from 2005 to the 2000 SQL Server.

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 6

My post dropped out and I just noticed.

You do have a great deal of control over transactional replication. By default, it creates stored procedures for it's inserts, updates, and deletes. You could create a publication and a subscription and then simply modify the procedures to update the different schema.

This can get complicated, but it would keep your process near-realtime.

You could also use service broker. Nobody likes triggers, but you could create triggers on your source system that add a message to a service broker queue telling it to insert, update, or delete on your "subscriber". This nearly eliminates the problem of triggers being part of the calling transaction and allows you to act on the event of data being modified on your tables. This is sometimes referred to as an asynchronous trigger and I have found it to be a reasonable option if written correctly.