Welcome to EMC Consulting Blogs Sign in | Join | Help

SSIS Junkie

SSIS: Destination Adapter Comparison

I recently had cause to look into the various ways that I could load data from the SSIS pipeline into SQL Server. There are three destination adapters for doing this:

[OK, the first two are actually the same destination adapter just with differing configurations but for the purposes of this post we'll treat them as two different ones]

 

We all hopefully know that in a straight side-by-side comparison OLE DB Destination is the slowest option and SQL Server Destination is the quickest.

 

Hopefully we also all know by now about the SQL Server Destination - its an adapter for getting data into SQL Server incredibly quickly but it does have the disadvantage that it can only be used when the SSIS package is being run on the same server that you are inserting into (by the end of this post you should understand a little about why).

 

I wanted to understand a little bit more about what happens under the covers with these three adapters. To that end I knocked up a SSIS package containing three data-flows. Each data-flow inserted the same data into the same table in SQL Server using the same SSIS Connection Manager, the only difference being the destination adapter being used.

 

 

 

The "Setup" sequence container puts my source data into a raw file - Konesans' Data Generator is used to generate my data- and creates my destination table if it doesn't already exist. I initially generated 1000 rows of data.

 

The other three sequence containers just separate out my three data-flows along with any annotations that I attribute to each.

 

I used the ever-brilliant SQL Server Profiler to capture what commands were being sent through to SQL Server in order to realise the insert.

 

The OLE DB Provider used in the Connection Manager was SQL Native Client (SNAC).

 

OLE DB Destination

When using OLE DB Destination the access mode for getting data into the table is OLE DB OPENROWSET. But how does that manifest itself when doing the insert? Well here's the commands that I caught with Profiler:

 

exec sp_cursoropen @p1 output,N'select * from [dbo].[Destination]',@p3 output,@p4 output,@p5 output

...

exec sp_cursor

180150003,4,0,N'[dbo].[Destination]',

@GeneratedStr_1='IRTL', @GeneratedStr_2='IRTL',

@GeneratedStr_3='IRTL', @GeneratedStr_4='IRTL',

@GeneratedStr_5='IRTL', @GeneratedInt_1=276802,

@GeneratedInt_2=951373, @GeneratedInt_3=377909,

@GeneratedInt_4=513423, @GeneratedInt_5=50702

...

exec sp_cursorclose 180150003

 

There were 1000 exec sp_cursor commands - one for each row of data. Here is that Profiler trace.

 

 

 

I had never heard of sp_cursoropen, sp_cursor, sp_cursorclose before so I looked them up in BOL and I found this page. It turns out that they are provided only for internal use by SNAC and are not supported if you call them directly.

 

Well OK, that makes sense because its SNAC that is doing the work. Straightaway we can see why this is the slowest option though right? It executes a statement for every row of data in the pipeline.

 

 

OLE DB Destination with FastLoad

Where OLE DB Destination uses an access mode of OPENROWSET, OLE DB Destination with FastLoad uses OPENROWSET with FastLoad.

 

FastLoad means that the adapter uses BULK INSERT for the insertion. Here is the insert command as captured in Profiler:

 

insert bulk

[dbo].[Destination](

[GeneratedStr_1] varchar(5) collate SQL_Latin1_General_CP1_CI_AS,

[GeneratedStr_2] varchar(5) collate SQL_Latin1_General_CP1_CI_AS,

[GeneratedStr_3] varchar(5) collate SQL_Latin1_General_CP1_CI_AS,

[GeneratedStr_4] varchar(5) collate SQL_Latin1_General_CP1_CI_AS,

[GeneratedStr_5] varchar(5) collate SQL_Latin1_General_CP1_CI_AS,

[GeneratedInt_1] int,

[GeneratedInt_2] int,

[GeneratedInt_3] int,

[GeneratedInt_4] int,

[GeneratedInt_5] int)with(TABLOCK,CHECK_CONSTRAINTS)

 

There was only one insert command issued because I had FastLoadMaxInsertCommitSize=0 on the destination adapter which means everything is issued in a single commit. Changing this property results in more insert commands.

 

Here is the Profiler trace:

 

 

 

Its pretty clear to see from the Profiler trace that using the OPENROWSET with FastLoad option results in a bulk insertion but I wanted to know more about it and after hunting around in BOL I got a surprise. BULK INSERT command is there but INSERT BULK is undocumented. Not only that but I wanted to know why the Profiler trace didn’t show where the data was coming from - all we get is a column list.

 

I turned the question over to the SQL Server community at Conchango and my colleague Joe Pollock found this article by Ken Henderson which makes mention of it. Not a lot though, all it says is it is a “special TDS packet type” for bulk loading data. Well that didn’t help too much but another one of our guys (and fellow MVP) David Portas was able to provide a bit more information. TDS stands for Tabular Data Stream and is the protocol that OLE DB uses to talk to SQL Server.

 

So the upshot of all that is that the OLE BD Destination with FastLoad uses OLE DB’s implementation of BULK INSERT to get data into SQL Server.

 

SQL Server Destination

Reading BOL will tell you that the SQL Server Destination also uses BULK INSERT, just as the OLE DB Destination with FastLoad option does (as we have just learned). So why is it any better? Well perhaps there is a clue in what we see in the Profiler trace:

 

BULK INSERT [dbo].[Destination]

FROM 'Global\DTSQLIMPORT              0000000000000c1c000000000096c5a8'

WITH (DATAFILETYPE = 'DTS_Buffers', CODEPAGE = 'RAW', CHECK_CONSTRAINTS, TABLOCK)

 

Here’s the full screenshot:

 

 

 

BOL is very light on what goes on under the covers with the SQL Server Destination (and I have requested that the BOL article be heavily expanded) but as far as I can discern an in-memory object called Global\DTSQLIMPORT is created. The fact that it is an in-memory object helps to explain why SQL Server Destination is so quick – any data stored in memory will be accessed quicker than anywhere else.

 

The DATAFILETYPE = 'DTS_Buffers' part is also very interesting. Again it isn’t documented in BOL (the documented options are 'char' | 'native'| 'widechar' | 'widenative') and again I have requested that it is in the future. I am assuming that this undocumented DATAFILETYPE was created specifically for the SSIS SQL Server Destination adapter – I hope this will be clarified in the future.

 

One thing I do know about the SQL Server Destination from reading BOL is that the user executing the SSIS package requires permission to create global objects. This permission is defined in Windows’ “Local Security Policy” tool. In the screenshot here I have highlighted the permission that needs to be set

 

 

 

This is more interesting. This is a setting within Windows itself, not within SQL Server. It looks as though the SQL Server Destination is creating an in-memory object in the memory space of the Destination server. That would explain why it has to be run on the same server as the destination, BULK INSERT will not be able to access the memory space of a remote machine.

 

 

 

Clearly, understanding how data moves between SSIS and SQL Server is integral to debugging and performance tuning our applications so hopefully this post will have given you some useful insights. I have been wanting to write this blog post for a long long time because I am a real advocate of understanding the components that are available so you can make them work effectively. And aside from that I just wanted to know some of the answers herein for myself!

 

There is still a lot more to learn I’m sure as for the purposes of this post I have only investigated use of SNAC. There are a number of other OLE DB Provider options available to you depending on your source system:

 

 

 

If there is anything here that you would like to follow up on then please get in touch. General comments, criticisms, corrections or additional information are all very much welcomed.

 

I have made the demo package that I built for this available here in case any of you want to try this out for yourself.

 

-Jamie

UPDATE 2007-12-19: Lara Rubbelke provides some useful supplementary information here: http://blogs.digineer.com/blogs/larar/archive/2006/08/20/385.aspx. if you want to read more of Lara's missives she now blogs here: http://sqlblog.com/blogs/lara_rubbelke/archive/2007/10/16/we-are-leaders.aspx

Published Monday, August 14, 2006 10:21 PM by jamie.thomson
Attachment(s): DestinationInsertions.zip

Comments

 

sqlbi said:

I have to say that in many different tests I found no significative differences (loading millions of records) between OLE DB / SNAC with Fast Load enabled (and a batch size between 1000 and 4000) and SQL Server Destination.
For that reason, I tend to prefer OLE DB because I'm able to track insert errors (you can't in SQL Server Destination).
What are your favorite scenario where you prefer SQL Server Destination to OLE DB + Fast Load?
August 17, 2006 12:57 AM
 

