DataPump performance problem

I'm using an SSIS under SQL 2005; i transfer data with a DataFlow Component:

DataSource = OLEDB Source;

Destination = OLEDB Destination

The source is a View returning about 100000 records; if i execute it in query analyser it takes few seconds. But if i use it as DataPump Source it takes HOURS to execute it...

I've noticed that If i modify the SELECT query inserting a "TOP 10000000000", it takes few seconds..

Why? Am i missing something?

Could I ask what would be wrong with just always using Select TOP 100% in your query? If adding the TOP 100% explicitly speeds things up that much, then that is what I would do.|||

Netoblivion wrote:

I'm using an SSIS under SQL 2005; i transfer data with a DataFlow Component:

DataSource = OLEDB Source;

Destination = OLEDB Destination

The source is a View returning about 100000 records; if i execute it in query analyser it takes few seconds. But if i use it as DataPump Source it takes HOURS to execute it...

I've noticed that If i modify the SELECT query inserting a "TOP 10000000000", it takes few seconds..

Why? Am i missing something?

Read this article:

SELECT *... or select from a dropdown in an OLE DB Source component?
(http://blogs.conchango.com/jamiethomson/archive/2006/02/21/2930.aspx)

as this could well be the problem.

Let us know!!

Thanks

-Jamie

|||Thanks Jamie that really helped me to solve this problem!!!

dataflow error

This error seem to be very silly.did anyone come across this error.

I have been transferring data from textfile to a table using oledb destination.

The number of records in the text file are 2,091,650

Its was running just fine couple of days ago when the incoming data was little small then this...(arround 300,000).Now it seem to have a problem.

Here is the flow

1.File System task ->I copy the file to different location

2.Execute sql task->truncate tables

3.DataFlow task->I check for only the error files in this data flow.and all valid rows i transfer to a different text file.

4.Dataflow->filesource i connect to new text file created earlier.Here i convert fields to repective datatype and i insert if new or update record.

I dont know whats going on...

When i run my package it runs through the first three perfectly fine.When it comes to fourth step it sits there.....it dosent go to the tasks within this dataflow at all...and begining i have flat files source...

What could be the reason...

when i look at progress tab...i was able to look at the progress of other tasks but when it comes to this task it shows start>>>>>time and it sits there...

Are you using the same file connection manager for the "new" file created in step 3 and used in step 4? If so, try creating a second file connection manager for the file and use that connection manager in step 4.|||Do you have step three hooked up to step four via a precedence constraint?|||

I checked it out using a same file connection and different connection manager as well...but it didnt work...seems to really silly problem....

And all my flows are perfectly connected with precedence constraint as well.

Then only change which was made to this package was increase in volumn of data.

How can i fix this?Is there any other solution?

I also checked executing the last dataflow task alone as the file had data in it.

But it sits there and dosent proceed.

|||

Suresh,

could you troubleshoot this by making a simple package to only process your flat file? If it fails, try cutting the file in pieces to find out if it has some formatting errors.

HTH.