hello

actually i have to import a text file into SQLServer2000 database
table. i can do it from Enterprise manager of SQLServer2000 using DTS
Import/Export Wizard. But i want to use SQL Server's DTS Import/Export
Wizard's functionality thorugh C# application without using DTS wizard
from Enterprise Manager of SQL Server.
i dont want to go to SQL Server Enterprise manager and import file from
there i want to use SQLDMO and perform the same task through C#
application without using SQLServer's Enterprise Manager and DTS
Import/Export Wizard.

i'll be very thankful if anyone helps me. if u dont have the exact
solution but can tell the website or link or any forum then plz do tell
me.

if this is not the correct forum then plz guide me to the correct forum
there i can get help.

you can send me mail at (adnan.develo***@gmail.com).

Best Regards.

Hi Adnan,

I'm not quite sure I fully understood your question, but it looks like you
might want to consider a "bulk insert" .  You can create a sql proc that does
the bulk insert from text to a sql table and then call it from within your
c#. No wizard are used. Books online will decribe the bulk insert command.

Hope that helps!

Show quoteHide quote

thanks for sparing time for me.

actually i have log files that are very huge sized like 700MB or even
sometimes these reach upto GBs.
i want to extract information from these files u can say i want to
apply filters to get only required rows. log files are maintained by
IIS and contain space seperated fields in each row and there are
millions of rows.
now currently i m reading file line by line and inserting that line
into a database table. after that i select data from that table using
different filtering criteria and then binding that selected data to a
data grid.
now the problem is when i use huge sized files the process becomes very
slow and user hav to wait for a long time so that the file to be read
line by line and get inserted into database line by line.
there are different sample codes that are related to execute DTS
packages. i want to get the functionality of DTS from C#. i mean i want
to use SQL Server objects and libraries to perform SQL server related
tasks from my application.
i hope that now u hav understood the scenario. the other thing is if i
create a DTS package from sql server and while executing it i want to
change its attributes like file name at run time so that i give file
name as a parameter and the package with that file name get executed.
if u still hav confusions abt understanding scenario then plz do tell
me.

thanks.
best regards.

I'd still suggest using either a bulk insert or bcp.   After your last
explanation, maybe bcp would be better.  It'll definitely be faster than
inserting a row at a time.  First of all, you can do massive inserts without
logging which will reduce overhead and speed the process.  Second, you can
write your C# code to change the bcp command line, for example, change the
table name or the test file path/name according to whatever parameters you
want.  In your program, write the bcp command to a windows .bat file then
execute a shell to run the bat if that makes more sense.  That way you have
the flexibility to change the insert parameters on the fly and you get the
speed of bcp inserts.

Other than performing an import from text to sql table, what do you mean by
this statement?

"i want to get the functionality of DTS from C#. i mean i want
to use SQL Server objects and libraries to perform SQL server related
tasks from my application."

Show quoteHide quote

hy

thanks again :-)
r u talking about SqlBulkCopy? is it BCP? as far as i know about
it(SqlBulkCopy), it is supported in version 2 of Dot net and in my
company we r using version 1.1. so if BCP is SqlBulkCopy then it is not
possible now. and if this is other thing then i m going to check it and
plz i'll b very thankful to u if u provide me related web links or
material along with ur valuable suggestions.
as far as my statement is concerned actually i was going to use SQLDMO
and finding solution via SQLDMO. at present i hav to only import txt
files but in future may b i need it.
i m really thankful to u that u spared ur time for me because i have
posted this problem at different forums but u r the only ho replied.
my email id is "adnan.develo***@gmail.com" if u like to attach some
useful material.

best regards.

BCP is very much like "bulk copy" technically, but BCP is a dos command.  You
can read all about it in the Sql Server Books Online. 

