Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

I am using SSIS to import a 25,000 row CSV to an empty table. The table has a primary key that is populated as the data is inserted. I am using an ADO.Net destination.

What reasons are there why this operation would take around 5 minutes? At the default value of DefaultBufferMaxRows (10,000), the import fails completely with a timeout after around 5,000 rows. I dropped the value to 1,000 and now it gets there eventually with no timeout, but takes a full five minutes.

The Flat File data source has explicit column widths set, could that be the issue?

share|improve this question
2  
is it a straight import or are you doing any transformations? – Jeff Hornby Dec 13 '10 at 17:15
only setting explicit column widths on the flat file datasource. – Craig Dec 14 '10 at 8:29

migrated from stackoverflow.com Feb 9 '12 at 19:12

3 Answers

up vote 2 down vote accepted

The 'answer' was that the ADO.Net providers in SSIS are basically useless and an embarrassment to the '.Net' moniker. They flake at more than a few thousand rows and are so hobbled as to be effectively useless. Basically, just stick to the old OLEDB providers and everything will be fine.

share|improve this answer
+1 - I was about to suggest using OLEDB providers for the destination. One caveat though - if you're going from an ADO.NET source you may have to do unicode to ANSI conversions on anything where the destination is a varchar column. – ConcernedOfTunbridgeWells Feb 16 '12 at 10:41
Good point, but I basically converted everything to OLEDB and haven't looked back. – Craig Feb 16 '12 at 14:50
up vote 1 down vote

Thoughts:

  • Any triggers on the table?

  • What happens when the PK is dropped? Or what happens when you load a key-less staging table, before loading the real table?

  • Is the log or data file growing? (aka has it been previously shrunk by some clever person)

share|improve this answer
No triggers. This is a staging table, I'll try it without the key. I'll check the log, thanks. – Craig Dec 14 '10 at 8:31
Actually, there are several constraints on the table, and one default constraint. Would these make a difference? – Craig Dec 14 '10 at 8:39
Okay, the constraints and the key also don't make a difference. – Craig Dec 14 '10 at 9:29
I have now tried it on the server and it's just as slow, so it's not the network. – Craig Dec 14 '10 at 9:56
@Craig: check for other issues such as IO or log errors. Try batch size too – gbn Dec 14 '10 at 11:23
show 2 more comments
up vote 1 down vote

Some other thoughts on diagnosing the fault:

  • try creating a completely new table, with no constraints/PK etc and seeing if this effects the load
  • try the same on a different machine
  • try putting some data viewers on the DFT, and seeing how far/fast the data is flowing around your DFT?
  • when you are using sql destination are you using fast load?
share|improve this answer
Dropping all constraints and indexes made no difference. Neither did running it locally. Can you explain about DFT and data viewers? Not sure what you mean? I am using ADO.Net Destination, which does not appear to have fast load? – Craig Dec 14 '10 at 15:58
Actually ... a more basic question, what version of SQL are you using, and how are "importing" the table, is it by a) writing a Data Flow Task (DFT), or b) using the import/export wizard? – Marcus D Dec 14 '10 at 16:40
SQL 2008 SP1 and I'm using Data Flow Tasks, yes. – Craig Dec 15 '10 at 9:17
sry @Craig, I didnt reply to your previous qn on data viewers. A data viewer allow you to see tables/graphs of data as it flows through a data flow task. But if you have only an "Excel source" and a "SQL destination" in tyour DFT, then it will be obvious by running in VisualStudio/BIDS how quickly the records are being processed and if the bottleneck is in the reading or the writing. Another diagnosis idea is in the DFT to have the "excel source" and a "trash destination" (try bit.ly/gJHtDb). This will show if it is the read or the write that is the problem – Marcus D Dec 23 '10 at 10:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.