jamie.thomson said:

Marco,
I don' have any. I always use OLE DB Destination myself anyway even when SQL Server Destinaton is an option. The reason is simple - if the ETL processing were ever to move to another box then I would have to re-engineer my packages!

-Jamie
August 17, 2006 6:49 AM
 

Lara's Blog said:

One of my favorite blogs on SQL Server Integration Services (SSIS) is by Jamie Thompson.  He does...
August 21, 2006 4:21 AM
 

Jack said:

I have been trying to use the BULK INSERT statement to import data from a text file to a database. The problem is both of the source(text file) and destination(database) are situated on different location or servers. When I am executing my stored procedure I get the system error that says it cannot fined the specified File path.

February 6, 2007 10:31 AM
 

HB said:

Just a dumb question - is OLE DB Destination + Fast Load available on a remote box?  I am under the impression that only OLE DB iDestination is available from a remote box. Fast Load is only available on the local server.  

February 21, 2007 1:32 AM
 

jamie.thomson said:

Hi HB,

Yes, you can use OLE DB Destination with FastLoad against a remote box. Its the SQL Server Destination that cannot access a remote box.

-Jamie

February 21, 2007 4:31 AM
 

Braulio_Malaga said:

Have you ever experience INSERT BULK errors and crashes on you SSIS process when running unattended ?...

