May 15, 2002 at 4:45 am
Hi,
I need to export tables from a SQL Server 7 db into an access db (creating the tables as we go). Using the DTS wizard, I Get an error saying that I do not have permissions on the object (table), although I am logged in as SA. What do I need to do to allow this?
Thanks
R
May 15, 2002 at 5:30 am
Can you post your steps. I get no issue with doing this in any of my test servers using the SA account. Also what is the exact text of the error message?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 15, 2002 at 5:44 am
Ok,
As I said, I am using the wizard, so here is the VB generted code (sorry there is so much). Perhaps it is an Access permission thing? I am using the Admin user account.
Option Explicit
Public goPackageOld As New DTS.Package
Public goPackage As DTS.Package2
Private Sub Main()
Set goPackage = goPackageOld
goPackage.Name = "New Package"
goPackage.Description = "DTS package description"
goPackage.WriteCompletionStatusToNTEventLog = False
goPackage.FailOnError = 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
goPackage.LogToSQLServer = False
goPackage.LogServerFlags = 0
goPackage.FailPackageOnLogFailure = False
goPackage.ExplicitGlobalVariables = False
goPackage.PackageType = 0
Dim oConnProperty As DTS.OleDBProperty
'---------------------------------------------------------------------------
' create package connection information
'---------------------------------------------------------------------------
Dim oConnection As DTS.Connection2
'------------- a new connection defined below.
'For security purposes, the password is never scripted
Set oConnection = goPackage.Connections.New("SQLOLEDB")
oConnection.ConnectionProperties("Persist Security Info") = True
oConnection.ConnectionProperties("User ID") = "sa"
oConnection.ConnectionProperties("Initial Catalog") = "myDB"
oConnection.ConnectionProperties("Data Source") = "DEV1"
oConnection.ConnectionProperties("Application Name") = "DTS Import/Export Wizard"
oConnection.Name = "Connection 1"
oConnection.ID = 1
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "DEV1"
oConnection.UserID = "sa"
oConnection.ConnectionTimeout = 60
oConnection.Catalog = "myDB"
oConnection.UseTrustedConnection = False
oConnection.UseDSL = False
'If you have a password for this connection, please uncomment and add your password below.
'oConnection.Password = "<put the password here>"
goPackage.Connections.Add oConnection
Set oConnection = Nothing
'------------- a new connection defined below.
'For security purposes, the password is never scripted
Set oConnection = goPackage.Connections.New("Microsoft.Jet.OLEDB.4.0")
oConnection.ConnectionProperties("User ID") = "admin"
oConnection.ConnectionProperties("Data Source") = "C:\testexport.mdb"
oConnection.ConnectionProperties("Mode") = 3
oConnection.Name = "Connection 2"
oConnection.ID = 2
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "C:\testexport.mdb"
oConnection.UserID = "admin"
oConnection.ConnectionTimeout = 60
oConnection.UseTrustedConnection = False
oConnection.UseDSL = False
'If you have a password for this connection, please uncomment and add your password below.
'oConnection.Password = "<put the password here>"
goPackage.Connections.Add oConnection
Set oConnection = Nothing
'---------------------------------------------------------------------------
' create package steps information
'---------------------------------------------------------------------------
Dim oStep As DTS.Step2
Dim oPrecConstraint As DTS.PrecedenceConstraint
'------------- a new step defined below
Set oStep = goPackage.Steps.New
oStep.Name = "Create Table Asset Step"
oStep.Description = "Create Table Asset Step"
oStep.ExecutionStatus = 1
oStep.TaskName = "Create Table Asset 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
oStep.FailPackageOnError = False
goPackage.Steps.Add oStep
Set oStep = Nothing
'------------- a new step defined below
Set oStep = goPackage.Steps.New
oStep.Name = "Copy Data from Asset to Asset Step"
oStep.Description = "Copy Data from Asset to Asset Step"
oStep.ExecutionStatus = 1
oStep.TaskName = "Copy Data from Asset to Asset Task"
oStep.CommitSuccess = False
oStep.RollbackFailure = False
oStep.ScriptLanguage = "VBScript"
oStep.AddGlobalVariables = True
oStep.RelativePriority = 3
oStep.CloseConnection = False
oStep.ExecuteInMainThread = True
oStep.IsPackageDSORowset = False
oStep.JoinTransactionIfPresent = False
oStep.DisableStep = False
oStep.FailPackageOnError = False
goPackage.Steps.Add oStep
Set oStep = Nothing
'------------- a precedence constraint for steps defined below
Set oStep = goPackage.Steps("Copy Data from Asset to Asset Step")
Set oPrecConstraint = oStep.precedenceConstraints.New("Create Table Asset Step")
oPrecConstraint.StepName = "Create Table Asset Step"
oPrecConstraint.PrecedenceBasis = 0
oPrecConstraint.Value = 4
oStep.precedenceConstraints.Add oPrecConstraint
Set oPrecConstraint = Nothing
'---------------------------------------------------------------------------
' create package tasks information
'---------------------------------------------------------------------------
'------------- call Task_Sub1 for task Create Table Asset Task (Create Table Asset Task)
Call Task_Sub1(goPackage)
'------------- call Task_Sub2 for task Copy Data from Asset to Asset Task (Copy Data from Asset to Asset Task)
Call Task_Sub2(goPackage)
'---------------------------------------------------------------------------
' Save or execute package
'---------------------------------------------------------------------------
'goPackage.SaveToSQLServer "(local)", "sa", ""
goPackage.Execute
goPackage.Uninitialize
'to save a package instead of executing it, comment out the executing package line above and uncomment the saving package line
Set goPackage = Nothing
Set goPackageOld = Nothing
End Sub
'------------- define Task_Sub1 for task Create Table Asset Task (Create Table Asset Task)
Public Sub Task_Sub1(ByVal goPackage As Object)
Dim oTask As DTS.Task
Dim oLookup As DTS.Lookup
Dim oCustomTask1 As DTS.ExecuteSQLTask2
Set oTask = goPackage.Tasks.New("DTSExecuteSQLTask")
Set oCustomTask1 = oTask.CustomTask
oCustomTask1.Name = "Create Table Asset Task"
oCustomTask1.Description = "Create Table Asset Task"
oCustomTask1.SQLStatement = "CREATE TABLE `Asset` (" & vbCrLf
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "`AssetID` VarChar (16) NOT NULL, " & vbCrLf
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "`LevelNo` Short NULL, " & vbCrLf
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "`UserTag` VarChar (18) NULL, " & vbCrLf
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "`AssetText` VarChar (80) NULL, " & vbCrLf
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "`Context` LongText NULL, " & vbCrLf
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "`PrintTag` Long NULL, " & vbCrLf
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "`HistoryStart` DateTime NULL, " & vbCrLf
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "`OpContextCode` Long NULL, " & vbCrLf
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "`LocRef` VarChar (3) NULL, " & vbCrLf
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "`FuncDesc` VarChar (30) NULL, " & vbCrLf
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "`CompartRef` VarChar (9) NULL, " & vbCrLf
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "`ParentModified` Bit NOT NULL, " & vbCrLf
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "`ValveFitNo` VarChar (9) NULL, " & vbCrLf
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "`RCMID` VarChar (16) NULL, " & vbCrLf
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "`TopLevelAssetID` VarChar (16) NULL, " & vbCrLf
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "`Parent` Long NULL, " & vbCrLf
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "`RCMREF` Long NULL, " & vbCrLf
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "`OpContextID` VarChar (16) NULL, " & vbCrLf
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "`IsShip` Bit NOT NULL, " & vbCrLf
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "`NextSSP` DateTime NULL, " & vbCrLf
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "`CSSSExpires` DateTime NULL, " & vbCrLf
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "`AssetUpkeepID` VarChar (11) NULL, " & vbCrLf
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "`RECORDDATE` DateTime NULL, " & vbCrLf
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "`GenerationCount` Long NULL, " & vbCrLf
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "`Timestamp` VarBinary (8) NOT NULL, " & vbCrLf
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "`SSI` Bit NOT NULL, " & vbCrLf
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "`SpecialReporting` Bit NOT NULL, " & vbCrLf
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "`ReportStart` DateTime NULL, " & vbCrLf
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "`ReportEnd` DateTime NULL, " & vbCrLf
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "`SpecialReportingAssetID` VarChar (16) NULL" & vbCrLf
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & ")"
oCustomTask1.ConnectionID = 2
oCustomTask1.CommandTimeout = 0
oCustomTask1.OutputAsRecordset = False
goPackage.Tasks.Add oTask
Set oCustomTask1 = Nothing
Set oTask = Nothing
End Sub
'------------- define Task_Sub2 for task Copy Data from Asset to Asset Task (Copy Data from Asset to Asset Task)
Public Sub Task_Sub2(ByVal goPackage As Object)
Dim oTask As DTS.Task
Dim oLookup As DTS.Lookup
Dim oCustomTask2 As DTS.DataPumpTask2
Set oTask = goPackage.Tasks.New("DTSDataPumpTask")
Set oCustomTask2 = oTask.CustomTask
oCustomTask2.Name = "Copy Data from Asset to Asset Task"
oCustomTask2.Description = "Copy Data from Asset to Asset Task"
oCustomTask2.SourceConnectionID = 1
oCustomTask2.SourceSQLStatement = "select [AssetID],[LevelNo],[UserTag],[AssetText],[PrintTag],[HistoryStart],[OpContextCode],[LocRef],[FuncDesc],[CompartRef],[ParentModified],[ValveFitNo],[RCMID],[TopLevelAssetID],[Parent],[RCMREF],[OpContextID],[IsShip],[NextSSP],[CSSSExpires],[AssetUpke"
oCustomTask2.SourceSQLStatement = oCustomTask2.SourceSQLStatement & "epID],[RECORDDATE],[GenerationCount],[Timestamp],[SSI],[SpecialReporting],[ReportStart],[ReportEnd],[SpecialReportingAssetID],[Context] from [UMMSM1].[dbo].[Asset]"
oCustomTask2.DestinationConnectionID = 2
oCustomTask2.DestinationObjectName = "Asset"
oCustomTask2.ProgressRowCount = 1000
oCustomTask2.MaximumErrorCount = 0
oCustomTask2.FetchBufferSize = 1
oCustomTask2.UseFastLoad = True
oCustomTask2.InsertCommitSize = 0
oCustomTask2.ExceptionFileColumnDelimiter = "|"
oCustomTask2.ExceptionFileRowDelimiter = vbCrLf
oCustomTask2.AllowIdentityInserts = False
oCustomTask2.FirstRow = 0
oCustomTask2.LastRow = 0
oCustomTask2.FastLoadOptions = 2
oCustomTask2.ExceptionFileOptions = 1
oCustomTask2.DataPumpOptions = 0
Call oCustomTask2_Trans_Sub1(oCustomTask2)
goPackage.Tasks.Add oTask
Set oCustomTask2 = Nothing
Set oTask = Nothing
End Sub
Public Sub oCustomTask2_Trans_Sub1(ByVal oCustomTask2 As Object)
Dim oTransformation As DTS.Transformation2
Dim oTransProps As DTS.Properties
Dim oColumn As DTS.Column
Set oTransformation = oCustomTask2.Transformations.New("DTS.DataPumpTransformCopy")
oTransformation.Name = "DirectCopyXform"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4
Set oColumn = oTransformation.SourceColumns.New("AssetID", 1)
oColumn.Name = "AssetID"
oColumn.Ordinal = 1
oColumn.Flags = 8
oColumn.Size = 16
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("LevelNo", 2)
oColumn.Name = "LevelNo"
oColumn.Ordinal = 2
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 2
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("UserTag", 3)
oColumn.Name = "UserTag"
oColumn.Ordinal = 3
oColumn.Flags = 104
oColumn.Size = 18
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("AssetText", 4)
oColumn.Name = "AssetText"
oColumn.Ordinal = 4
oColumn.Flags = 104
oColumn.Size = 80
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("Context", 5)
oColumn.Name = "Context"
oColumn.Ordinal = 5
oColumn.Flags = 232
oColumn.Size = 0
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("PrintTag", 6)
oColumn.Name = "PrintTag"
oColumn.Ordinal = 6
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("HistoryStart", 7)
oColumn.Name = "HistoryStart"
oColumn.Ordinal = 7
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 135
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("OpContextCode", 8)
oColumn.Name = "OpContextCode"
oColumn.Ordinal = 8
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("LocRef", 9)
oColumn.Name = "LocRef"
oColumn.Ordinal = 9
oColumn.Flags = 104
oColumn.Size = 3
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("FuncDesc", 10)
oColumn.Name = "FuncDesc"
oColumn.Ordinal = 10
oColumn.Flags = 104
oColumn.Size = 30
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("CompartRef", 11)
oColumn.Name = "CompartRef"
oColumn.Ordinal = 11
oColumn.Flags = 104
oColumn.Size = 9
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("ParentModified", 12)
oColumn.Name = "ParentModified"
oColumn.Ordinal = 12
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 11
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("ValveFitNo", 13)
oColumn.Name = "ValveFitNo"
oColumn.Ordinal = 13
oColumn.Flags = 104
oColumn.Size = 9
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("RCMID", 14)
oColumn.Name = "RCMID"
oColumn.Ordinal = 14
oColumn.Flags = 104
oColumn.Size = 16
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("TopLevelAssetID", 15)
oColumn.Name = "TopLevelAssetID"
oColumn.Ordinal = 15
oColumn.Flags = 104
oColumn.Size = 16
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("Parent", 16)
oColumn.Name = "Parent"
oColumn.Ordinal = 16
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("RCMREF", 17)
oColumn.Name = "RCMREF"
oColumn.Ordinal = 17
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("OpContextID", 18)
oColumn.Name = "OpContextID"
oColumn.Ordinal = 18
oColumn.Flags = 104
oColumn.Size = 16
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("IsShip", 19)
oColumn.Name = "IsShip"
oColumn.Ordinal = 19
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 11
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("NextSSP", 20)
oColumn.Name = "NextSSP"
oColumn.Ordinal = 20
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 135
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("CSSSExpires", 21)
oColumn.Name = "CSSSExpires"
oColumn.Ordinal = 21
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 135
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("AssetUpkeepID", 22)
oColumn.Name = "AssetUpkeepID"
oColumn.Ordinal = 22
oColumn.Flags = 104
oColumn.Size = 11
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("RECORDDATE", 23)
oColumn.Name = "RECORDDATE"
oColumn.Ordinal = 23
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 135
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("GenerationCount", 24)
oColumn.Name = "GenerationCount"
oColumn.Ordinal = 24
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("Timestamp", 25)
oColumn.Name = "Timestamp"
oColumn.Ordinal = 25
oColumn.Flags = 592
oColumn.Size = 0
oColumn.DataType = 128
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("SSI", 26)
oColumn.Name = "SSI"
oColumn.Ordinal = 26
oColumn.Flags = 24
oColumn.Size = 0
oColumn.DataType = 11
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("SpecialReporting", 27)
oColumn.Name = "SpecialReporting"
oColumn.Ordinal = 27
oColumn.Flags = 24
oColumn.Size = 0
oColumn.DataType = 11
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("ReportStart", 28)
oColumn.Name = "ReportStart"
oColumn.Ordinal = 28
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 135
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("ReportEnd", 29)
oColumn.Name = "ReportEnd"
oColumn.Ordinal = 29
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 135
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("SpecialReportingAssetID", 30)
oColumn.Name = "SpecialReportingAssetID"
oColumn.Ordinal = 30
oColumn.Flags = 104
oColumn.Size = 16
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("AssetID", 1)
oColumn.Name = "AssetID"
oColumn.Ordinal = 1
oColumn.Flags = 8
oColumn.Size = 16
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("LevelNo", 2)
oColumn.Name = "LevelNo"
oColumn.Ordinal = 2
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 2
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("UserTag", 3)
oColumn.Name = "UserTag"
oColumn.Ordinal = 3
oColumn.Flags = 104
oColumn.Size = 18
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("AssetText", 4)
oColumn.Name = "AssetText"
oColumn.Ordinal = 4
oColumn.Flags = 104
oColumn.Size = 80
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("Context", 5)
oColumn.Name = "Context"
oColumn.Ordinal = 5
oColumn.Flags = 232
oColumn.Size = 0
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("PrintTag", 6)
oColumn.Name = "PrintTag"
oColumn.Ordinal = 6
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("HistoryStart", 7)
oColumn.Name = "HistoryStart"
oColumn.Ordinal = 7
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 7
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("OpContextCode", 8)
oColumn.Name = "OpContextCode"
oColumn.Ordinal = 8
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("LocRef", 9)
oColumn.Name = "LocRef"
oColumn.Ordinal = 9
oColumn.Flags = 104
oColumn.Size = 3
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("FuncDesc", 10)
oColumn.Name = "FuncDesc"
oColumn.Ordinal = 10
oColumn.Flags = 104
oColumn.Size = 30
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("CompartRef", 11)
oColumn.Name = "CompartRef"
oColumn.Ordinal = 11
oColumn.Flags = 104
oColumn.Size = 9
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("ParentModified", 12)
oColumn.Name = "ParentModified"
oColumn.Ordinal = 12
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 11
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("ValveFitNo", 13)
oColumn.Name = "ValveFitNo"
oColumn.Ordinal = 13
oColumn.Flags = 104
oColumn.Size = 9
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("RCMID", 14)
oColumn.Name = "RCMID"
oColumn.Ordinal = 14
oColumn.Flags = 104
oColumn.Size = 16
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("TopLevelAssetID", 15)
oColumn.Name = "TopLevelAssetID"
oColumn.Ordinal = 15
oColumn.Flags = 104
oColumn.Size = 16
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("Parent", 16)
oColumn.Name = "Parent"
oColumn.Ordinal = 16
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("RCMREF", 17)
oColumn.Name = "RCMREF"
oColumn.Ordinal = 17
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("OpContextID", 18)
oColumn.Name = "OpContextID"
oColumn.Ordinal = 18
oColumn.Flags = 104
oColumn.Size = 16
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("IsShip", 19)
oColumn.Name = "IsShip"
oColumn.Ordinal = 19
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 11
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("NextSSP", 20)
oColumn.Name = "NextSSP"
oColumn.Ordinal = 20
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 7
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("CSSSExpires", 21)
oColumn.Name = "CSSSExpires"
oColumn.Ordinal = 21
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 7
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("AssetUpkeepID", 22)
oColumn.Name = "AssetUpkeepID"
oColumn.Ordinal = 22
oColumn.Flags = 104
oColumn.Size = 11
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("RECORDDATE", 23)
oColumn.Name = "RECORDDATE"
oColumn.Ordinal = 23
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 7
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("GenerationCount", 24)
oColumn.Name = "GenerationCount"
oColumn.Ordinal = 24
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("Timestamp", 25)
oColumn.Name = "Timestamp"
oColumn.Ordinal = 25
oColumn.Flags = 592
oColumn.Size = 8
oColumn.DataType = 128
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("SSI", 26)
oColumn.Name = "SSI"
oColumn.Ordinal = 26
oColumn.Flags = 24
oColumn.Size = 0
oColumn.DataType = 11
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("SpecialReporting", 27)
oColumn.Name = "SpecialReporting"
oColumn.Ordinal = 27
oColumn.Flags = 24
oColumn.Size = 0
oColumn.DataType = 11
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("ReportStart", 28)
oColumn.Name = "ReportStart"
oColumn.Ordinal = 28
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 7
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("ReportEnd", 29)
oColumn.Name = "ReportEnd"
oColumn.Ordinal = 29
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 7
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("SpecialReportingAssetID", 30)
oColumn.Name = "SpecialReportingAssetID"
oColumn.Ordinal = 30
oColumn.Flags = 104
oColumn.Size = 16
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oTransProps = oTransformation.TransformServerProperties
Set oTransProps = Nothing
oCustomTask2.Transformations.Add oTransformation
Set oTransformation = Nothing
End Sub
May 15, 2002 at 8:23 am
So you are entering "admin" for the username on the Access connection screen? If so try without it and see what happens. Also do not use a password for that connection. That is the only difference I see.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply