Hello all,

You all are probably going "HUH?!?!?!" by the subject line. Yeah, I know, a contradiction in terms.

We have a client sending us data in fixed width text files--one month per file. The problem is that the width of each file varies
from month to month. For example, in July & August of 2003, each row is 68 characters long. Then Sept & Oct, 2003 are 69
characters long. I can't set up the DTS package for the July '03 file because when it gets to the Sept '03 file, it will be off and
it won't import correctly.

Is there anyway to check the length of rows in each file and then dynamically set the file/column properties for each file before
importing?

These files have 5 fields in them. The first 4 will be the same from file to file. The problem is the last field. It will always
end at the last possible column (character column in Notepad--NOT data type/field column like in Access/SQL Server) in each row of
data and it will always be 8 characters in length. But since the row length is different from file to file, I can't really set it
up.

If I were to write out field definitions in VBA using RIGHT, LEFT and MID functions it would go something like this:

Col001 = LEFT(Row of Data, 11)
Col002 = MID(Row of Data, 13, 2)
Col003 = MID(Row of Data, 16, 4)
Col004 = MID(Row of Data, 21, 13)
Col005 = RIGHT(Row of Data, 8)

OR the 5th column/field could be written like this as well:

Col005 = MID(Row of Data, LEN(Row of Data) - 8, 8)

Can this be done dynamically with each new file?

Thanks for any help anyone can provide,

Conan Kelly

Hello Conan,

I'd simply try this:

for you file connection do not set "Fixed field " but "Delimited coulmns"
and choose a 'Column separator' that you presume it will IMPOSSIBLE to find
in your file (example: 'ÇÇÇÇÇ').
Doing so you will always see only one variable lenght column delimited by
the Row delimiter.
So you could then set up a datapump with only one ActiveX transformation
like this :


'************************************************* *********************
' Visual Basic Transformation Script
'************************************************* ***********************

' Copy each source column to the destination column
Function Main()
DTSDestination("Col001") = Left( DTSSource("Col001") , 11 )
DTSDestination("Col002") = Mid( DTSSource("Col001") , 13,2 )
DTSDestination("Col003") = Mid( DTSSource("Col001") , 16,4 )
DTSDestination("Col004") = Mid( DTSSource("Col001") , 21,13 )
DTSDestination("Col005") = Right( DTSSource("Col001") , 8 )
Main = DTSTransformStat_OK
End Function

Davide,

Thanks for the info. That looks pretty good. I'll give it a try.

Thanks again for all of your help,

Conan
"Davide" <rossetti71 (AT) gmail (DOT) com> wrote