Data Convesrion Task + OLE DB Destination is too slow

Question

Data Convesrion Task + OLE DB Destination is too slow

JMI

SSChampion

Points: 11831
More actions
January 26, 2009 at 10:14 am

#217953

I have a Data Conversion Task
that converts data from raw table SRC_CUSTOMER where all the fields are varchar(255)
to real data types. Then the records that passed the conversion
get inserted into STG_CUSTOMER_INTEG table.
It is very slow.
SRC_CUSTOMER has about 850,000 records
It's been running for 35 min and so far it processed only 667,000 records.
Is there any way to increase the performance for this SSIS package?
I checked performance on the SQL Server where SSIS is doing work
and it looks OK. 3,000 records were updated in less than 1 sec.
So it's SSIS that is slow.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

COZYROC One Orange Chip Points: 28499 More actions · Answer 1

riga1966 (1/26/2009)
I have a Data Conversion Task
that converts data from raw table SRC_CUSTOMER where all the fields are varchar(255)
to real data types. Then the records that passed the conversion
get inserted into STG_CUSTOMER_INTEG table.
It is very slow.
SRC_CUSTOMER has about 850,000 records
It's been running for 35 min and so far it processed only 667,000 records.
Is there any way to increase the performance for this SSIS package?
I checked performance on the SQL Server where SSIS is doing work
and it looks OK. 3,000 records were updated in less than 1 sec.
So it's SSIS that is slow.

The standard OLEDB Destination component is really slow. Try using "SQL Server Destination" component.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

JMI SSChampion Points: 11831 More actions · Answer 2

I also noticed in my OLE DB Destination

in "Data access mode" I used "SQL command" option to make mapping easier so that

I only see the columns to which I insert. Not all the columns.

Maybe this was slowing down the INSERT too?

COZYROC One Orange Chip Points: 28499 More actions · Answer 3

riga1966 (1/27/2009)
I also noticed in my OLE DB Destination
in "Data access mode" I used "SQL command" option to make mapping easier so that
I only see the columns to which I insert. Not all the columns.
Maybe this was slowing down the INSERT too?

Actually this should make it faster because it will deal only with the selected by you columns. The OLEDB destination component is slow because it does use "INSERT INTO ..." statements for each and every record insertion. This is terribly slow.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

JMI SSChampion Points: 11831 More actions · Answer 4

I tried SQL Server Destination component

and got an error:

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.".
Error: 0xC0202071 at dft_SRC_CUSTOMER_copy, SS_DST [18098]: Unable to prepare the SSIS bulk insert for data insertion.

I don't understand.

Why it's mentioning bulk insert?

What exactly this "SQL Server Destination" is?

COZYROC One Orange Chip Points: 28499 More actions · Answer 5

riga1966 (1/27/2009)
I tried SQL Server Destination component
and got an error:
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.".
Error: 0xC0202071 at dft_SRC_CUSTOMER_copy, SS_DST [18098]: Unable to prepare the SSIS bulk insert for data insertion.
I don't understand.
Why it's mentioning bulk insert?
What exactly this "SQL Server Destination" is?

This is SQL Server bulk-load destination component. It is much-much faster. However it has one limitation. Your package should execute on the same server where your database is running.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

JMI SSChampion Points: 11831 More actions · Answer 6

Yes.

Data access mode "SQL command" is a really bad option.

Once I switched to "table or view - fast load"

it took 30 sec to insert 200,000 records.

It's good enough for me.

So unless I figure out how to use "SQL Server Destination"

I'll stick to OLE DE Destination for now...

Thank you CozRoc!

I read your latest answer.

In my case dtexec will be running on a different server.

Anyway. Thank you very much for your help.

jai_har SSC Rookie Points: 43 More actions · Answer 7

riga1966 (1/27/2009)
I tried SQL Server Destination component
and got an error:
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.".
Error: 0xC0202071 at dft_SRC_CUSTOMER_copy, SS_DST [18098]: Unable to prepare the SSIS bulk insert for data insertion.
I don't understand.
Why it's mentioning bulk insert?
What exactly this "SQL Server Destination" is?