I've a SSIS Package, what executes a stored procedure, who calculates a lot of stuff with a recursive algorithm (TSQL). Sometimes the package aborts with this error message:
"Syntaxfehler, Berechtigungsversto? oder anderer allgemeiner Fehler". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
The first few german words mean: "Syntax error, Security error or general error"...
If i restart the packages, the error usually does'nt appear again. All releated data are'nt changed in the meantime. I can not reproduce the the error and a think, it depends on the connection (but SSIS and SQL Server are running on the same virtual maschine). By the way, the executed stored procedure does not have any resultset what is given back...
Every idea is very welcome...
Torsten
Since you say there is a lot of complexity in the stored procedure and that it only happens sometimes, I wonder if it isn't something in the procedure causing the problem (a concurrency-induced deadlock, a random number causing a divide by zero, the recursion limit being reached, etc.)I suggest you try running a profiler trace and see if any errors are getting thrown by the procedure which aren't being reported by SSIS.
|||
Thanks for your message!
Running the profiler reduces the performance in a way, that it would take a fews days...
When talking about recusion limit, what is ment by it? In my recursion i've an hirarchie with not more then 10 levels. the max-recursion option i've set to 500 and increased to 1000. Doesn't matter - i get the error sometimes like before... A division by zero is not possible, i'm only summerizing. A deadlock doesn't make sense, i'm not changing the data while running these process (and no other does).
But furthermore i get an other errormessage (sometimes), is it possible that they are combined in an unhappy way?
"The number of threads required for this pipeline is 107, which is more than the system limit of 64. The pipeline requires too many threads as configured. There are either too many asynchronous outputs, or EngineThreads property is set too high. Split the pipeline into multiple packages, or reduce the value of the EngineThreads property. "
Where is the place to increase the system limit of 64 threads? Sp_configure says following:
name min max config run_value
Ad Hoc Distributed Queries 0 1 0 0
affinity I/O mask -2147483648 2147483647 3 3
affinity mask -2147483648 2147483647 3 3
affinity64 I/O mask -2147483648 2147483647 0 0
affinity64 mask -2147483648 2147483647 0 0
Agent XPs 0 1 0 1
allow updates 0 1 0 0
awe enabled 0 1 1 0
blocked process threshold 0 86400 0 0
c2 audit mode 0 1 0 0
clr enabled 0 1 1 1
cost threshold for parallelism 0 32767 5 5
cross db ownership chaining 0 1 0 0
cursor threshold -1 2147483647 -1 -1
Database Mail XPs 0 1 0 0
default full-text language 0 2147483647 1031 1031
default language 0 9999 1 1
default trace enabled 0 1 1 1
disallow results from triggers 0 1 0 0
fill factor (%) 0 100 0 0
ft crawl bandwidth (max) 0 32767 100 100
ft crawl bandwidth (min) 0 32767 0 0
ft notify bandwidth (max) 0 32767 100 100
ft notify bandwidth (min) 0 32767 0 0
index create memory (KB) 704 2147483647 0 0
in-doubt xact resolution 0 2 0 0
lightweight pooling 0 1 0 0
locks 5000 2147483647 0 0
max degree of parallelism 0 64 0 0
max full-text crawl range 0 256 4 4
max server memory (MB) 16 2147483647 4000 2147483647
max text repl size (B) 0 2147483647 65536 65536
max worker threads 128 32767 128 0
media retention 0 365 0 0
min memory per query (KB) 512 2147483647 1024 1024
min server memory (MB) 0 2147483647 0 16
nested triggers 0 1 1 1
network packet size (B) 512 32767 4096 4096
Ole Automation Procedures 0 1 0 0
open objects 0 2147483647 0 0
PH timeout (s) 1 3600 60 60
precompute rank 0 1 0 0
priority boost 0 1 0 0
query governor cost limit 0 2147483647 0 0
query wait (s) -1 2147483647 -1 -1
recovery interval (min) 0 32767 0 0
remote access 0 1 1 1
remote admin connections 0 1 0 0
remote login timeout (s) 0 2147483647 20 20
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 600 600
Replication XPs 0 1 0 0
scan for startup procs 0 1 0 0
server trigger recursion 0 1 1 1
set working set size 0 1 0 0
show advanced options 0 1 1 1
SMO and DMO XPs 0 1 1 1
SQL Mail XPs 0 1 0 0
transform noise words 0 1 0 0
two digit year cutoff 1753 9999 2049 2049
user connections 0 32767 0 0
user options 0 32767 0 0
Web Assistant Procedures 0 1 0 0
xp_cmdshell 0 1 0 0
|||
Torsten_Katthoefer wrote:
But furthermore i get an other errormessage (sometimes), is it possible that they are combined in an unhappy way?
"The number of threads required for this pipeline is 107, which is more than the system limit of 64. The pipeline requires too many threads as configured. There are either too many asynchronous outputs, or EngineThreads property is set too high. Split the pipeline into multiple packages, or reduce the value of the EngineThreads property. "
Good grief! You must have a huge Data Flow. You need to break that up into multiple smaller data flows. Increasing the thread limit is not a good solution (even if it is possible). A raw file destination can be placed in a data flow to persist the pipeline to the disk, and then another data flow started with a raw file source to pick up where the other left off. I think you'll find your performance will be much better.
|||
Hmm, i don't think that this will help, because the package only starts a stored proc what makes all these calculations. The amount of data transported through the SSIS is not really much... So there is nothing to put into a raw file.
It would like to increase the thread limit, only to observe these process. Is it possoble to do this?
|||
Hmm, i don't think that this will help, because the package only starts a stored proc what makes all these calculations. The amount of data transported through the SSIS is not really much... So there is nothing to put into a raw file.
It would like to increase the thread limit, only to observe these process. Is it possoble to do this?
Well then something must be really messed up for SSIS to think it needs 107 threads. I don't know for sure, but I doubt you can raise that limit. If your package so simple, I would delete it and start over. Something in there is not right.
. In DTS2000, this issue could be solved by turning on the workflow-option "Execute on main package thread" !
