sql server dts: I have set up a DTS transform from a flat file to a SQL table. I verified
the transform works with a flat file of about 32,000 rows, no problems.
When I tried the same transform with the full 6,100,000 row production file
I got a message that the network connection was lost.

I get the message:
Error creating file mapping view: not enough storage is available to
process this command

What storage is being exhausted? Task Monitor shows 300MB available memory,
and I have 11GB free on the hard drive. Pagefile is 1.5GB. I searched the
KB but couldn't find anything relevant. Environment is SQL Server 2000,
client is Windows XP SP2.

The flat file is about 430MB in size.
Jack Peacock

Hi Jack,

Unfortuantely, in this case, I am afraid you will have to use BCP intead of
DTS to resolve this issue or DTS the table little by little.

To find the root cause of this issue, I suggest you open an support
incident from from a Microsoft Support Professional through Microsoft
Product Support Services. You can contact Microsoft Product Support
directly to discuss additional support options you may have available, by
contacting us at 1-(800)936-5800 or by choosing one of the options listed
at http://support.microsoft.com/directory/overview.asp


Sincerely yours,

Michael Cheng

Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---------------------------------------------------------------
Get Secure! - http://www.microsoft.com/security

This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Michael.

I would be interested in the reason for suggesting this course of action
(BCP instead of DTS).

What about BCP would not cause this problem that is occuring in DTS?
What about BULK INSERT?
What about using the batch option in the transform data task?
Do we see anything in Event Viewer?
I have successfully imported text files of 11.7 million rows and the file
was wide (1.2 GB)
What happend is we copy the file locally and import?


--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.Konesans.com


[quoted text, click to view]

[quoted text, click to view]
I copied the file to a local drive before the import, still failed. I
suspect it has something to do with DTS mapping the entire file into memory,
since there is an old article on the KB about DTS failing on some OSes.
Nothing in event viewer. Looks like it's a DTS bug that isn't easy to fix.

I've successfully copied tab-delimited files with over 2 million rows, so it
must be related to using fixed-format flat files. Plan B is to convert the
file to tab-delimted first.

I'm curious as to why I should open an incident with PSS if there's no fix?
Seems like it would be little more than a waste of time sitting on the
phone.
Jack Peacock

The reason you would open a call with PSS is to let them know something is wrong. If they discover it is an unknown bug then they
can at least contemplate fixing it. If they don't know anything is wrong then they cannot fix it. MS build the tools but we use
them.

You did not mention in the original post it was a Fixed Width file

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.Konesans.com


[quoted text, click to view]

Hi Jack and Allan,

Thanks for your updates!

Sorry and I should clarify it more clearly. I made some case studies
internally and found we have similar cases before. The customer had enough
free disk spaces and he could successfully make DTS for a smaller number
rows. The action plan is using BCP as a workaround, another workaround is
re-generate the flat file(I am not sure whether this is possible for your
scenario)

I noticed you have also mentioned knowledge base article

BUG: DTS - Error Creating File Mapping View
http://support.microsoft.com/kb/221513

however, you are not using Windows 95/98 or SQL Server 7 obviously, we
suspected that the cause of the issue was the re-occurence of KB221513. To
check it internally, we may have to reprocue of your issue in house and
reviewing SQL Server codes internally. That's why I suggest you contact PSS
for the *root cause* of this issue.

You may find that BCP will be stuck in some row, skip that row by splitting
into two parts.

Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!


Sincerely yours,

Michael Cheng

Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---------------------------------------------------------------
Get Secure! - http://www.microsoft.com/security

This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Hi Jack,

I am just checking on your progress regarding the information that was sent
you! Do you agree with you? Do not hesitate to let me know if you have any
questions or concerns on this.

Sincerely yours,

Michael Cheng

Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---------------------------------------------------------------
Get Secure! - http://www.microsoft.com/security

This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!