I have issue with SSIS performance, when SSIS is used with SQL server 2005 database engine as backend, it takes long to execute procedure but when SSIS is used with SQL server 2000 database as backend, procedure execute in expected time frame. (No delays are faced.)
Kindly suggest.
Thanks
Rohit Wason
SP with SSIS and 2005 database
hueys
My current dev box is a P4, 3.4GHz with 2GB of RAM. We're pushing up to a million rows at a time without too many problems. You do need a fairly high spec machine for running VS/BIDS.
We're not doing any fuzzy stuff though and I'm guessing that might be CPU intensive. I don't know for sure though.
-Jamie
Bruce T
Not meaning to be rude but you have to supply alot more information in order for people to be able to help.
-Jamie
lrryklly
Right, here we go ...
The procedure is a User defined procedure and I am trying to call it through "Execute SQL Task" in SSIS. If UDF is executed through SQL server management studio, it executes under expected time frame but if its called through SSIS, it takes ever to move to the next control task.
By running UDP in SSMS, I already know the number of records affected. And when I execute UDP with SSIS control flow task, I can see expected number of records processed through SQL statements (within estimated time frame) but control task does not pass control to the next task in sequence.
Please do let me know, if you need any further information.
Thanks
Rohit Wason
Titou
Info here about memory usage if you're interested: http://blogs.conchango.com/jamiethomson/archive/2005/05/29/1486.aspx
-Jamie
cyr2224
Hello Donald
Thanks for document on procedure performance, its very useful. I did check procedure through profiler and it shows status as completed (but still in control flow it does not pass control to the next task).
Now, I have tried to use ADO.NET connection (instead of connection type as OLE DB) and this time to my surprise SSIS works perfectly.
My procedure use cursor in logic; so first question comes to my mind, Is there any issue with using cursor along with OLE DB connection to SQL server 2005. (The OLE DB connection works fine with SQL server 2000)
Kindly suggest.
Jamie, your document is also very helpful !
Thanks
Rohit Wason
HsiaoI
Mondo327
You got it !! It does get executed but after a long long time.
I will try to see what's happening with profiler and will let you know further details !
One more thing, can you please suggest what kind of machine configuration I should request at client end Like processor speed, RAM etc. We are about to start on a data migration project and it will involve data profiling, validation, Fuzzy grouping and Fuzzy lookup for eight different source systems. (With maximum records per source as 800,000) I know all these activities will be performed on server but what should be configuration for developer machines
Thanks
Rohit Wason
Trubble
You may wish to look at this kb: http://support.microsoft.com/ kbid=913371 and this thread on the T-SQL forum: http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=409594&SiteID=1
hth
Donald
N.Raja
Did you find anythng of interest with SQL profiler It is imporant to remember that SSIS is simply a client of SQL Server, like any other. If you are seeing issues and differences between queries in management studio and SSIS, then the reasons will be found using profiler.
I know a lot of people have found the information here http://www.sql-server-performance.com/stored_procedures.asp very useful.
Donald
Michael W Powell
OK, that sounds a bit strange.
Are you saying that the next task in the workflow NEVER gets executed, or that it does get executed but after a long time
Can you see, via Profiler, that the sproc finishes executing
Can you enlighten me as to what UDP stands for as well
-Jamie