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 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

Build Report Model from Analysis Service Database

I try to build a report model that have data source point to AS Database, but
i can't.
This is what i try: change the provider to OLE Db AS 9.0 and select the AS
database, but the data source view can't detect a data source.
another way i try is using an existing AS project, but the project combo box
is empty.
Pls let me know way to do this. Thank you.Just to add-on: I am using Microsoft SQL Server 2005 CTP version
"Chai Ling" wrote:
> I try to build a report model that have data source point to AS Database, but
> i can't.
> This is what i try: change the provider to OLE Db AS 9.0 and select the AS
> database, but the data source view can't detect a data source.
> another way i try is using an existing AS project, but the project combo box
> is empty.
> Pls let me know way to do this. Thank you.