SSIS Is so slow...

Matter73
Matter73 used Ask the Experts™
on
I am averaging about 1000 records loaded an hour.  My software is:
Windows Server 2008 R2 Standard x64
SQL Server 2008 R2 standard x64

The Windows Server is a Quad Core and has 16 Gig of Ram.  This process used to take 4 hours longer, until we moved to this new server, which the 1000 records per hour still stinks.  I am not a newby, but am somewhat to SSIS.  This is a SQL Server to SQL Server and I feel that If I had done all this with stored procedures and server cursors I would have been better off.  Are there any known performance gotchas that I should be aware of.  This load time is so rediculous that I must be doing something wrong?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Reza RadConsultant, Trainer

Commented:
what did you do in data flow?
what is your source type?
what is your destination type?
did you used Fast Load in destination?
do you have any transformation between source and destination?
Jason YousefSr. BI  Developer

Commented:
Hi,

As @reza_rad said, what component and tasks you're using, so we can direct you to the right way, a nice screen shot of your package will do.

also you could surf that

http://technet.microsoft.com/en-us/library/cc966529.aspx

it'll give you an idea about the various component that SSIS is using , whether it's Non-blocking, Semi-blocking and Fully-blocking components.

Commented:
Hi,
  If you are not doing a truncate - load, I suggest drop all Index and recreate those after the Load.
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Author

Commented:
I have been making heavy use of the foreach loop.  I have been reading on the net that the foreach loop was not meant to act as a cursor for datasets.  Other's have gone on to say that this would be painfully slow and that this should be implemented with a SQL Server server side cursor outside of SSIS.  Please, any thoughts on this subject would be most appriciated.  

Note:  I have about 5,000 rows and it takes about 5 hours to process....

Commented:
Hi,
  If its such a heavy process, probably you can have a script task handle it. I think that way you will
 have the flexibility of using c# and getting it executed the way you want without much hassle.
Sr. BI  Developer
Commented:
@Matter73: I wouldn't use the for each loop for that, SSIS is an ETL tool, it's not meant to do that, whatever you can replace by doing it in the SQL engine will be faster than SSIS.

some would say to use an "Execute SQL Task" to return the result set of your cursor's founding select statment and store this in a package variable.  You could then place a data flow task within a For Each Loop container and manipulate the data according to the logic in your cursor.

But I would personally use my loginc in the "Data Flow task" It's not a cursor, but you do deal with rows one at a time.

it can just be an alternative to using them. You'd use a data flow task, which lets you specify a SQL Statement as a source, and then work with each row individually.

Try that.

by the way 5000 rows is nothing!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial