Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  Import/Export (DTS) and Replication (2000)  Troubleshooting DTS: SS7 to SS2K

Author  Topic  dpnash Starting Member 1 Post Posted - 2009-06-17 : 17:24:07 Good afternoon, I'm working on an old (classic ASP) application that needs to process SQL Server 7 or MSDE 1.0 databases and copy portions of them to a remote server running SQL Server 2000. I am having trouble getting DTS to actually copy the data. It seems to create the target table just fine, but then fails on the table-copying step. I've used the Visual Basic packages that the DTS wizard creates as a basis for an ASP (VBScript) page to do the transfer. The DTS packages run perfectly. To isolate the problem I've tried to do simpler tasks than the full app at first. Here's a sample VBScript subroutine that is supposed to copy a single small table from one database to another on the same server (login information removed). This particular DTS package attempts to copy between two SQL Server 7 databases, but I have similar problems when copying data to SQL Server 2000: --- Public Sub TestDataLoader() Dim goPackage Set goPackage = Server.CreateObject("DTS.Package2") goPackage.Name = "AETest to AETest_Import" goPackage.Description = "Test intra-DB copy" goPackage.WriteCompletionStatusToNTEventLog = False goPackage.FailOnError = True ' was False goPackage.PackagePriorityClass = 2 goPackage.MaxConcurrentSteps = 4 goPackage.LineageOptions = 0 goPackage.UseTransaction = True goPackage.TransactionIsolationLevel = 4096 goPackage.AutoCommitTransaction = True goPackage.RepositoryMetadataOptions = 0 goPackage.UseOLEDBServiceComponents = True Dim oConnProperty Dim oConnection Set oConnection = goPackage.Connections.New("SQLOLEDB.1") oConnection.ConnectionProperties("Persist Security Info").Value = True oConnection.ConnectionProperties("User ID").Value = "xxx" oConnection.ConnectionProperties("Initial Catalog").Value = "aetest" oConnection.ConnectionProperties("Data Source").Value = "xxx" oConnection.Name = "Connection 1" oConnection.ID = 1 oConnection.Reusable = True oConnection.ConnectImmediate = False oConnection.DataSource = "xxx" oConnection.UserID = "xxx" oConnection.ConnectionTimeout = 60 oConnection.Catalog = "aetest" oConnection.UseTrustedConnection = False oConnection.UseDSL = False goPackage.Connections.Add oConnection Set oConnection = Nothing Set oConnection = goPackage.Connections.New("SQLOLEDB.1") oConnection.ConnectionProperties("Persist Security Info").Value = True oConnection.ConnectionProperties("User ID").Value = "xxx" oConnection.ConnectionProperties("Initial Catalog").Value = "aetest_import" oConnection.ConnectionProperties("Data Source").Value = "xxx" oConnection.Name = "Connection 2" oConnection.ID = 2 oConnection.Reusable = True oConnection.ConnectImmediate = False oConnection.DataSource = "xxx" oConnection.UserID = "xxx" oConnection.ConnectionTimeout = 60 oConnection.Catalog = "aetest_import" oConnection.UseTrustedConnection = False oConnection.UseDSL = False goPackage.Connections.Add oConnection Set oConnection = Nothing Dim oStep 'As DTS.Step2 Dim oPrecConstraint 'as DTS.PrecedenceConstraint Set oStep = goPackage.Steps.New oStep.Name = "Create Table [aetest_import].[dbo].[ACCOUNTCODE_REF] Step" oStep.Description = "Create Table [aetest_import].[dbo].[ACCOUNTCODE_REF] Step" oStep.ExecutionStatus = 1 oStep.TaskName = "Create Table [aetest_import].[dbo].[ACCOUNTCODE_REF] Task" oStep.CommitSuccess = False oStep.RollbackFailure = False oStep.ScriptLanguage = "VBScript" oStep.AddGlobalVariables = True oStep.RelativePriority = 3 oStep.CloseConnection = False oStep.ExecuteInMainThread = False oStep.IsPackageDSORowset = False oStep.JoinTransactionIfPresent = False oStep.DisableStep = False goPackage.Steps.Add oStep Set oStep = Nothing Set oStep = goPackage.Steps.New oStep.Name = "Copy Data from ACCOUNTCODE_REF to [aetest_import].[dbo].[ACCOUNTCODE_REF] Step" oStep.Description = "Copy Data from ACCOUNTCODE_REF to [aetest_import].[dbo].[ACCOUNTCODE_REF] Step" oStep.ExecutionStatus = 1 oStep.TaskName = "Copy Data from ACCOUNTCODE_REF to [aetest_import].[dbo].[ACCOUNTCODE_REF] Task" oStep.CommitSuccess = False oStep.RollbackFailure = False oStep.ScriptLanguage = "VBScript" oStep.AddGlobalVariables = True oStep.RelativePriority = 3 oStep.CloseConnection = False oStep.ExecuteInMainThread = False oStep.IsPackageDSORowset = False oStep.JoinTransactionIfPresent = False oStep.DisableStep = False goPackage.Steps.Add oStep Set oStep = Nothing Set oStep = goPackage.Steps("Copy Data from ACCOUNTCODE_REF to [aetest_import].[dbo].[ACCOUNTCODE_REF] Step") Set oPrecConstraint = oStep.PrecedenceConstraints.New("Create Table [aetest_import].[dbo].[ACCOUNTCODE_REF] Step") oPrecConstraint.StepName = "Create Table [aetest_import].[dbo].[ACCOUNTCODE_REF] Step" oPrecConstraint.PrecedenceBasis = 0 oPrecConstraint.Value = 4 oStep.precedenceConstraints.Add oPrecConstraint Set oPrecConstraint = Nothing Call Task_Sub1( goPackage ) Call Task_Sub2( goPackage ) goPackage.Execute goPackage.Uninitialize set goPackage = Nothing End Sub Public Sub Task_Sub1(ByVal goPackage) Dim oTask Dim oLookup Dim oCustomTask1 Set oTask = goPackage.Tasks.New("DTSExecuteSQLTask") Set oCustomTask1 = oTask.CustomTask oCustomTask1.Name = "Create Table [aetest_import].[dbo].[ACCOUNTCODE_REF] Task" oCustomTask1.Description = "Create Table [aetest_import].[dbo].[ACCOUNTCODE_REF] Task" oCustomTask1.SQLStatement = "CREATE TABLE [aetest_import].[dbo].[ACCOUNTCODE_REF] (" & vbCrLf oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "[AC_ID] smallint NOT NULL, " & vbCrLf oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "[AC_CODE] varchar (20) NOT NULL" & vbCrLf oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & ")" oCustomTask1.ConnectionID = 2 oCustomTask1.CommandTimeout = 0 goPackage.Tasks.Add oTask Set oCustomTask1 = Nothing Set oTask = Nothing End Sub Public Sub Task_Sub2(ByVal goPackage) Dim oTask Dim oLookup Dim oCustomTask2 Set oTask = goPackage.Tasks.New("DTSDataPumpTask") Set oCustomTask2 = oTask.CustomTask oCustomTask2.Name = "Copy Data from ACCOUNTCODE_REF to [aetest_import].[dbo].[ACCOUNTCODE_REF] Task" oCustomTask2.Description = "Copy Data from ACCOUNTCODE_REF to [aetest_import].[dbo].[ACCOUNTCODE_REF] Task" oCustomTask2.SourceConnectionID = 1 oCustomTask2.SourceSQLStatement = "select [AC_ID],[AC_CODE] from [aetest].[dbo].[ACCOUNTCODE_REF]" oCustomTask2.DestinationConnectionID = 2 oCustomTask2.DestinationObjectName = "[aetest_import].[dbo].[ACCOUNTCODE_REF]" oCustomTask2.ProgressRowCount = 1000 oCustomTask2.MaximumErrorCount = 0 oCustomTask2.FetchBufferSize = 1 oCustomTask2.UseFastLoad = True oCustomTask2.InsertCommitSize = 0 oCustomTask2.ExceptionFileColumnDelimiter = "|" oCustomTask2.ExceptionFileRowDelimiter = vbCrLf oCustomTask2.AllowIdentityInserts = False oCustomTask2.FastLoadOptions = 2 Call oCustomTask2_Trans_Sub1( oCustomTask2 ) goPackage.Tasks.Add oTask Set oCustomTask2 = Nothing Set oTask = Nothing End Sub Public Sub oCustomTask2_Trans_Sub1(ByVal oCustomTask2) Dim oTransformation Dim oTransProps Dim oColumn Set oTransformation = oCustomTask2.Transformations.New("DTS.DataPumpTransformCopy") oTransformation.Name = "DirectCopyXform" oTransformation.TransformFlags = 63 oTransformation.ForceSourceBlobsBuffered = 0 oTransformation.ForceBlobsInMemory = False oTransformation.InMemoryBlobSize = 1048576 Set oColumn = oTransformation.SourceColumns.New("AC_ID" , 1) oColumn.Name = "AC_ID" oColumn.Ordinal = 1 oColumn.Flags = 24 oColumn.Size = 0 oColumn.DataType = 2 oColumn.Precision = 0 oColumn.NumericScale = 0 oColumn.Nullable = False oTransformation.SourceColumns.Add oColumn Set oColumn = Nothing Set oColumn = oTransformation.SourceColumns.New("AC_CODE" , 2) oColumn.Name = "AC_CODE" oColumn.Ordinal = 2 oColumn.Flags = 8 oColumn.Size = 20 oColumn.DataType = 129 oColumn.Precision = 0 oColumn.NumericScale = 0 oColumn.Nullable = False oTransformation.SourceColumns.Add oColumn Set oColumn = Nothing Set oColumn = oTransformation.DestinationColumns.New("AC_ID" , 1) oColumn.Name = "AC_ID" oColumn.Ordinal = 1 oColumn.Flags = 24 oColumn.Size = 0 oColumn.DataType = 2 oColumn.Precision = 0 oColumn.NumericScale = 0 oColumn.Nullable = False oTransformation.DestinationColumns.Add oColumn Set oColumn = Nothing Set oColumn = oTransformation.DestinationColumns.New("AC_CODE" , 2) oColumn.Name = "AC_CODE" oColumn.Ordinal = 2 oColumn.Flags = 8 oColumn.Size = 20 oColumn.DataType = 129 oColumn.Precision = 0 oColumn.NumericScale = 0 oColumn.Nullable = False oTransformation.DestinationColumns.Add oColumn Set oColumn = Nothing oCustomTask2.Transformations.Add oTransformation Set oTransformation = Nothing End Sub --- As I mentioned above, the original DTS package to do this task works perfectly fine. However, the ASP page running this minimally-modified code fails at the copying step: Microsoft Data Transformation Services (DTS) Package error '80040428' Package failed because Step 'Copy Data from ACCOUNTCODE_REF to [aetest_import].[dbo].[ACCOUNTCODE_REF] Step' failed. /AE6/includes/MiniDataLoaderOneDB.asp, line 109 -- I can log into the database and confirm there is a new table with the correct column definitions, but it has 0 rows. I haven't been able to use GetExecutionErrorInfo to extract error code info; the script either fails to recognize it as a valid method of the DTS step object, or returns blank values for all the possible values it can return. If anyone could give me any concrete suggestions for what might be going on here, or even to suggest an improved method for moving the data around, I'd appreciate it enormously. Sincerely, David Nash, Sr. Developer, Avotus