Need to bulk insert quickly with .NET

Hi all, I hope this is the correct place for this post.
I need to write a service that will bulk insert 3 million+ records from a
fixed width formatted text file to an SQL Server table. When I started
importing these records I used Access to put the records into a table and
DTS to get the table into SQL server. This process took maybe 1/2 hour.
I then tried using .NET to import the data by grabbing the textfile into a
datatable in batches of 250 records and then updating to the SQL server
database with a DataAdaptor. This process took about 5 hours which is kind
of unacceptable.
Is there anyway to speed up this process? Can .NET access any bulk insert
functionality? I need to automate this process into a service and would
like to get it to run on the same order as the manual process, and I would
like to use a service programmed in .NET. If I could even call SQL to acess
bulk insert functionality on SQL Server that would be fine, but SQL Server
does not seem to support fixed width text files.ADO.NET doesn't have a built-in bulk insert capability. As I see it you have
several options to do this. But the best bet is to use the SQLCommand object
to call the T-SQL BULK INSERT statement. Other options include creating a
bcp format file and calling that from the .NET program. Likewise, you could
create a DTS package and call that use the .NET COM interop classes or you
could use DMO's BulkInsert object.
Michael O.
"Todd Burry" <tburry@.nospam.com> wrote in message
news:Xns94CAA6111D5C1mythstoddburrycom@.2
07.46.248.16...
> Hi all, I hope this is the correct place for this post.
> I need to write a service that will bulk insert 3 million+ records from a
> fixed width formatted text file to an SQL Server table. When I started
> importing these records I used Access to put the records into a table and
> DTS to get the table into SQL server. This process took maybe 1/2 hour.
> I then tried using .NET to import the data by grabbing the textfile into a
> datatable in batches of 250 records and then updating to the SQL server
> database with a DataAdaptor. This process took about 5 hours which is kind
> of unacceptable.
> Is there anyway to speed up this process? Can .NET access any bulk insert
> functionality? I need to automate this process into a service and would
> like to get it to run on the same order as the manual process, and I would
> like to use a service programmed in .NET. If I could even call SQL to
acess
> bulk insert functionality on SQL Server that would be fine, but SQL Server
> does not seem to support fixed width text files.|||Thanks for the help. I didn't realize that the BULK INSERT supports fixed
width files. That will work just fine.

Need to bulk insert quickly with .NET

Hi all, I hope this is the correct place for this post.
I need to write a service that will bulk insert 3 million+ records from a
fixed width formatted text file to an SQL Server table. When I started
importing these records I used Access to put the records into a table and
DTS to get the table into SQL server. This process took maybe 1/2 hour.
I then tried using .NET to import the data by grabbing the textfile into a
datatable in batches of 250 records and then updating to the SQL server
database with a DataAdaptor. This process took about 5 hours which is kind
of unacceptable.
Is there anyway to speed up this process? Can .NET access any bulk insert
functionality? I need to automate this process into a service and would
like to get it to run on the same order as the manual process, and I would
like to use a service programmed in .NET. If I could even call SQL to acess
bulk insert functionality on SQL Server that would be fine, but SQL Server
does not seem to support fixed width text files.
ADO.NET doesn't have a built-in bulk insert capability. As I see it you have
several options to do this. But the best bet is to use the SQLCommand object
to call the T-SQL BULK INSERT statement. Other options include creating a
bcp format file and calling that from the .NET program. Likewise, you could
create a DTS package and call that use the .NET COM interop classes or you
could use DMO's BulkInsert object.
Michael O.
"Todd Burry" <tburry@.nospam.com> wrote in message
news:Xns94CAA6111D5C1mythstoddburrycom@.207.46.248. 16...
> Hi all, I hope this is the correct place for this post.
> I need to write a service that will bulk insert 3 million+ records from a
> fixed width formatted text file to an SQL Server table. When I started
> importing these records I used Access to put the records into a table and
> DTS to get the table into SQL server. This process took maybe 1/2 hour.
> I then tried using .NET to import the data by grabbing the textfile into a
> datatable in batches of 250 records and then updating to the SQL server
> database with a DataAdaptor. This process took about 5 hours which is kind
> of unacceptable.
> Is there anyway to speed up this process? Can .NET access any bulk insert
> functionality? I need to automate this process into a service and would
> like to get it to run on the same order as the manual process, and I would
> like to use a service programmed in .NET. If I could even call SQL to
acess
> bulk insert functionality on SQL Server that would be fine, but SQL Server
> does not seem to support fixed width text files.
|||Thanks for the help. I didn't realize that the BULK INSERT supports fixed
width files. That will work just fine.

