Hi All,

My DTS Imports data from a Comma Delimited TEXT from to SQL Server table.
Thsi works fine 99% of the time. But Fails some time by giving the below
ERROR - that I Log in a text file.

Step 'DTSStep_DTSDataPumpTask_1' failed

Step Error Source: Microsoft Data Transformation Services Flat File Rowset
Provider
Step Error Description:Invalid delimited data: text qualifier must be
followed by a column delimiter (except the last column).
Step Error code: 80004005
Step Error Help FileTSFFile.hlp
Step Error Help Context ID:0

I can see that reason of the fail, But as the test file is very BIG it is
very difficult to find the location or line number of the Problem. So Can
any one tell me how can I log the Line Number of ERROR TOO? Any Help or
Suggestion for Improvement Please.

Thanks
Prabhat

Hi Prabhat

In CSV File, There will be a new record for every line and the columns are
saperated by a comma or a semi-colon.

If the DTS fails, just see the number of records that are uploaded. Later
you can go to that line / record and correct the datat.

I believe this addressed your question. Please revert back if you have any
issues.

thanks and regards
Chandra


"Prabhat" wrote:

Hi Chandra,

thanks for reply.

My text file is Comma Separated. And if the Package will fail none of the
record will be Imported I believe. Please suggest.

Sample: -
"","I","6109163172","MS","STEPHANIE","FIDLER","206 INDEPENDENCE CT"

Thanks
Prabhat


"Chandra" <Chandra (AT) discussions (DOT) microsoft.com> wrote

Hi Prabhat
There are options in the DTS package where you can the preferences.

Right-Click on the DTS object and you can see for the options that are
available.

I dont have SQL Installed on my system, or else I would have told u what are
steps to follow.

If you still need assistance please get back and I will try to help u out

thanks and regards
Chandra


"Prabhat" wrote:

Hi Chandra,

Thanks for reply. We can set using that property. But I need a complete
transaction. So If Successful then Commit or Rollbak. So in that situation
we can't have half of the records imported. And the end user can't go to the
database and see what row was last imported. So we should provide a way that
should tell the user due to this row in the TEST FILE caused the problem, So
the user will go to test file and modify that.

Thanks
Prabhat


"Chandra" <Chandra (AT) discussions (DOT) microsoft.com> wrote

It's a long time since I used DTS, but I'm convinced you could get it to
report the row data that it fell over on.

It might not be on the error trapping for the particular transformation but
set on the whole package....you'd have to check package properties (Don't
have SQLServer in front of me to check this)

If I'm wrong then what you could do is this:

Split the import file into two and run both halves, identify in which half
where the error lies

Keep doing this until you have a manageable set of rows (you only have to do
this about 7 times to get a 10,000 row file down to 40 rows).

Hope this helps

Griff