DTS - Import Text Files

Hi,

I am really new to DTS so please excuse if you find my query to simple.

I have a text file which has lots of records which I am trying to import into a SQL table using a DTS package.

The format of the text file is like this

Field1 ,Field2 ,Field3 ,Field4
Field1 ,Field2 ,Field3 ,Field4
Field1 ,Field2 ,Field3 ,Field4
Field1 ,Field2 ,Field3 ,Field4

As you can see, the text is properly organized but I am facing the following problem

1) If I try to import it as a comma delimited file
Some of the fields inside the text have comma within them. For example field1 in row 1 is like AA,BB,CC and field1 in row 2 is like FFFF. This causes a problem as DTS puts AA and FFFF in one column and BB and Field2 in the next column and so on. Effectively thus, the no. of columns keep increasing and I end up getting an error
"non-white spaces have been found at the end of last column"

2) If I try to import it is a fixed width file

It does extract properly but also places the comma along with the fields in the table. How do I get rid of them?

I will appreciate if someone can give me a solution fix for both the above methods or atleast one of the above.

Thanks a lot

If you trying to do this in DTS, it is a wrong forum. This is SSIS forum. SSIS in SQL2005, is replacement for DTS in SQL 2000.

Anyway, the answer to your question is as follows :

You can't use comma delimited for file format as comma exists within the data (AA,BB,CC).

You have to use fixed field file format. If you have column heading on row one select "Skip Rows = 1", else leave as 0. Make sure you select the commas before field2, field3 and field4 as separate columns in Fixed Field Column Positions. Then you can ignore those columns when you do transformation mappings. This is all you have to do in Text File (Source).

During transformations, you have select ActiveX, instead of Copy Column. the code below should replace all the commas with nothing.
Function Main()

DTSDestination("Field1") = Replace( DTSSource("Col001"),",","")
Main = DTSTransformStat_OK

End Function
You would need to replace DTSSource("Col001") and DTSDestination("Field1") with correct source and destination columns in your scenario.

I have written a simple package to test this. If you wish to have please e-mail me.

Thanks
Sutha

|||Sorry for my late reply Sutha.

Thank you very much. I will attempt your method tmr in the office.

Rochak