Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


DTS Pack to copy tables from SQL 2000 to SQL 2005

Posted on 2009-05-09
Medium Priority
Last Modified: 2013-11-30
Hello all,

I have a database on a SQL Server 2000 machine with tables that need to be copied to a SQL Server 2005 database on a different machine. Both servers are on separate networks and the way our firewall is configured is such that the 2000 server can see the 2005 server but not the other way around; so it looks like my only option is to initiate the transfer from the 2000 server using DTS instead of SSIS from the 2005 server.

Every table in my 2000 database exists in the 2005 database and both have the exact same schema so there's no concern for crazy column mapping: it's just a straight column-to-column transfer. What I have done thus far is add a Transform Data Task for each table that needs to be copied but as the number of tables grow or shrink it has become bothersome to manage the DTS package.

My question: Is there some way I could dynamically specify which table is to be copied for a single Transform Data Task and then automatically reconfigure the column mappings and execute the task so that I won't have so many tasks to manage? I was hoping to just select a list of tables in an ActiveX Script Task then modify the settings for a single Transform Data Task that exists in the Package. Has anyone faced this issue before? If so, your thoughts?

I have attached a screenshot of my current DTS in which I have one Transform Data Task for each table to be copied.
Question by:tritonmgt
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +3
LVL 31

Expert Comment

by:James Murrell
ID: 24346976
cannot you do as one dts job?

just right click on db in 2005 and import from then just select all tables form 2000
LVL 71

Expert Comment

ID: 24347513
Definitely using the DTS Wizard is the best option, as it constructs everything needed into a single job.
LVL 27

Expert Comment

ID: 24348292
You could also use the OPENROWSET or OPENDATASET functions to do the jon and then all you need to do is to writhe some insert statements:

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

LVL 75

Expert Comment

by:Anthony Perkins
ID: 24348935
And the big question, much as I like DTS and SSIS, why are you using them in this case.  It seems overkill.  Why not just link the servers and do a regular INSERT.  Or am I missing something?
LVL 71

Expert Comment

ID: 24350170
There is (nearly) no difference between INSERT with linked server or with OPENROWSET in this case. However, both require you to loop thru all tables and insert their data. Since there is a sp_msForEachTable, this is no big deal either, as shown with linked server:

exec sp_msforeachtable "insert into ? select * from rmtsrv.db.owner.?"

Open in new window

LVL 75

Expert Comment

by:Anthony Perkins
ID: 24350250
Sure.  But my point was that DTS was not the right approach.
LVL 71

Expert Comment

ID: 24350270
I see, acperkins, I just wanted to add that detail.
Nevertheless, DTS allows for an additional step: check if the table structure is sufficient, i.e. columns are matching, even if the sequence is not the same. To have the same effect with above script, it would be much more complex, as you have to read the columns and put them into a dynamic SQL.
LVL 38

Expert Comment

by:Jim P.
ID: 24350323
More complicated, but another idea: Microsoft SQL Server Database Publishing Wizard 1.1.

I believe it has a command line option to run it from and automate the transfer.


Author Comment