Need to add date during bulk import from csv

I'm downloading an wly retailer statement from our state's website in csv
format so I can import it into a SQL database and then use the data for some
custom reports. I've been able to successfully write my first bcp command to
import the file into an existing SQL database. However, I just realized that
the csv file I'm importing doesn't have any date information so I can select
database records based on a date. I've tried adding a Timestamp column at
the end of my database thinking it wouldn't interfere with the bulk import
but after I did such the import no longer worked. Does anyone have any idea
of how I can add a date field to each record during the import. Would BULK
INSERT allow me anymore flexibility? I could setup a user form where the
desired date is entered and then included with each record. Any suggestions
would be greatly appreciated.
Here's my bcp command:
bcp mydatabase.dbo.mytable in
c:\RetailerStatement.csv -c -t, -r\n -F3 -L9 -Smyservername -U -P
Thanks,
Barryyou want to create a datetime column with the default defined as getdate().
e.g.
alter table <tb> add crdate default getdate()
-oj
"BCS" <bswedeen@.tayloroil.com> wrote in message
news:kxikg.29114$JW5.5867@.southeast.rr.com...
> I'm downloading an wly retailer statement from our state's website in
> csv
> format so I can import it into a SQL database and then use the data for
> some
> custom reports. I've been able to successfully write my first bcp command
> to
> import the file into an existing SQL database. However, I just realized
> that
> the csv file I'm importing doesn't have any date information so I can
> select
> database records based on a date. I've tried adding a Timestamp column at
> the end of my database thinking it wouldn't interfere with the bulk import
> but after I did such the import no longer worked. Does anyone have any
> idea
> of how I can add a date field to each record during the import. Would BULK
> INSERT allow me anymore flexibility? I could setup a user form where the
> desired date is entered and then included with each record. Any
> suggestions
> would be greatly appreciated.
> Here's my bcp command:
> bcp mydatabase.dbo.mytable in
> c:\RetailerStatement.csv -c -t, -r\n -F3 -L9 -Smyservername -U -P
> Thanks,
> Barry
>|||No go. Here's the error I get:
#@. Row 1, Column 22: Invalid character value for cast specification @.#
The error repeats for all rows.
Barry
"oj" <nospam_ojngo@.home.com> wrote in message
news:O%23MGDlRkGHA.3588@.TK2MSFTNGP02.phx.gbl...
> you want to create a datetime column with the default defined as
getdate().
> e.g.
> alter table <tb> add crdate default getdate()
>
> --
> -oj
>
> "BCS" <bswedeen@.tayloroil.com> wrote in message
> news:kxikg.29114$JW5.5867@.southeast.rr.com...
command
at
import
BULK
>|||Try creating a view that selects all columns except
the (new) datetime column with the default date.
create view myview
as
select col1,col2,...
from mytable
Change your BCP command to import into this view instead.
bcp mydatabase.dbo.myview in c:\RetailerStatement.csv -c -t, -r\n -F3
-L9 -Smyservername -U -P|||1. my sample alter tb is missing 'datetime' datatype for the added column.
hopefully, you've caught that.
2. if your source cvs does not contain data for the datetime column, you
will need to exclude it by using a format file. you can, of course, use a
view as suggested by markc600.
-oj
"BCS" <bswedeen@.tayloroil.com> wrote in message
news:Hgxkg.29152$JW5.10677@.southeast.rr.com...
> No go. Here's the error I get:
> #@. Row 1, Column 22: Invalid character value for cast specification @.#
> The error repeats for all rows.
> Barry
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:O%23MGDlRkGHA.3588@.TK2MSFTNGP02.phx.gbl...
> getdate().
> command
> at
> import
> BULK
>|||My cvs file does not contain the datetime data and apparently the bcp
command won't work if I try to plug a default value in a datetime column in
my table or a timestamp column.
I've decided to import the csv file into an Excel workbook using a macro,
adding the date value, and then doing an INSERT into my database. I'm sure
using a VIEW as Mark suggested would have accomplished the same thing. The
Excel route gave me an opportunity to allow the user to verify the data
before making the final INSERT.
Thanks everyone!
Barry
"oj" <nospam_ojngo@.home.com> wrote in message
news:eiCqTuWkGHA.2200@.TK2MSFTNGP05.phx.gbl...
> 1. my sample alter tb is missing 'datetime' datatype for the added column.
> hopefully, you've caught that.
> 2. if your source cvs does not contain data for the datetime column, you
> will need to exclude it by using a format file. you can, of course, use a
> view as suggested by markc600.
> --
> -oj
>
> "BCS" <bswedeen@.tayloroil.com> wrote in message
> news:Hgxkg.29152$JW5.10677@.southeast.rr.com...
for
realized
column
any
>