March 31, 2007 7:22 PM
 

Dejan said:

I tested this with 32 million records (9GB) and I have even found OLEDB a touch faster.

April 16, 2007 9:14 PM
 

jamie.thomson said:

Interesting. Thanks Dejan.

I'd be interested in knowing more about this giving that there isn't much out there in the way of mini case studies.

How long did it take?

What was the width of the row? (I've calculated about 280bytes from what you've said)

What was the nature of the destination table (i.e. any indexes, was the table empty)?

Thanks

Jamie

April 16, 2007 11:15 PM
 

Ches Weldishofer said:

I'd like to know if you've found a significant difference in the loads when running SSIS from a remote server, but the destination and the target tables are the same.

IE.  Checking for new records in an existing warehouse table... you join them to the possible new records coming in, then put any new ones into the table.

Which is the best choice to not collide?

May 15, 2007 6:55 PM
 

jamie.thomson said:

Ches,

Try using the Lookup component with full cache.

-Jamie

May 15, 2007 7:10 PM
 

Ches Weldishofer said:

Looked into that.  One thing I hit a big wall on, is parameters.  Lookup components don't allow for them.  And as well they don't allow for using a variable as the query.

While I do use these extensively for looking up ID's from small lookup tables, what I'm trying to get to now is my main tables which are on in the 10's of millions currently (with only a years data).

So it's importante to be able to limit to just one specific BU at a time not the entire table.

My current data flow is to pick up the new records out of the staging database, and pick up existing records (both for one business unit) then join them together using the logical keys from the source data.

Once they're joined, I use a conditional split to pull out any NEW records, Any delete records (exist in the target not the source) and any changed records.

Any unchanged records are at that point dropped from the stream.  New one's go have a couple fields added and then are inserted into the target.  Changed or deleted one's then have a date and delete ind field attached and the target is updated.

What seems to happen is a select statment blocks the entire process once you get past rather small datasets.

May 15, 2007 8:38 PM
 

Ches Weldishofer said:

As a second thought.  None of this happens until the packages are moved to the server.  They run fine as long as they are in debug, but just hang when run by a job.

May 15, 2007 8:51 PM
 

jamie.thomson said:

Ches,

I gotcha. I've experienced the same pain myself.

Get the data out of the target and drop it into a raw file. Use that raw file as the input to the data-flow that you ahve already built. That way you are using the same data and the lock on the target table has gone.

-Jamie

May 15, 2007 9:35 PM
 

Ches Weldishofer said:

Okies.  Thanks Jamie!

May 15, 2007 9:58 PM
 

Meena said:

Thanks for the wonderful article.

I learned a lot from this very useful article.

I had a question though.

When using When using OLE DB DESTINATION as the access mode for getting data into the table , (using  OLE DB OPENROWSET from a variable), will that cause deadlock in the transactions?

Should I use OLE DB DESTINATION WITH FAST LOAD?

   OR

Should I use the table name staright instead of the variable?

PLEASE HELP!

urgent!....

May 24, 2007 11:05 PM
 

dotnetfellow said:

The operational group at our company has decided that all data manipulation to the database must go through a user stored procedure on that server.  I do not see a way using any of the Data Flow destination objects how to insert data into the destination table calling a stored procedure.  The only way I can figure how to do it is by using the Ole Db Command transformation object, but this does not have rollback capability as does the insert object.  I am not trying to defend the policy of the operational group, I am only trying to comply with it.  Does anyone know how to call a stored procedure from a destination data flow object in order to insert/update/delete data there?  Thanks.  -Dotnetfellow

June 26, 2007 5:02 PM
 

Guna said:

Jamie, does using OLEDB Destination with fast load write transaction log entries? if it doesn't, and the package uses 'transactionoption=required', will the rollback fail if package encounters error?

thanks.

December 5, 2007 3:57 AM
 

Guna said:

dotnetfellow,

have you explored using a custom script component, which is able to receive a buffer of rows and then you can write managed code to call the stored procedure?

Since its a custom component, its easy to reuse across dataflows.

December 5, 2007 6:22 AM
 

Guna said:

dotnetfellow,

another suggestion. maybe you could send the rows to a RAW, or text file source, and then use the 'Execute SQL Task' outside the dataflow?

Haha.. i just realised how late i am suggesting this. ;)

December 5, 2007 6:24 AM
 

jamie.thomson said:

Guna,

"Jamie, does using OLEDB Destination with fast load write transaction log entries? if it doesn't, and the package uses 'transactionoption=required', will the rollback fail if package encounters error? "

I honestly don't know. If you investigate and discover anything I would welcome your letting me know.

-Jamie

December 5, 2007 1:38 PM
 

Andy said:

Using SQL Server Destination.

Why does the rate slow down as a large table is transfered?  I have a 5.6 million row table.  The first 800k comes over in the first minute.  It slowly descreases over time.  After 10 minutes and 3 million rows its down below 200k/minute.  

I've tried every setting I can find.  

I'm using an OLEDB source (Sybase).  Storing it on a SQL Server 2005 database.  (package runs on the same server as the database)

Not checking constraints, table is locked, no indexes, no triggers.  Just a straight data dump.  

I tried MaxCommitSize but that seems to have no effect.  