ID: 24350888
Wow! Thank you for all of the suggestions. I know that using DTS is a little much and moreover I would rather update existing records and insert those that do not exist (based off the table's primary key). But transferring the data between the two servers is not my primary goal; I just need the data on the 2005 server for the sake of convenience. Some of the tables literally have over 100 fields: they are imports from our old FilePro system and I am migrating the data to our new, better system (which will reside on the 2005 server). So even though a lot of data will be transferred and many columns, I do not wish to invest a lot of time and effort into the transfer mechanism.
I was, however, able to accomplish my goal with a single ActiveX Script Task containing the code below. It may not be very efficient (I'm not sure) but it gets the job done. If anyone has any suggestions for modifying the code to make it faster I would defintely appreciate it.

'  Visual Basic ActiveX Script
Function Main()
	Dim package, task, step, trans
	Dim dataMigration, db1Migration
	Dim dataSource
	Dim sqlTables
	Dim iTables
	dataSource = "Provider=SQLOLEDB.1;Data Source=myserver;Initial Catalog=Migration;User ID=myuser;Password=mypassword;"
	Set package = DTSGlobalVariables.Parent
	'ADO Objects
	Set conTables = CreateObject("ADODB.Connection")
	Set rsTables = CreateObject("ADODB.Recordset")
	conTables.Open = dataSource
	rsTables.Open sqlTables, conTables, 3
	For iTables = 1 to rsTables.RecordCount
		stepExists = False
		taskExists = False
		tableName = rsTables.Fields("TABLE_NAME").Value
		For iTasks = 1 to package.Tasks.Count
			If package.Tasks(iTasks).Name = "task_" & tableName Then
				taskExists = True
				Exit For
			End If
		For iSteps = 1 to package.Steps.Count
			If package.Steps(iSteps).Name = "step_" & tableName Then
				stepExists = True
				Exit For
			End If
		If stepExists = True Then package.Steps.Remove "step_" & tableName
		If taskExists = True Then package.Tasks.Remove "task_" & tableName
		Set step = package.Steps.New
		step.Name = "step_" & tableName
		step.TaskName = "task_" & tableName
		package.Steps.Add step
		Set pump = package.Tasks.New("DTSDataPumpTask")
		Set task = pump.CustomTask
		task.Name = "task_" & tableName
		package.Tasks.Add pump
		task.SourceConnectionID = 1
		task.DestinationConnectionID = 2
		task.SourceObjectName = tableName
		task.DestinationObjectName = tableName
		Set rsColumns = CreateObject("ADODB.Recordset")
		rsColumns.Open sqlColumns, conTables, 3 'uses existing connection object that is already open
		For iColumns = 1 to rsColumns.RecordCount
			columnName = rsColumns.Fields("COLUMN_NAME").Value
			Set trans = task.Transformations.New("DTS.DataPumpTransformCopy")
			Set colSource = trans.SourceColumns.New(columnName, iColumns)
			Set colDest = trans.DestinationColumns.New(columnName, iColumns)
			trans.Name = "trans_" & tableName & "_" & columnName
			colSource.Name = columnName
			colSource.Ordinal = iColumns
			trans.SourceColumns.Add colSource
			colDest.Name = columnName
			colDest.Ordinal = iColumns
			trans.DestinationColumns.Add colDest
			task.Transformations.Add trans
		package.Tasks.Remove task.Name
		package.Steps.Remove step.Name
	Main = DTSTaskExecResult_Success
End Function

Open in new window

LVL 38

Expert Comment

by:Jim P.
ID: 24350951
>> But transferring the data between the two servers is not my primary
>> goal; I just need the data on the 2005 server for the
>> sake of convenience.

Then a one time thing with the pub wizard may be what you are looking for.

Author Comment

ID: 24350969
Well, I suppose I should have worded that statement differently. As time goes on, more tables will be converted from the FilePro format to a SQL table version that need to be moved to the 2005 server so this package will be ran several times over the course of the next few weeks and the number of tables (and possibly their fields) can and will change.
LVL 71

Accepted Solution

Qlemo earned 2000 total points
ID: 24356001
That is a hard job, and I would rethink that procedure if I were in your shoes. You request for something that keeps track of structural and data changes, and only applies changes. And all that work "for the sake of convenience".

Author Comment

ID: 24356106
You all provide great points. I only meant to say that I would prefer to manage the transfer process in a more efficient manner but I do not wish to spend a great deal of time on this as it is not the most important part of my project. It is merely a means to an end. I can connect to the 2005 server from my laptop (at home) without having to RDC (remote desktop) to the machine but in order for me to interact with the 2000 server I have to use RDC. Like I said, transferring the data is only for convenience so I can run my migration scripts directly from my laptop instead of over remote desktop.
I really appreciate all of your suggestions and input. I would like to revisit this package down the road to make it more efficient; when that time comes, your suggestions will prove very helpful I am sure.
LVL 71

Expert Comment

ID: 24440318
I reckon all is said, so you should close this question now, giving points at your liking.

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question