Variable no. of Fields in DTS Import File 1

[itsmarkdavies]

I am trying to write a DTS Package to import a text file. The file will always be Tab(or Comma)-delimited but the number of Fields may vary. What is the best way to create the package in order to make sure I get all the data, and also know what Fields i`ve pulled in ?. Should I just pull the whole file in and examine it in some way with T-SQL, or is there a better way perhaps with an ActiveX / VB Script Task ?

Any pointers would be much appreciated.

Thanks.


itsmarkdavies@hotmail.com

 

[Adam95]

I had the exact same situation come up. In my case there would sometimes be a middle name field and sometimes not. When you tell the DTS package what file you want to bring in it get the layout of the text file(ie. # of field) from like the first 200 rows. Thus if the middle name field was absent in the first 200 rows, I could not select that column to bring over. So what I did to take care of this was edit the incoming text file before I loaded it. I add a line at the begining of the text file with n 3 of commas(ie. ,,,,,,,,,,,). Since it was the first line in my text file I ALWAYS had a fixed # of columns to import, thus not leaving out any columns. So lets say 90% of the time your text file has 23 columns, but the other 10% of the time it can have more or less than 23 columns, append a line at the top with lets say 25 blank columns(ie.,,,,,,,,,,,,,,,,,,,,,,,,,) than way you will never miss a column if it comes in with more than 23 in the text file. This is kinda confusing, let me know if you need clarification.

 

[nigelrivett]

See

http://www.nigelrivett.net/ImportTextFiles.html

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.