Data Load with ntext column
Are the tables in the same database? Same instance? Same server? Describe the environment a little more. And please try to find out the limiting factor - is it disk? RAM I/O? CPU? Network I/O? Talk to me now on
August 27th, 2011 1:09pm

Which component do you use to load into the destination table?SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
Free Windows Admin Tool Kit Click here and download it now
August 27th, 2011 8:54pm

Hi , I have a SQL table whih has some 30M rows where two of its colums are ntext. I need to load this table into destination sql table. The time it takes to load goes to some 5 to 6 hours. I thought of using a RAW file , but the fact that there is a ntext column , it doesn't allow the RAW file way. Does anyone had any experience in loading such kind of data in the most optimim way ? Thanks SSridhar
August 28th, 2011 12:24am

A SQL to SQL table transfer - if there aren't significant transformations - could be better accomplished with plain T-SQL. But in order to diagnose performance issuew with SSIS, you need to find out the critical limiting factor. Is it RAM? CPU? Disk? Network? Is it the source extract? Or inserting into the destination? Watch your perfmon counters for the first four, and decompose your package for the last two. Talk to me now on
Free Windows Admin Tool Kit Click here and download it now
August 28th, 2011 1:51am

Hi , I have a SQL table whih has some 30M rows where two of its colums are ntext. I need to load this table into destination sql table. The time it takes to load goes to some 5 to 6 hours. -- > What is the process you currently doing ? Sridhar Hope that helps ... Kunal
August 28th, 2011 1:53am

Its definitely to do with the data type " ntext " . If I remove the columns then go via Raw file mode , it takes few mins to load them all . The problem lies to the fact that RAW files do not support ntext/ image etc datatypes, hence forcing to use the conventional way of loading either through SSIS or TSQL (which is lot slower than SSIS). Sridhar
Free Windows Admin Tool Kit Click here and download it now
August 28th, 2011 2:27am

The "Process" is to load the data from one sql table to another without any transformation. Sridhar
August 28th, 2011 2:28am

Hi Skola17, I am not sure why loading ntext data cause the poor performance, please try to change the DefaultBufferSize from 10485760 to 40485760 on the DTSX step (Data Flow Task), and then check if it works for you. Moreover, please take the following article about how to optimize SSIS Package Performance, Optimizing SSIS Package Performance: http://www.powerpivotblog.nl/optimizing-ssis-package-performance If you have any question, please feel free to ask. Thanks, Eileen
Free Windows Admin Tool Kit Click here and download it now
August 28th, 2011 3:41am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics