Bulk Insert vs. Data Flow Task (different row results using flat file source)

I'm importing a large csv file two different ways - one with Bulk Import Task and the other way with the Data Flow Task (flat file source -> OLE DB destination).

With the Bulk Import Task I'm putting all the csv rows in one column. With the Data Flow Task I'm mapping each csv value to it's own column in the SQL table.

I used two different flat file sources and got the following:

Flat file 1: Bulk Import Task = 12,649,499 rows; Data Flow Task = 4,215,817 rows
Flat file 2: Bulk Import Task = 3,403,254 rows; Data Flow Task = 1,134,359 rows

Anyone have any guess as to why this is happening?

It seems that there's a factor of 3 in between the two components, which could mean that Flat File Connection Manager is interpereting 3 rows as 1. This might be related with the row delimiter in the Flat File connection manager.

Can you try to use preview in the Flat File connection manager, and see if rows show up correctly?

|||Yeah, thanks for the reply. I've checked the preview and it looks fine. I've also tracked down some of the skipped rows and there's nothing signficant about them. They look exactly the same as all the others. Perhaps there is a hidden character or something? Is this possible?|||

Can you compare the rows imported by FlatFile with the rows imported by Bulk insert task and see if there is any difference between them?

Thanks,
Ovidiu Burlacu

|||yes, i can do that and the resolution is that they look exactly the same. ug. there really is no indication i can see to why a rows get dropped. they are not the same type, they don't have rows before them or after them with any significant difference. it's really very puzzling.|||

Can you put a data viewer in your pipeline and see what rows pass through at execution time?

Thanks,
Ovidiu Burlacu

Bulk insert in to a partitioned View?

Greetings once again my SQL friends,

I am getting the following error when I attempt to complete my data flow task. The destination is a partitioned view but I get the following error message when I run the package :

Partitioned view 'PRICE_DIM' is not updatable as the target of a bulk operation

How to solve this problem?

Hi,

Bulk insert operations are not supported for partitioned views. See for more details:

Exporting Data from or Importing Data to a View
http://msdn2.microsoft.com/en-us/ms187086.aspx

You can import Data within a Data Flow Task into a Partitioned View if you use an OLE DB Destination with the Data Access Mode option set to Table or View instead of "Table or View - fastload", which is the default and technically a bulk operation.

Please be aware that locally partitioned views are supported in SQL Server 2005 only for backward compatibility. See also:

Scenarios for Using Views
http://msdn2.microsoft.com/en-us/library/ms188250.aspx

I hope that helps,

Bertil

Bulk insert in SSIS

Has anyone else had this problem?

I am using 'OLE DB Destination' task in a data flow. When I select 'Table or view - fast load', check 'Table lock' and 'Check constraints' and run the flow, I get only one row inserted. The data flow edge shows hundreds of thousands of rows flowing to destination and the task completes (turns green).

The problem goes away if I uncheck bulk load (e.g., select 'Table or view').

Is fast load using bulk insert or some internal code?

?

That is weird. Have you tried to set a value for 'Maximux insert commit size'?

As far as I know SSIS uses bulk inserts when fast load is selected.

Use SQL Server profiler to see the databse activity while the package is being run and insepect the results of the progress tab during the debug session...

Other than that; no idea

Rafael Salas