DTS tranform data task error required help urgent!

• darenhan

  Valued Member

  Points: 64

  More actions

  January 20, 2003 at 9:45 am

  #105433

  hi , below is a vbscript which i put inside my transform data task between my file connection and the sqlserver oldeb connection. In the package, i tried to create a table based on the filename and

  create the transformation column mapping

  to cater for new "fields" which may be in the textfile the next time uploading. However, i got an error on Line 38. Error

  Source: ADODB.Field

  Error Description: Error Code:0

  Error Source= ADODB.Field

  Error.Description: Operation not allowed when the object is closed.

  Error occured when

  -> oColumn.Size = adoRs.Fields(i).ActualSize . See below.

  what am i doing wrong? please advise.

  Option Explicit

  Const adVarChar = 200

  Const rsFieldLength = 60

  Function Main()

  Dim oPkg, oTask, oDataPump

  Dim sSourceTable, sDestinationTable, sHeaderString, sHeadArray

  Dim adoCn, adoRs

  Dim i, oColumn, oTransform, oTransProps

  set oPkg = DTSGlobalVariables.Parent

  sSourceTable = DTSGlobalVariables("sFileName").Value

  sDestinationTable = DTSGlobalVariables("sTableName").Value

  sHeaderString = DTSGlobalVariables("sHeaderName").Value

  sHeadArray = Split(Trim(sHeaderString), ",")

  set oTask = oPkg.Tasks("Copy Data from Sales_Budget_Daily_2002 to

  [remedi].[dbo].[Sales_Budget_Daily_2002] Task").CustomTask

  oTask.SourceObjectName = sSourceTable

  oTask.DestinationObjectName = sDestinationTable

  set adoCn = CREATEOBJECT("ADODB.Connection")

  set adoRs = CREATEOBJECT("ADODB.Recordset")

  adoCn.Open

  "Provider=sqloledb;server=tbssdb;uid=sa;pwd=;database=remedi"

  For i = 0 To UBound(sHeadArray)

  adoRs.Fields.Append sHeadArray(i) , adVarChar,

  rsFieldLength

  Next

  For i = 0 to adoRs.Fields.Count-1

  ' add Column Definition

  set oColumn =

  oTask.DestinationColumnDefinitions.New(adoRs.Fields(i).Name, i + 1)

  With oColumn

  .Nullable = True

  .Size = adoRs.Fields(i).ActualSize ---> Error

  here!!!

  .DataType = adoRs.Fields(i).Type

  End With

  oTask.DestinationColumnDefinitions.Add oColumn

  set oColumn = Nothing

  ' add Transformation

  set oTransform =

  oTask.Transformations.New("DTS.DataPumpTransformCopy")

  oTransform.Name = "Transformation" & i + 1

  ' source Transform

  set oColumn =

  oTransform.SourceColumns.New(adoRs.Fields(i).Name, 1)

  With oColumn

  .Nullable = True

  .Size = adoRs.Fields(i).ActualSize

  .DataType = adoRs.Fields(i).Type

  End With

  oTransform.SourceColumns.Add oColumn

  set oColumn = Nothing

  ' dest Transform

  set oColumn =

  oTransform.DestinationColumns.New(adoRs.Fields(i).Name, 1)

  With oColumn

  .Nullable = True

  .Size = adoRs.Fields(i).ActualSize

  .DataType = adoRs.Fields(i).Type

  End With

  oTransform.DestinationColumns.Add oColumn

  set oColumn = Nothing

  oTask.Transformations.Add oTransform

  set oTransProps = oTransform.TransformServerProperties

  set oTransform = Nothing

  set oTransProps = Nothing

  Next

  Main = DTSStepScriptResult_ExecuteTask

• philcart

  SSC-Forever

  Points: 47794

  More actions

  January 20, 2003 at 2:37 pm

  #446805

  Would it be taht you haven't actually opened the Recordset object? You open the connection, not the recordset.

  Also, probably not a good idea to advertise your server name and the fact that it has a blank sa password

  Thanks

  Phill Carter

  --------------------
  Colt 45 - the original point and click interface