What I'm suggesting is that you can write your C# code to dynamically create
or edit a BCP command in a .bat file (technically it's just a text file). 
You can manipulate the file/folder name or even the sql server table name
that the BCP will load if that makes sense in your application.  You can set
it to load a table from a formatted or delimited file.  For example, you said
you have a space separated file.

bcp is pretty easy to set up and runs quickly. 
A simple example of a bcp command would look like this:
bcp "database1.dbo.tbl_target_table" in "c:\foldername\file1.txt"
-c /S"servername" /U"userid" /P"userpassword" -f"c:\foldername\formatfile.fmt"

In this example, the process is to load an existing sql table
(tbl_target_table) from a text file (file1.txt) using a format file that you
pre-created (formatfile.fmt)

There are many options for BCP depending on your needs.  You would be
interested in the -t (field terminator character) and the -r (row terminator
character) which is where you'd tell it that a space is a field terminator
and a Cr/LF is the row terminator.  As long as you can run a dos command from
within C#, you can do this.  BCP is part of Sql Server, not .Net per se, so I
don't think you'll have any problems with your version.


Show quoteHide quote

hello

how r u tthrone?
i hav read ur suggestion and i think it is v close to my solution but
one thing that i hav to telll u that i m not an expert of .net i m new
to it.
as i found abt BCp it is a command line utility as u also told. now the
problem is that from where i can execute it? i mean i m going to make a
GUI windows based application and i m confudsed that how to implement
it in my windows based application. plz help me regarding:
if BCP is a command line utility then fromn which command prompt i can
execute it? .net command prompt or which.
and how and where i hav to use it in my application. i dont want my
user to go to any command prompt.
actually as it looks i m not clear about BCP that how it works and how
it is used.
i'll b very very thankful to u if u help me in this regard because i
have already taken very much time from company for this.
plz if it is possible then do provide me some sort of code or at least
try to clear the concept that what steps should b taken to use BCP from
my C# application.
or if u know the link of such a web page then plz tell me because i hav
searched my best  but couldnt find satisfactory help. i hav already
searched MSDN library it only tell about BCP but doesnt tell that how
to use it in my application. it just tells its syntax and parameters
details.

best regards.

Adnan Akram.

hello

how r u tthrone?
i hav read ur suggestion and i think it is v close to my solution but
one thing that i hav to telll u that i m not an expert of .net i m new
to it.
as i found abt BCp it is a command line utility as u also told. now the
problem is that from where i can execute it? i mean i m going to make a
GUI windows based application and i m confudsed that how to implement
it in my windows based application. plz help me regarding:
if BCP is a command line utility then fromn which command prompt i can
execute it? .net command prompt or which.
and how and where i hav to use it in my application. i dont want my
user to go to any command prompt.
actually as it looks i m not clear about BCP that how it works and how
it is used.
i'll b very very thankful to u if u help me in this regard because i
have already taken very much time from company for this.
plz if it is possible then do provide me some sort of code or at least
try to clear the concept that what steps should b taken to use BCP from
my C# application.
or if u know the link of such a web page then plz tell me because i hav
searched my best  but couldnt find satisfactory help. i hav already
searched MSDN library it only tell about BCP but doesnt tell that how
to use it in my application. it just tells its syntax and parameters
details.

best regards.

Adnan Akram.

Adnan,

Sorry, I can't help you with C# code.  I don't use it.  But I've done
similar processes from VB.  I suggest you try a different MS community for C#
programming help.  Ask about opening a Dos command shell from c#.  I'm sure
you can do this, and it's probably not very difficult. 

Microsoft's sites will mostly just describe the command and options, as you
found.  They will not help much if you are looking for specific code.  But,
there are some good IT community sites other than Microsoft that offer tips
and code for just about any languange.  You can search for them on the web
probably find something useful quickly.  Use your favorite search engine and
find "C# programming examples"  Here's one I like to use: www.ittoolbox.com

You need to know two main programming concepts: 1 how to create/update a
text file from within c#.   2: how to open a dos command shell and execute a
..bat or .cmd command file. 

To use it, your application must know how to set up the BCP.  that is, your
app must know the pathname/file name of the the text data file an of the BCP
..bat file.  That's up to you to determine.  Next, you would need to write a
BCP command to the .bat file using the the path/file name of the data, and
the SQL server, database, table, etc. that will be loaded.  Last, you need to
open a dos command shell, and tell it to execute the BCP .bat file.  All of
this can be done in the application without the user intervention if that's
what you need to do.

Good luck.

Show quoteHide quote

thanks alot tthrone.
i m very grateful 2 u. i m doing at the same track now and searching 4
how to execute dos command from C# windows based application.
hopefully i'll do it now (inshaAllah).
best regards for u.
take care.
bye.

http://www.codeproject.com/useritems/DTS__VBNET_.asp

Default Values for a Column
case expression in where clause and null's
T-SQL problem.
Using YEN sign in MONEY field
Performance of EM