(I have several large tables, running them at the same time brings the data pull to it's knees after a while

Any help would be appreciated.  Thanks,

Andy

December 10, 2007 8:47 PM
 

Andy said:

Quick update.  Slowdown seems to be caused by the source as changing to a Flat File Destination saw the same slowdown.

What's really annoying is if I cut the data transfer into 2 chained data flow tasks (using key on the soure table to split the rows pulled), I can save a significant amount of time (from 26 minutes down to 16 minutes).  If I split it to thirds it would be even greater.  

I can't imagine this is the best solution.  

Andy

December 11, 2007 3:06 PM
 

jamie.thomson said:

Andy,

Are you doing any transformations?

Use the OVAL principle (http://search.live.com/results.aspx?q=oval&form=QBJK&q1=macro%3Ajamiet.ssis) to try and break down where the bottleneck is occurring.

Interesting that splitting over multuple data flows causes speed up. It'd be nice to have the chance to investigate but obviously I'm not there with you so can't :(

Make sure that you are monitoring memory usage. And use perf mon to see if any buffers are getting spooled to disk.

-Jamie

December 11, 2007 3:17 PM
 

Andy said:

No transformations.  

More information:

Same setup in a SQL Server 2000 DTS package shows no slowdown.  In fact the speed that the SSIS package showed for the first 30 seconds is maintained throughout the data pull (finishes all 5.6 million rows in ~4.5 minutes)

I set up another package that pulled a large table from an Oracle database (with SSIS) and the speed remained constant.  

So it is just the Sybase setup with SSIS.

I must have something setup incorrectly.  I just don't know what it might be.

Andy

December 14, 2007 7:40 PM
 

Andy said:

Well, this is beyond frusctrating.  Way to many hours spent trying to resolve this issue.  

I thought for a minute it might be the newer Sybase Client install (12.5) I did to get the Sybase OLE DB providers.  So I set up the data pull in a SQL Server 2000 DTS package using the same exact OLE DB setup and the slowdown does not occur.  

This seems to be directly related to using SSIS.  There is not a whole lot of options to alter on the source side of a data pull.  There is the connection configuration, which is the same for both 200 and 2005 and a ValidateExternalMetadata option on the OLE DB Source object that seems to have no effect on speed.   If I missing some setting that I haven't noticed let me know.

Thanks,

Andy

December 17, 2007 8:52 PM
 

jamie.thomson said:

Andy,

Where exactly is the the bottleneck? Did you use the techniques described in the OVAL webcast to determine where they are?

-Jamie

December 17, 2007 9:19 PM
 

Andy said:

Yes I watched and used the tecniques in the webcast.  The bottleneck is in the Source.  But the bottleneck doesn't happen right away.  It slows down as the transfer occurs.  This does not happen in a 2000 DTS package.  Same exact source.  Slows down to a crawl after initially going fast to start with SSIS.  Starts and stays fast with a 2000 DTS package.

December 18, 2007 3:00 PM
 

jamie.thomson said:

Andy,

Just some extra clarification. Do you get the same behaviour if you (say) push the data straight from the source into a Rowcount component?

-Jamie

December 18, 2007 3:06 PM
 

Andy said:

Well I'll be a monkey's uncle.  Lowering the DefaultBufferMaxRows to 1000 performed the magic I was looking for.   It has performed the transfer in 3 minutes twice now.

Odd, I thought I had tried this before (kind of like checking your pocket for a 3rd time and finding your keys).  

Plus I moved the process to the prod server which has 10 times the memeory, 4x the CPUs and 3 times the processor speed and did not see speed at 10,000 DefaultBufferMaxRows.

I'm assuming the slowdown has to be due to paging memory, odd that I did not see a spike in memory when it was running.   Maybe it's a network thing?  That would be the same for dev and prod.

Well, doesn't matter now I guess.  80-100 hours used trying every imaginable thing, or at least so I thought.

Thanks for responding,

Andy

December 18, 2007 6:18 PM
 

Andy said:

(just noticed your post)  Yes I was putting the source directly to a row count component.  

December 18, 2007 6:19 PM
 

Andy said:

I suppose one more thing that it could have been was the server that Sybase was on could have been paging.  Did not have access to notice that.  (Given I did not notice issues with the memory on the SSIS server(s).

December 18, 2007 6:37 PM
 

jamie.thomson said:

Hey that's great news Andy. A lesson in there for all of us. Keep perservering.

Am I right in thinking that you got it down from 26 minutes to 3 minutes?

-Jamie

December 18, 2007 7:02 PM
 

Andy said:

Yes, that perticular table is down from 26 minutes to 3 minutes.  (Got rid of the slowdown there)

Unfortunately the 9 million row table is still witnessing slwodown.  Just 3 integer columns being extracted there.  Still trying different settings to remove the slowdown...

December 18, 2007 7:45 PM
 

jamie.thomson said:

Andy,

Was the 3-minute figure achieved executing the package in BIDS or from dtexec.exe?

Executing using dtexec.exe should be significantly faster.

-Jamie

December 18, 2007 7:45 PM
 

SSIS Junkie said:

I believe it can be very useful to share success stories and case studies, particularly when a seemingly

December 18, 2007 7:56 PM
 

jamie.thomson said:

Andy,

I hope you don't mind but I've written up your experiences here: http://blogs.conchango.com/jamiethomson/archive/2007/12/18/SSIS_3A00_-A-performance-tuning-success-story.aspx

-Jamie

December 18, 2007 7:59 PM
 

Andy said:

The 3 minute figure was run from dtexec.exe but the source is the full allotment of time as I am loading to a Row Count.  (No increase in time when running without debug)

On a side note, I discovered the issue with the other table.  The rows I was pulling in were not unique.  It was taking 18 minutes to finish up with just the 3 integer columns I was pulling.  (9 million rows).  After adding a couple of columns the figure dropped to 3 minutes.  Realizing that made each row more unique, I grabbed the column to make all rows unique and it finished in just over 2 minutes.  (I may go back and do a distinct, don't believe I need the other piece of data)

So in summary:

- lowering the DefaultBufferMaxRows to 1000 eliminated the slowdown(less and less rows retreived each minute).  

- On one db source object I had a slowdown(again with the less and less rows each minute) due to non-unique rows.  

I don't mind if you want to write it up.  If you can save someone else time with this, go for it.  There really wasn't a good example out there.  The only other slowdown I read about on the web was due to someobody having indexes on their destination table.  

Thanks,

Andy

December 18, 2007 9:06 PM
 

Beny said:

Hi there ,

i used sql server deatination inestad of ole db destination , but i just kept geting the following error  "

[SQL Server Destination [312]] Error:

SSIS Error Code DTS_E_OLEDBERROR.  

An OLE DB error has occurred. Error code: 0x80040E14.

An OLE DB record is available.  Source: "Microsoft SQL Native Client"  

Hresult: 0x80040E14  Description:

"Could not bulk load because SSIS file mapping object

'Global\DTSQLIMPORT              '

could not be opened.

Operating system error code 2(The system cannot find the file specified.).

Make sure you are accessing a local server via Windows security.".

January 24, 2008 10:41 PM
 

jamie.thomson said:

Beny,

Please do me the courtesy of reading one of my blog entries before you reply to it. The answer is above.

-Jamie

January 24, 2008 10:54 PM
 

Raja said:

Thank you for this post

With the info u have provided and following the principles that are provided in the success story i was able to reduce the  exec times well over 80%

February 1, 2008 8:33 PM
 

Neil Squires said:

Thanks so much for your blog.  It helped me figure out to use a OLE DB destination rather than a SQL Server Destination.  Was making me pull my hair out when trying to get the SSIS package working with a remote SQL instance.

Neil

February 26, 2008 11:11 PM
 

Ade said:

I'll like to say a very big thank you to Jamie for this blog, to all the contributors and in particular Andy, for persevering through his performance issue.

It has been of great help.

March 20, 2008 1:54 PM
 

Harit Gohel said:

Thank you  Guys this really help me to solve my problem

March 27, 2008 9:28 PM
 

Puneet said:

Hi there,

I'm having nearly 40 Million of data rows in SQL SERVER 2005, which have to be migrated to the the other DB in the same SQL SERVER 2005.

I had seen the below URL to optimize the package:

http://blog.cybner.com.au/2008/03/optimising-ssis-for-large-data-loads.html

Right now it's taking 30 hours to migrate using lookups and the open rowset with fast load OLEDB destination (SQL Native Client).

How can I optimize it to migrate it in 1-2 hours.

Please suggest.

Thanks!

Puneet.

April 10, 2008 8:48 PM
 

jamie.thomson said:

Puneet,

I haven't got a clue. How on earth am I meant to be able to give you ans answer with the paucity of info here?

Have you looked at the SSIS Performance tuning paper (http://search.live.com/results.aspx?q=SSIS+Performance+tuning+paper&src=IE-SearchBox)?

Also, check out the OVAL principle: http://search.live.com/results.aspx?q=ssis+oval&form=QBRE

-Jamie

April 14, 2008 1:56 PM
 

Naren NS said:

Hi Jamie,

As you have mentioned above, BOL is indeed far from clear as to what really happens behind the scenes as far as the "SQL Server Destination" is concerned. As you say, I too assume that it is an in-memory object that is created. however, in my case, i do not see much memory being used with the SQl Server destination component in my package. Is there any particular performance counter/monitor that can be looked at to see if the SQL Server Destination component is using resources (disk/memory)  ?

I have tried looking at the pagefile and available memory and cant find much !

Regards,

Naren.

April 14, 2008 4:16 PM
 

jamie.thomson said:

Naren,

I don't know of any such counter, no. I would expect however that data enters and leaves that memory space as per the pipeline buffers so if there are 1000000 rows in the pipeline I wouldn't necassarily expect there to eb a point in time in which 1000000 rows are in memory.

-Jamie

April 14, 2008 4:44 PM
 

Naren said:

Hi Jamie,

Thanks for the information. Well, my question above was rather cryptic. Anyway, here's what my actual problems is:

I have an SSIS package that reads a flat file using a script component, runs a couple of lookups and eventually uses SQL server destination components to insert into a table.

The package when run against a particular file on our dev environment here provides a throughput of about 8000 rows per second.

However, when the same package is run on our QA box (which is a much stronger and better box), i  get a measly throughput of about 4000 rows per second.

WE have gone through the cycles of looking at any other databases/processes that might be slowing this down, but havent found any. We have also benchamarked the IO capacity of the disks and this doesnt yield any clues.

Eventually, a call was raised with microsoft about this and after 2 weeks of mails, stats and diags flowing back and forth, we have not become any wiser. All we have been hearing from MS is that the SQL Page IO latch times are longer on the QA box as compared to the DEV box. My question to them was to understand where this IO latch was taking longer (which drive etc) and they havent been able to help (as yet).

From your vast experience , willl you be able to perhaps, shed some light on this ?

Also, it would be nice if I could reach you an email so that I can share more details of my problem.

Thanks & Regards,

Naren.

April 15, 2008 3:29 PM
 

jamie.thomson said:

Naren,

Sorry, this sounds like something for which one would need to be actually there with you to diagnose. Nothing is sprining to my mind I'm afraid.

-Jamie

April 17, 2008 8:20 AM
 

Puneet said:

Hi Jamie,

I'm sorry for not able to describe my problem perfectly.

My exact problem is :

I have to migrate 40 Million data records from source table to destination table. Both the table are on SQL Server 2005.

While startup till 3.6 million it use to transfer in less than 1 hr but afterwards, it gets slow down and use to take 72 hours in fully migrating rest of the 36.4 million data.

I looked into the Performance Monitor, it transfers 30 k rows in 30 secs and after that the Performance Monitor shows "Avg. Queue Disk Length" taking 100% resources for around 1 min. By this time only 1k - 2k rows got transferred and afterwards, migration picks up the pace for the next 30 secs. And again, the loop gets started.

I hope this time I would have presented my problem clearly than before.

Puneet.

April 21, 2008 4:16 PM
 

jamie.thomson said:

Puneet,

I'm afraid I simply don't know. How can I possibly know better than you?

Have you tried the tips in the performance tuning whitepaper that I linked to before? Have you attempted to determine where the bottleneck is using the OVAL tips?

From the Performance Monitor it could be that disk IO could be your bottleneck but again I reiterate that I cannot say for sure.

-Jamie

April 21, 2008 4:22 PM
 

Bill said:

I'm running a package that uses SQL Server Destinations.  I am running the package on the same server that the inserted DB objects reside on.  

the package ran fine once.  The second time, it failed with the error: "Unable to propare the SSIS bulk insert for data insertion".

I already know that the accepted work around is to switch to an OLE DB Destination.  But I would like to know if there is a REAL SOLUTION, not just a work around.  I would like to keep using the SQL Server Destination because of the performance benefit.

Any insights on this issue?

Bill

April 25, 2008 6:46 PM
 

jamie.thomson said:

Bill,

Sorry Bill, that's a new one on me. I think you'll have to dig a bit deeper on that one.

Run Profiler to see what activity you can see when t works and compare that to what you get when it doe not.

-Jamie

April 27, 2008 7:37 PM
 

Chris said:

I have a problem with a fairly large load - 3 million rows, 1.1 GB.  Data Reader Source pulling from an ODBC connection to Sybase.  OLE DB Destination with SQL Native Client using fast load.  Tried Rows per Batch = 0 & Maximum insert commit size = 0 & with both values equal to 10,000.  After about 2.1 million rows are loaded in debug mode I receive the following three messages.  The package is running on the SQL server, with about 12 GB of Ram, half of which is in use.  The problem appears to be coming from the data source.  Any ideas?

Thanks,

Chris

Information: 0x4004800D at Contact Data Flow Task, DTS.Pipeline: The buffer manager failed a memory allocation call for 10223616 bytes, but was unable to swap out any buffers to relieve memory pressure. 7 buffers were considered and 7 were locked. Either not enough memory is available to the pipeline because not enough are installed, other processes were using it, or too many buffers are locked.

Error: 0xC0047012 at Contact Data Flow Task, DTS.Pipeline: A buffer failed while allocating 10223616 bytes.

Error: 0xC0047011 at Contact Data Flow Task, DTS.Pipeline: The system reports 52 percent memory load. There are 12883918848 bytes of physical memory with 6157287424 bytes free. There are 2147352576 bytes of virtual memory with 754827264 bytes free. The paging file has 16942628864 bytes with 10294919168 bytes free.

May 1, 2008 11:51 PM
 

jamie.thomson said:

May 2, 2008 10:27 AM
 

SSIS User said:

I need to transfer data from a table in SQL Server to a file in AS 400 server. I am using the provider Microsoft OLE DB provider for DB2.

When trying to execute the package I am getting the following error message:

Error: 0xC0202009 at Data Flow Task, OLE DB Destination [3643]: An OLE DB error has occurred. Error code: 0x00040EDA.

Error: 0xC0209029 at Data Flow Task, OLE DB Destination [3643]: The "input "OLE DB Destination Input" (3656)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (3656)" specifies failure on error. An error occurred on the specified object of the specified component.

Error: 0xC0047022 at Data Flow Task, DTS.Pipeline: The ProcessInput method on component "OLE DB Destination" (3643) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0209029.

Initially I thought like this is a casting issue. But I tried transferring data from one column alone. Then also I am getting the error. The package gets failed in OLE DB Destination. It gets successfully executed in OLE DB source. Expecting a reply on this.

July 23, 2008 7:47 AM
 

Chris said:

Jamie, thanks for the post!  Though you wrote it some time ago, its still very relavant today.  Its very torough and love that you included your source links for more detail.  

I'm an OLE DB Destination guy myself, and I second the notion that BOL should better document some of the "Microsoft Magic" better... if only to state that its reserved for specific situations andusupported otherwise.

August 15, 2008 5:26 PM
 

Fiorella said:

I have a store procedure that copys data from one table in a server to another table in a different server. I want to use SSIS instead of a store procedure to see if it will do it faster. I implemented a package but it takes the same time as the store procedure. I use OLE  DB source and OLE DB destination. Can anyone give me a hint on what should be done to make this quicker. Thank You

August 19, 2008 3:17 PM
 

parke said:

thank youu

September 1, 2008 3:12 PM
 

rüya tabirleri said:

thank youu

September 1, 2008 3:13 PM
 

bobthecoder said:

Wow, thats fantastic information to have. Thanks for highlighting the differences. Its going to make the deployment a lot simpler with the information you have provided. Understanding what goes on under the covers is key. Keep up the blogs!!

Rob

September 16, 2008 7:03 PM
New Comments to this post are disabled

This Blog

Syndication

Powered by Community Server (Personal Edition), by Telligent Systems