June 17, 2009 at 1:38 pm
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).
---
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
June 18, 2009 at 12:38 pm
Can you run this in VBSCript from a command prompt and have it work? Or maybe using a scheduled task and the account that IIS is using? I would almost guess it's an ASP/IIS issue if the code works elsewhere.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply