DTS Pack to copy tables from SQL 2000 to SQL 2005

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

Select allOpen in new window

'**********************************************************************
'  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")
	sqlTables = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME NOT IN ('dtproperties') ORDER BY TABLE_NAME"
 
	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
		Next
		For iSteps = 1 to package.Steps.Count
			If package.Steps(iSteps).Name = "step_" & tableName Then
				stepExists = True
				Exit For
			End If
		Next
		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")
		sqlColumns = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='" & tableName & "' ORDER BY ORDINAL_POSITION"
 
		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
			
			rsColumns.MoveNext
		Next
 
		step.Execute
 
		package.Tasks.Remove task.Name
		package.Steps.Remove step.Name
		rsTables.MoveNext
	Next
 
	conTables.Close
 
	Main = DTSTaskExecResult_Success
End Function

Select allOpen in new window