Hi!
I have been having an intermittent problem with a Transform in one of
my DTS packages that I'm stumped on - when I run the Transform source
query thru SQL Analyzer, I consistently get 443K rows returned. When
I execute the DTS package (with the exact same SQL in the Trasnform),
I *usually* only get about 101K rows processed - when I run the
package again, I might get 99K or 102K rows - *occasionally* I do get
the right number of rows loaded. This problem happens whether I run
the package interactively or thru a job. And there are no error
messages

I'm guessing that there's some sort of temp space issue. However,
there's definitely enough disk space...the log file is set for
unlimited growth...I've shrunk the destination database log file as
well as the tempdb data and log files before running the package. The
only steps in the package are an Exec SQL task that creates a view,
and the Transform which references the view - each of these steps is
set to execute on the main package thread.

What am I missing?? Any help on this issue would be greatly
appreciated!

Thanks,
Pete

I do not think you are missing anything.

are there any constraints on the destination? It is the rowcount in the
destination you are using to determine how many rows were moved or are you
looking at the GUI?

What about using the ExecuteSQL task to assign the value of COUNT(*) to a
Global Variable and then msgbox out that GV. Does that give you the correct
rowcount?

What SP of SQL Server + any hotfixes?

Does the value of the amount of rows moved change every time?

Are you just doing a 1:1?

What about on a smaller load? Same thing?

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Pete K." <pfk92000 (AT) yahoo (DOT) com> wrote

Thanks for your reply. Turns out I am running out of system memory on
the server when running this package, so not all rows are getting
processed - but no error messages or indication of this condition. I
am able to get the package to process all rows if I stop/start the SQL
Server services just prior to running the package. BTW, the source
SQL for my Transform is joining across about 30 tables/views (which
includes about 20 instances of one of the dimension tables), so it's
definitely eating up the memory.

Any suggestions on what I should do in my DTS package to better manage
the server memory? I'm using the Fast Load option on the Transform
task - I've tried playing with some of the task options (e.g., Fetch
Buffer Size & Insert Batch Size), but I don't have a good
understanding on the impact these options have.

Thanks,
Pete



"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Hi, I am experiencing the exact same problem with the
server memory while running a DTS package. However the
same DTS had been running for over a year. There has been
no change to the tables or the environment. It was
transfering 500 million rows without problem before, now
it dies after about 90 million. No error, no warning,
just hang. Bouncing SQL Server did not help. Nor did
rebooting Windows. Any suggestion will be greatly
appreciated. Thanks.

I'm still having these intermiitent memory issues when running my
scheduled DTS packages - not all the rows are making into the
destination table unless I stop/start the SQL Server before running
the package.

Any suggestions on better managing the server memory in the DTS
package would be greatly appreciated!

Thanks,
Pete

Hi, we resolved our problem by selecting a different ODBC driver. The
pacakge was to transfer data from Oracle to SQL Server. We ran out of
memory when using the "Oracle Provider for OLE DB" driver, but okay when
using the "Microsoft Ole DB Provider for Oracle" driver.

Thanks.


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!