Import data from SQL Server 2000 incredibly slow

I have a problem with bad perfomance with my import of data from a SQL Server 2000 database. I use an OLEDB datasource in my SSIS package to connect to the sql server 2000 database. My 2005 server runs 64bits but i dont think this is an issue. With this configuration the import is VERY slow, we are talking about 40+ minutes to get 3.5 million rows with about 20 columns. When i create a test DTS package on the SQL Server 2000 server itself and run it, its blazingly fast. Has anyone run into something similar?

Have you done any debuggin of where the bottleneck occurs? Donald Farmer explains some useful ways to do this, see here:

SSIS: Donald Farmer's Technet webcast
(http://blogs.conchango.com/jamiethomson/archive/2006/06/14/4076.aspx)

-Jamie

|||I had a look at the webcast. While it contains a lot of good stuff on optimization it doesnt really help me in this particular case. I tested the SSIS package against a mirror of the production server on another box, and it ran about 10X faster. So there has to be somenthing with the way the server is set up.

Import csv files to Sql Server problem

Hi,

I try to import csv files to Sql Server using .net. The code is as following:

string strCsvConn =@."Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\;Extended Properties='text;HDR=Yes;FMT=Delimited(,)';";

using (OleDbConnection cn =newOleDbConnection(strCsvConn))

{

string strSQL ="SELECT * FROM " + strFileName;

OleDbCommand cmd =newOleDbCommand(strSQL, cn);

cn.Open();

using (OleDbDataReader dr = cmd.ExecuteReader())

{

// Bulk Copy to SQL Server

using (SqlBulkCopy bulkCopy =newSqlBulkCopy(strSqlConn))

{

bulkCopy.DestinationTableName = strSqlTable;

bulkCopy.WriteToServer(dr);

}

}

}

And the data is as following (simplified):

Model,Serial

AFICIO 3045,K9464900965

AFICIO 3045,K9464900932

Fax 5510L,A3761290041

Fax 2210L,A4978800008

AFICIO 3025,K8565201014

AFICIO 3025,K8565102398

The result of the 2nd column is: 9464900965, 9464900932, null, null, 8565201014, 8565102398 - either the first character is missing or the whole entry is missing.

One more weird thing is that some other files work fine, though I am not able to tell any difference between them.

Any idea is hoghly appreciated.

shz

Hmmm... what's the datatype / size of the second column in your destination database?

In your example, the two rows that get null in second column contains a space in the first column. Is it maybe because space is used as a separator too in some way?|||

Thanks johram,

The datatype is varchar(50). However, I am afraid it has nothing to with the database, because it is the DataReader that retrieves wrong data. I test the DataReader with the following code:

while (dr.Read())

{

string str =Convert.ToString(dr[1]);

}

And all entries in the 1st column contain a space. The file contains more than 10 columns actually, all other columns are good.

Some more findings:

I have some "good" files that work fine and some "bad" files that have this problem - I cannot tell any difference between them in terms of data format. If I copy some records from a "good" file to a "bad" file, those records become bad. If I copy some records from a "bad" file to a "good" file, those records become good.

If I use TDS to import the files to SQL Server, it works fine. Excel can open the files properly too.

Thanks,

shz

|||

It seems to be the problem in the header (columns) of the cvs file.

Please make sure thay are okay.

Good luck.

|||

Fixed - need a Schema.ini file to define the Extended Properties of the driver.

Thanks to everyone.

import a text file into the database

hello,
i want to import a textfile in the database (SQL)
For access.database the code like:
cnn.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=e:\My Documents\DB1.mdb;" & _
"Jet OLEDB:Engine Type=4;"
sqlString = "SELECT * INTO [tblSample2] FROM
[Text;HDR=NO;DATABASE=e:\My Documents\TextFiles].[Sample2.txt]"
cnn.Execute sqlString
works ok
How can i use such a command for the sql server
sqlString = "SELECT * INTO [tblSample2] FROM
[Text;HDR=NO;DATABASE=e:\My Documents\TextFiles].[Sample2.txt]"
i get the error
"Invalid object name 'Text;HDR=NO;DATABASE=e:\My
Documents\TextFiles\Sample2.txt'."
best regardsYou can use a passthrough query like:
INSERT tbl
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="e:\My Documents\TextFiles].[Sample2.txt]";
User ID=Admin;Password=;Extended properties=text )
Another option is to use xp_cmdshell directly like:
INSERT tbl
EXEC master..xp_cmdshell 'TYPE e:\My Documents\TextFiles].[Sample2.txt]" ;
For complex ascii files you should consider using BULK INSERT/BCP IN or
DTS( Data transformation Services ). See SQL Server Books Online for details
on these utilities.
Anith|||hello Anith,
i tryed to run in the query analyser the command
SELECT * FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data
Source="e:\transfer\Input\t1.txt";Extended properties=text')
but i get the syntax error
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ')'.
any idea?
thanks
Xavier
"Anith Sen" wrote:

> You can use a passthrough query like:
> INSERT tbl
> SELECT *
> FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source="e:\My Documents\TextFiles].[Sample2.txt]";
> User ID=Admin;Password=;Extended properties=text )
> Another option is to use xp_cmdshell directly like:
> INSERT tbl
> EXEC master..xp_cmdshell 'TYPE e:\My Documents\TextFiles].[Sample2.txt]" ;
> For complex ascii files you should consider using BULK INSERT/BCP IN or
> DTS( Data transformation Services ). See SQL Server Books Online for detai
ls
> on these utilities.
> --
> Anith
>
>