I also tried using SQL Server destination, but unfortunately I got the
same
error.
Raman/Bogdan, I remember encountering this in the labs - where if we use
OLEDB source (with the DMX prection query) and throw the result into a SQL
Server destination, we get an error. Here is a portion of the execution
results page of the SSIS package I created indicating the error:
[Predict Query batch 1 [1]] Error: An OLE DB error has occurred. Error
code:
0x80040E00
[DTS.Pipeline] Error: The PrimeOutput method on component "Predict Query
batch 1" (1) returned error code 0xC0202009. The component returned a
failure code when the pipeline engine called PrimeOutput(). The meaning of
the failure code is defined by the component, but the error is fatal and
the
pipeline stopped executing.
[DTS.Pipeline] Error: Thread "SourceThread0" has exited with error code
0xC0047038.
[DTS.Pipeline] Error: Thread "WorkThread0" received a shutdown signal and
is
terminating. The user requested a shutdown, or an error in another thread
is
causing the pipeline to shutdown.
[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code
0xC0047039.
As per my e-mail below, I tried using OLEDB data flow task. For a while,
the packages runs and I see predictions running consistently without
fluctuations. However, the package fails with the error in the attached
image. When I check the destination table, no rows hasn't been written
yet. Maybe the errors are caused when the packages tries to write the
results in the destination table.
What do you exactly mean by "large gaps?" Anyway, I don't think that
the
fluctuations are caused by delays in fetching data from SQL Server -
this
is
also what I had in mind initially. But when I tried running the query
in
management studio, I don't experience the fluctuations. It must have
something to do with the DM query task and how it writes the results
the
output table.
I tried running the DMX in management studio, and also using OLEDB data
flow
tasks and I did not experience the fluctuations. I also notice that the
locks are a lot smaller. See attached.
Regarding the connection error, I tried using both SQLOLEDB and
The fluctuations could be due to delays fetching data from SQL Server.
Are
there large gaps in the foreign keys in the nested table - the shaping
algorithm that joins the two might be skipping a lot of records while
producing the shaped rowset.
Re. the connection error, are you using SQLOLEDB or SQLNCLI in the
datasource you're referencing in your OPENQUERY?
--
Raman Iyer
SQL Server Data Mining
[Please do not send email directly to this alias. This alias is for
newsgroup purposes and is intended to prevent automated spam. This
posting
is provided "AS IS" with no warranties, and confers no rights.]
.
I'm experiencing some odd behavior when I run predictions against a
decision
tree model that I've created. When I run the DM predict query either
through
SQL Server Management Studio or through a DM task using SSIS, the CPU
cycles
seems to fluctuate. When I checked predictions per second counter
using
perfmon, I found out that in short bursts predictions per sec would
read
somewhere from 80 to 130 then drop to 0. After a few seconds
predictions
will go up again to and back down to 0. The average CPU utilization
is
around 30%. What could be causing this kind of behavior?
Another odd thing I noticed is that the SQL Server:Locks per second
is
quite
high.
I have a dual CPU server, 1.3GHZ each processor and 2GM of RAM. I
have
Windows 2003 Standard Edition installed on the machine and SQL Server
2005
(IDW13). I get the same result even if I run 2 parallel DMX queries
(1
per
CPU).
Here is the query:
SELECT FLATTENED
PREDICT([TonesHits], 5),
t.[mobileno]
From
[Tone Reco Tree]
PREDICTION JOIN
SHAPE {
OPENROWSET('SQLNCLI',
'Data Source=LOCALHOST;Integrated Security=SSPI;Initial
Catalog=Project88_DM',
'SELECT
[mobileno]
FROM
[dbo].[Proj88_TopTable0] --change underlying table to target
users
ORDER BY
[mobileno]')}
APPEND
({OPENROWSET('SQLNCLI',
'Data Source=LOCALHOST;Integrated Security=SSPI;Initial
Catalog=Project88_DM',
'SELECT
[SubKey],
[CustHit]
FROM
[dbo].[Proj88_NestedTable0] --change underlying table to
history
table
ORDER BY
[CustHit]')}
RELATE
[mobileno] TO [CustHit])
AS
[PreviousTones] AS t
ON
[Tone Reco Tree].[TonesHits].[SubKey] = t.[PreviousTones].[SubKey]
By the way, when I tried using OPENQUERY I got this error message:
"OLE
DB
error: OLE DB or ODBC error: Login timeout expired; HYT00; Client
unable
to
establish connection; 08001; Named Pipes Provider: Could not open a
connection to SQL Server [53]. ; 08001."