Need permission to use bulk load statement in SQL 2005?

Hello,
I've developed a tool in Visual Basic.NET and have a local instance of SQL
Server 2005 running. This tool loads a lot of data into my database. It runs
locally just fine, but when I set it to load the data into the remote
(webhosting) database, I get an error: "You do not have permission to use th
e
bulk load statement."
Can anyone tell me what kind of permission that I need to use this
statement? Using a simple Insert state would take weeks and weeks to perform
the operation. The remote database at the hosting company has a bunch of SQL
Server 2005 databases on it. I'm sure that they won't want to give me admin
rights. Is there anyway that they can give us specifically rights to use a
bulk load statement without giving us access to all the other databases on
the server?
ThanksAdd the login to the bulkadmin fixed server role.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"John Riddle" <JohnRiddle@.discussions.microsoft.com> wrote in message
news:256E7437-06C4-4392-A5DA-C9DF50F70A58@.microsoft.com...
Hello,
I've developed a tool in Visual Basic.NET and have a local instance of SQL
Server 2005 running. This tool loads a lot of data into my database. It runs
locally just fine, but when I set it to load the data into the remote
(webhosting) database, I get an error: "You do not have permission to use
the
bulk load statement."
Can anyone tell me what kind of permission that I need to use this
statement? Using a simple Insert state would take weeks and weeks to perform
the operation. The remote database at the hosting company has a bunch of SQL
Server 2005 databases on it. I'm sure that they won't want to give me admin
rights. Is there anyway that they can give us specifically rights to use a
bulk load statement without giving us access to all the other databases on
the server?
Thanks

Need help with XML Bulk Load

I have a source xml file which I think is fairly complex. It s an EDIFACT
D96A INVRPT file.
I just need to bring the data within into a single table
CREATE TABLE InventoryOnHand (
InventoryDate nvarchar(10) DEFAULT (getdate()),
Barcode nvarchar(50),
Quantity float,
Warehouse nvarchar(50)
)
I've been fiddling with creating a schema to read the data, but I keep
getting various errors.
If anyone wants to help, let me know & I'll email you a sample source file
and my attempts at a schema. I'm getting desperate - I've spent so many
hours mucking around I'm at the end of my tether, so any assistance is
greatly appreciated!
cheers
DannyPlease send me your files and I'll take a look.
Regards,
--
Monica Frintu
"dc" wrote:

