txt >transform > sql table question

• BobMcC

  SSCrazy

  Points: 2260

  More actions

  April 19, 2007 at 3:00 pm

  #68363

  I'm maintaining an old DTS package, there is a Transform Data Task property that takes a comma delimited text file of unknown number of columns and transforms the data into a table that accepts nulls. There are then a series of Active X Tranformation Scripts defined that are basically of the form:

  If DTSSource.Count >= n Then

       DTSDestination("columnName") = DTSSource("Col00n")

  Else

       DTSDestination("columnName") = NULL

  End If

  But while testing some changes I made, I have discovered that when using activeX if you have code that copies the data over from a column that doesn't exist, it just copies NULL over anyway. In Other Words, a single ActiveX script for all of the 'extra' columns,

  i.e.

  DTSDestination(“destinationColumnName”) = DTSSource(“Col00n")

  DTSDestination(“destinationColumnName”) = DTSSource(“Col00n+1")

  DTSDestination(“destinationColumnName”) = DTSSource(“Col00n+2")

  etc

  Because if n, or n+1, or n+2 doesn't exist, it just copies a NULL anyway.

   

  So... Please comment on the above...  What is the Best Practices when dealing with source text files of unknown column length (where less columns signifies that NULLS are to be placed in the extraneous columns)?

   

  Thanks,

   

  Bob McC

• Adrian Nichols-360275

  SSCertifiable

  Points: 7694

  More actions

  April 20, 2007 at 10:05 am

  #701770

  I use a script similar to this to create the ActiveX transformation script for unknown columns:

  Function Main()

   Dim fso, f

   Dim inLine

   Dim outLine

   Dim sourceName

   Dim colNumber

   SET fso = CreateObject("Scripting.FileSystemObject")

   SET f = fso.openTextFile("c:\trial.csv")

   inLine = f.readline

   outLine = ""

   sourceName = ""

   colNumber = 1

   For i = 1 to LEN(inLine) + 1

   

    If Mid(inLine, i, 1) <> ","  AND i <= LEN(inLine) Then

     sourceName = sourceName + Mid(inLine, i, 1)

    ELSE

     outLine = outLine & "DTSDestination(Col"

     outLine = outLine & String( 3 - LEN(colNumber) , "0") & colNumber

     outLine = outLine &  ") = DTSSource(" & sourceName & ")" & vbcrlf

     sourceName = ""

     colNumber = colNumber + 1

    End if

   Next

   msgbox outLine

   SET f = Nothing

   SET fso = Nothing

   Main = DTSTaskExecResult_Success

  End Function

  You could build a temporary import table the same way (if you know the datatypes - or varchar them all??).

   

  ---

  
  Ade

  A Freudian Slip is when you say one thing and mean your mother.
  For detail-enriched answers, ask detail-enriched questions...[/url]