> I have a source xml file which I think is fairly complex. It s an EDIFACT
> D96A INVRPT file.
> I just need to bring the data within into a single table
> CREATE TABLE InventoryOnHand (
> InventoryDate nvarchar(10) DEFAULT (getdate()),
> Barcode nvarchar(50),
> Quantity float,
> Warehouse nvarchar(50)
> )
> I've been fiddling with creating a schema to read the data, but I keep
> getting various errors.
> If anyone wants to help, let me know & I'll email you a sample source file
> and my attempts at a schema. I'm getting desperate - I've spent so many
> hours mucking around I'm at the end of my tether, so any assistance is
> greatly appreciated!
> cheers
> Danny
>
>|||Monica,
Thanks for your offer. You can you ensure your email address is correct -
I'm getting NDRs.
cheers
Danny
dannyc@.accolade.com.au
"Monica Frintu [MSFT]" <MonicaFrintuMSFT@.discussions.microsoft.com> wrote in
message news:66FD8513-C76B-48B1-80AC-7BD50ED994F1@.microsoft.com...
> Please send me your files and I'll take a look.
> Regards,
> --
> Monica Frintu
>
> "dc" wrote:
>|||Monica,
This might help instead
My table is thus:
CREATE TABLE AA_InventoryOnHand (
InventoryDate nvarchar(10),
Barcode nvarchar(50) ,
Quantity float
)
My sample XML file is long, but copying to notepad and saving as
V3_INVRPT.XML should help someone to diagnose. Go down to the
============== line
<?xml version="1.0" encoding="UTF-8"?>
<EDIFACT_D96A_INVRPT xmlns="http://holoncorp.com/xml/EDIFACT/D96A/INVRPT"
xmlns:v3="http://holoncorp.com/xml/EDIFACT/D96A/INVRPT">
<UNB UNB010_0001_syntaxIndentifier="UNOA" UNB010_0002_syntaxVersion="3"
UNB020_0004_senderIdentification="VISA Sydney"
UNB020_0007_partnerIdentificationCodeQua
lifier="ZZ"
UNB030_0007_partnerIdentificationCodeQua
lifier="ZZ"
UNB030_0010_recipientIdentification="Barilla"
UNB040_0017_dateOfPreparation="070510" UNB040_0019_timeOfPreparation="1017"
UNB050_0020_interchangeControlReference=
"1020"/>
<INVRPT>
<UNH UNH010_0062_referenceNumber="0001" UNH020_0051_controllingAgency="UN"
UNH020_0052_versionNumber="D" UNH020_0054_releaseNumber="96A"
UNH020_0057_associationAssignedCode="EAN005"
UNH020_0065_typeIdentifier="INVRPT"/>
<BGM BGM010_1001_messageName="35" BGM020_1004_messageNumber="1020"
BGM030_1225_messageFunction="9"/>
<DTM DTM010_2005_dateTimePeriodQualifier="366"
DTM010_2379_dateTimePeriodFormatQualifie
r="102"
DTM010_2380_dateTimePeriod="20070510"/>
<GRP2>
<NAD NAD010_3035_partyQualifier="GY" NAD020_3039_partyIdIdentification="VISA
Sydney" NAD020_3055_codeListResponsibleAgency="86"/>
<GRP4>
<CTA CTA010_3139_contactFunctionCode="WH"
CTA020_3412_departmentOrEmployeeName="Jim Vikas"/>
<COM COM010_3148_communicationAddressIdentifi
er=""
COM020_3155_communicationAddressQualifie
r="TE"/>
<COM COM010_3148_communicationAddressIdentifi
er=""
COM020_3155_communicationAddressQualifie
r="EM"/>
</GRP4>
</GRP2>
<GRP2>
<NAD NAD010_3035_partyQualifier="GM"
NAD020_3039_partyIdIdentification="Cantarella"
NAD020_3055_codeListResponsibleAgency="86"/>
</GRP2>
<GRP9>
<LIN LIN030_7140_itemNumber="841158000029"
LIN030_7143_itemNumberTypeCode="EN"/>
<GRP12>
<INV INV040_4503_inventoryBalanceMethodCode="1"/>
<QTY QTY010_6060_quantity="144" QTY010_6063_quantityQualifier="17"
QTY010_6411_measurementUnitCode="EA"/>
<QTY QTY010_6060_quantity="0" QTY010_6063_quantityQualifier="170"
QTY010_6411_measurementUnitCode="EA"/>
<QTY QTY010_6060_quantity="0" QTY010_6063_quantityQualifier="253"
QTY010_6411_measurementUnitCode="EA"/>
</GRP12>
</GRP9>
<GRP9>
<LIN LIN030_7140_itemNumber="841158000043"
LIN030_7143_itemNumberTypeCode="EN"/>
<GRP12>
<INV INV040_4503_inventoryBalanceMethodCode="1"/>
<QTY QTY010_6060_quantity="166" QTY010_6063_quantityQualifier="17"
QTY010_6411_measurementUnitCode="EA"/>
<QTY QTY010_6060_quantity="2" QTY010_6063_quantityQualifier="170"
QTY010_6411_measurementUnitCode="EA"/>
<QTY QTY010_6060_quantity="0" QTY010_6063_quantityQualifier="253"
QTY010_6411_measurementUnitCode="EA"/>
</GRP12>
</GRP9>
<GRP9>
<LIN LIN030_7140_itemNumber="AZZURRA" LIN030_7143_itemNumberTypeCode="EN"/>
<GRP12>
<INV INV040_4503_inventoryBalanceMethodCode="1"/>
<QTY QTY010_6060_quantity="1000" QTY010_6063_quantityQualifier="17"
QTY010_6411_measurementUnitCode="EA"/>
<QTY QTY010_6060_quantity="30" QTY010_6063_quantityQualifier="170"
QTY010_6411_measurementUnitCode="EA"/>
<QTY QTY010_6060_quantity="0" QTY010_6063_quantityQualifier="253"
QTY010_6411_measurementUnitCode="EA"/>
</GRP12>
</GRP9>
<GRP9>
<LIN LIN030_7140_itemNumber="fab" LIN030_7143_itemNumberTypeCode="EN"/>
<GRP12>
<INV INV040_4503_inventoryBalanceMethodCode="1"/>
<QTY QTY010_6060_quantity="10" QTY010_6063_quantityQualifier="17"
QTY010_6411_measurementUnitCode="EA"/>
<QTY QTY010_6060_quantity="1" QTY010_6063_quantityQualifier="170"
QTY010_6411_measurementUnitCode="EA"/>
<QTY QTY010_6060_quantity="0" QTY010_6063_quantityQualifier="253"
QTY010_6411_measurementUnitCode="EA"/>
<GIN GIN010_7405_identityNumberQualifier="BX"
GIN020_7402_identityNumberRange="13"/>
</GRP12>
</GRP9>
<GRP9>
<LIN LIN030_7140_itemNumber="fab" LIN030_7143_itemNumberTypeCode="EN"/>
<GRP12>
<INV INV040_4503_inventoryBalanceMethodCode="1"/>
<QTY QTY010_6060_quantity="100" QTY010_6063_quantityQualifier="17"
QTY010_6411_measurementUnitCode="EA"/>
<QTY QTY010_6060_quantity="20" QTY010_6063_quantityQualifier="170"
QTY010_6411_measurementUnitCode="EA"/>
<QTY QTY010_6060_quantity="0" QTY010_6063_quantityQualifier="253"
QTY010_6411_measurementUnitCode="EA"/>
<GIN GIN010_7405_identityNumberQualifier="BX"
GIN020_7402_identityNumberRange="12"/>
</GRP12>
</GRP9>
<GRP9>
<LIN LIN030_7140_itemNumber="INSTANT" LIN030_7143_itemNumberTypeCode="EN"/>
<GRP12>
<INV INV040_4503_inventoryBalanceMethodCode="1"/>
<QTY QTY010_6060_quantity="1000" QTY010_6063_quantityQualifier="17"
QTY010_6411_measurementUnitCode="EA"/>
<QTY QTY010_6060_quantity="30" QTY010_6063_quantityQualifier="170"
QTY010_6411_measurementUnitCode="EA"/>
<QTY QTY010_6060_quantity="0" QTY010_6063_quantityQualifier="253"
QTY010_6411_measurementUnitCode="EA"/>
</GRP12>
</GRP9>
<GRP9>
<LIN LIN030_7140_itemNumber="KING OSCAR"
LIN030_7143_itemNumberTypeCode="EN"/>
<GRP12>
<INV INV040_4503_inventoryBalanceMethodCode="1"/>
<QTY QTY010_6060_quantity="1000" QTY010_6063_quantityQualifier="17"
QTY010_6411_measurementUnitCode="EA"/>
<QTY QTY010_6060_quantity="40" QTY010_6063_quantityQualifier="170"
QTY010_6411_measurementUnitCode="EA"/>
<QTY QTY010_6060_quantity="0" QTY010_6063_quantityQualifier="253"
QTY010_6411_measurementUnitCode="EA"/>
</GRP12>
</GRP9>
<GRP9>
<LIN LIN030_7140_itemNumber="sard brisling"
LIN030_7143_itemNumberTypeCode="EN"/>
<GRP12>
<INV INV040_4503_inventoryBalanceMethodCode="1"/>
<QTY QTY010_6060_quantity="1000" QTY010_6063_quantityQualifier="17"
QTY010_6411_measurementUnitCode="EA"/>
<QTY QTY010_6060_quantity="30" QTY010_6063_quantityQualifier="170"
QTY010_6411_measurementUnitCode="EA"/>
<QTY QTY010_6060_quantity="0" QTY010_6063_quantityQualifier="253"
QTY010_6411_measurementUnitCode="EA"/>
</GRP12>
</GRP9>
<UNT UNT010_0074_numberOfSegments="54" UNT020_0062_referenceNumber="0001"/>
</INVRPT>
<UNZ UNZ010_0020_interchangeControlReference=
"1020"
UNZ010_0036_interchangeControlCount="1"/>
</EDIFACT_D96A_INVRPT>
=======================
And here is the schema file I've written.
<?xml version="1.0" ?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"
xmlns:sql="urn:schemas-microsoft-com:xml-sql" >
<ElementType name="EDIFACT_D96A_INVRPT" sql:is-constant="1">
<element type="UNB" />
<element type="LIN" />
<element type="QTY" />
</ElementType>
<ElementType name="UNB" sql:relation="AA_InventoryOnHand" >
<AttributeType name="UNB040_0017_dateOfPreparation" dt:type="string"
/>
<attribute type="UNB040_0017_dateOfPreparation"
sql:field="InventoryDate" />
</ElementType>
<ElementType name="LIN" sql:relation="AA_InventoryOnHand" >
<AttributeType name="LIN030_7140_itemNumber" dt:type="string" />
<attribute type="LIN030_7140_itemNumber" sql:field="Barcode" />
</ElementType>
<ElementType name="QTY" sql:relation="AA_InventoryOnHand" >
<AttributeType name="QTY010_6060_quantity" dt:type="float" />
<attribute type="QTY010_6060_quantity" sql:field="Quantity" />
</ElementType>
</Schema>
==============================
And here's a vbs file I'm running
Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
objBL.ConnectionString = "provider=SQLOLEDB.1;data
source=DANNYCVM;database=MyDemoDB;uid=sa
;pwd=MysaPassword"
objBL.ErrorLogFile = "c:\error.log"
objBL.Execute "c:\Invmapping.xml", "c:\V3_INVRPT.xml"
Set objBL = Nothing
=============================
The table should be populated thus:
070510 841158000029 144
070510 841158000043 166
070510 841158000043 2
etc
"dc" <dannyc@.accoalde.com.au> wrote in message
news:uns4XWxkHHA.1624@.TK2MSFTNGP06.phx.gbl...
>I have a source xml file which I think is fairly complex. It s an EDIFACT
>D96A INVRPT file.
> I just need to bring the data within into a single table
> CREATE TABLE InventoryOnHand (
> InventoryDate nvarchar(10) DEFAULT (getdate()),
> Barcode nvarchar(50),
> Quantity float,
> Warehouse nvarchar(50)
> )
> I've been fiddling with creating a schema to read the data, but I keep
> getting various errors.
> If anyone wants to help, let me know & I'll email you a sample source file
> and my attempts at a schema. I'm getting desperate - I've spent so many
> hours mucking around I'm at the end of my tether, so any assistance is
> greatly appreciated!
> cheers
> Danny
>