Importing lots of rows :-)

Simon,
Try using SqlBulkCopy object (ado.net 2.0).
AMB
"Simon Harvey" wrote:

> Hi All,
> I'm not sure how to approach this problem so if anyone could advise me
> how to do it I would be very grateful.
> I have an app that calls a web service. The webservice returns an array
> of business objects.
> So I have a collections of objects that might be, say, 10000 objects in
> size.
> I need to import all the business objects into an SQL Server 2005
> database. The only way I know how to do this is to loop through each
> object and call a stored procedure repeatedly that takes the appropriate
> parameters. The whole import must be atomic. That is, if something goes
> wrong, the whole import needs to be aborted.
> I guess I have two questions:
> 1. Is repeatedly calling a stored procedure the best way to do this sort
> of stuff? I know doing imports like this must be a pretty common
> operation, but I don't know if using an SPROC is a dumb idea.
> 2. Is it ok to start a transaction and repeatedly call a SPROC, like,
> 10000 times? Is there a better what to go about this?
> Maybe I'm worrying about nothing - but it just "feels" wrong to be
> repeatedly calling an SPROC 10,000 times! :-) I was wondering if there
> is a more elegant way?
> Any advice anyone could offer would be very much appreciated.
> Kindest Regards
> Simon
>You're right. You don't want to call the same stored procedure 10,000 times
if there is way to accomplish the task as one unit of work. First off,
calling the stored procedure 10,000 times -while inside a transaction, will
consume and hold more resources longer than necessary.
Look into SQLBulkCopy as Alejandro offered
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:ezy9dt4mGHA.732@.TK2MSFTNGP04.phx.gbl...
> Hi All,
> I'm not sure how to approach this problem so if anyone could advise me how
> to do it I would be very grateful.
> I have an app that calls a web service. The webservice returns an array of
> business objects.
> So I have a collections of objects that might be, say, 10000 objects in
> size.
> I need to import all the business objects into an SQL Server 2005
> database. The only way I know how to do this is to loop through each
> object and call a stored procedure repeatedly that takes the appropriate
> parameters. The whole import must be atomic. That is, if something goes
> wrong, the whole import needs to be aborted.
> I guess I have two questions:
> 1. Is repeatedly calling a stored procedure the best way to do this sort
> of stuff? I know doing imports like this must be a pretty common
> operation, but I don't know if using an SPROC is a dumb idea.
> 2. Is it ok to start a transaction and repeatedly call a SPROC, like,
> 10000 times? Is there a better what to go about this?
> Maybe I'm worrying about nothing - but it just "feels" wrong to be
> repeatedly calling an SPROC 10,000 times! :-) I was wondering if there is
> a more elegant way?
> Any advice anyone could offer would be very much appreciated.
> Kindest Regards
> Simon|||If your data is coming in as one large XML document, XML Bulk Load might be
another alternative. The final result is the same as SQLBulkCopy but the
input is an XML document.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:eyYZQd5mGHA.1204@.TK2MSFTNGP03.phx.gbl...
> You're right. You don't want to call the same stored procedure 10,000
> times if there is way to accomplish the task as one unit of work. First
> off, calling the stored procedure 10,000 times -while inside a
> transaction, will consume and hold more resources longer than necessary.
> Look into SQLBulkCopy as Alejandro offered
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Simon Harvey" <nothanks@.hotmail.com> wrote in message
> news:ezy9dt4mGHA.732@.TK2MSFTNGP04.phx.gbl...
>|||Hi All,
I'm not sure how to approach this problem so if anyone could advise me
how to do it I would be very grateful.
I have an app that calls a web service. The webservice returns an array
of business objects.
So I have a collections of objects that might be, say, 10000 objects in
size.
I need to import all the business objects into an SQL Server 2005
database. The only way I know how to do this is to loop through each
object and call a stored procedure repeatedly that takes the appropriate
parameters. The whole import must be atomic. That is, if something goes
wrong, the whole import needs to be aborted.
I guess I have two questions:
1. Is repeatedly calling a stored procedure the best way to do this sort
of stuff? I know doing imports like this must be a pretty common
operation, but I don't know if using an SPROC is a dumb idea.
2. Is it ok to start a transaction and repeatedly call a SPROC, like,
10000 times? Is there a better what to go about this?
Maybe I'm worrying about nothing - but it just "feels" wrong to be
repeatedly calling an SPROC 10,000 times! :-) I was wondering if there
is a more elegant way?
Any advice anyone could offer would be very much appreciated.
Kindest Regards
Simon|||Simon,
Try using SqlBulkCopy object (ado.net 2.0).
AMB
"Simon Harvey" wrote:

> Hi All,
> I'm not sure how to approach this problem so if anyone could advise me
> how to do it I would be very grateful.
> I have an app that calls a web service. The webservice returns an array
> of business objects.
> So I have a collections of objects that might be, say, 10000 objects in
> size.
> I need to import all the business objects into an SQL Server 2005
> database. The only way I know how to do this is to loop through each
> object and call a stored procedure repeatedly that takes the appropriate
> parameters. The whole import must be atomic. That is, if something goes
> wrong, the whole import needs to be aborted.
> I guess I have two questions:
> 1. Is repeatedly calling a stored procedure the best way to do this sort
> of stuff? I know doing imports like this must be a pretty common
> operation, but I don't know if using an SPROC is a dumb idea.
> 2. Is it ok to start a transaction and repeatedly call a SPROC, like,
> 10000 times? Is there a better what to go about this?
> Maybe I'm worrying about nothing - but it just "feels" wrong to be
> repeatedly calling an SPROC 10,000 times! :-) I was wondering if there
> is a more elegant way?
> Any advice anyone could offer would be very much appreciated.
> Kindest Regards
> Simon
>|||You're right. You don't want to call the same stored procedure 10,000 times
if there is way to accomplish the task as one unit of work. First off,
calling the stored procedure 10,000 times -while inside a transaction, will
consume and hold more resources longer than necessary.
Look into SQLBulkCopy as Alejandro offered
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:ezy9dt4mGHA.732@.TK2MSFTNGP04.phx.gbl...
> Hi All,
> I'm not sure how to approach this problem so if anyone could advise me how
> to do it I would be very grateful.
> I have an app that calls a web service. The webservice returns an array of
> business objects.
> So I have a collections of objects that might be, say, 10000 objects in
> size.
> I need to import all the business objects into an SQL Server 2005
> database. The only way I know how to do this is to loop through each
> object and call a stored procedure repeatedly that takes the appropriate
> parameters. The whole import must be atomic. That is, if something goes
> wrong, the whole import needs to be aborted.
> I guess I have two questions:
> 1. Is repeatedly calling a stored procedure the best way to do this sort
> of stuff? I know doing imports like this must be a pretty common
> operation, but I don't know if using an SPROC is a dumb idea.
> 2. Is it ok to start a transaction and repeatedly call a SPROC, like,
> 10000 times? Is there a better what to go about this?
> Maybe I'm worrying about nothing - but it just "feels" wrong to be
> repeatedly calling an SPROC 10,000 times! :-) I was wondering if there is
> a more elegant way?
> Any advice anyone could offer would be very much appreciated.
> Kindest Regards
> Simon|||If your data is coming in as one large XML document, XML Bulk Load might be
another alternative. The final result is the same as SQLBulkCopy but the
input is an XML document.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:eyYZQd5mGHA.1204@.TK2MSFTNGP03.phx.gbl...
> You're right. You don't want to call the same stored procedure 10,000
> times if there is way to accomplish the task as one unit of work. First
> off, calling the stored procedure 10,000 times -while inside a
> transaction, will consume and hold more resources longer than necessary.
> Look into SQLBulkCopy as Alejandro offered
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Simon Harvey" <nothanks@.hotmail.com> wrote in message
> news:ezy9dt4mGHA.732@.TK2MSFTNGP04.phx.gbl...
>|||Thanks guys!
That XML one seems even more interesting actually, although I'm guessing
it might be quite hard to set up the mappings. Especially seeing as the
data is quite hierarchichal in nature.
Many thanks for your advice!
Simon
Roger Wolter[MSFT] wrote:
> If your data is coming in as one large XML document, XML Bulk Load might b
e
> another alternative. The final result is the same as SQLBulkCopy but the
> input is an XML document.
>|||Thanks guys!
That XML one seems even more interesting actually, although I'm guessing
it might be quite hard to set up the mappings. Especially seeing as the
data is quite hierarchichal in nature.
Many thanks for your advice!
Simon
Roger Wolter[MSFT] wrote:
> If your data is coming in as one large XML document, XML Bulk Load might b
e
> another alternative. The final result is the same as SQLBulkCopy but the
> input is an XML document.
>

Importing data from Oracle9i CLOB column to MS SQL Server text column

Hi everyone,

I encountered an error "Need to run the object to perform this operation

Code execution exception: EXCEPTION_ACCESS_VIOLATION

" When I try to import data from Oracle to MS SQL Server with Enterprise
Manager (version 8.0) using DTS Import/Export Wizard. There are 508 rows
in Oracle table and I did get first 42 rows imported to SQL Server.
Anyone knows what does the above error message mean and what causes the
rest of the row failed importing?

Thanks very much in advance!

Rene Z.

--
Posted via http://dbforums.com"yzhao12" <member46605@.dbforums.com> wrote in message
news:3547161.1067635623@.dbforums.com...
> Hi everyone,
>
> I encountered an error "Need to run the object to perform this operation
> Code execution exception: EXCEPTION_ACCESS_VIOLATION
> " When I try to import data from Oracle to MS SQL Server with Enterprise
> Manager (version 8.0) using DTS Import/Export Wizard. There are 508 rows
> in Oracle table and I did get first 42 rows imported to SQL Server.
> Anyone knows what does the above error message mean and what causes the
> rest of the row failed importing?
>
> Thanks very much in advance!
>
> Rene Z.
>
> --
> Posted via http://dbforums.com

This error is described here:

http://support.microsoft.com/?kbid=271889

However, since the KB article refers to running a package from VB, but
you're using the Import/Export wizard, I'm not sure if this will help. You
could try saving the package from the wizard, instead of executing it, then
using this workaround:

http://www.databasejournal.com/feat...e.php/1461391#5

If that doesn't help, then I suggest you post to
microsoft.public.sqlserver.dts - you may get a better answer there.

Simon

Importing an XML file (hierarchy issue)

Hi,

I am trying to import an xml file using xml source object.

The problem is that this object ignores the hierarchy/dependeces of the elements, each output of the object has data of every element isolated... and i have to merge them to update my destination table, beacuse i need to insert several fields of diferent elements in the same table (only one). SISS doesnt take into account hierarchy and insert the records separatedly and fill the fields of the other elements with null...

Thats my xls:

Code Snippet

<?xml version="1.0" ?>

- <xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">

- <xs:element name="responses">

- <xs:complexType>

- <xs:sequence>

- <xs:element minOccurs="0" name="response">

- <xs:complexType>

- <xs:sequence>

- <xs:element minOccurs="0" maxOccurs="unbounded" name="reserva">

- <xs:complexType>

- <xs:sequence>

<xs:element minOccurs="0" name="localizador" type="xs:string" />

<xs:element minOccurs="0" name="programa" type="xs:string" />

<xs:element minOccurs="0" name="fecha_creacion" type="xs:string" />

<xs:element minOccurs="0" name="fecha_modificacion" type="xs:string" />

- <xs:element minOccurs="0" name="oficina_responsable">

- <xs:complexType>

- <xs:sequence>

<xs:element minOccurs="0" name="empresa" type="xs:string" />

<xs:element minOccurs="0" name="nombre" type="xs:string" />

</xs:sequence>

</xs:complexType>

</xs:element>

<xs:element minOccurs="0" name="plazas" type="xs:string" />

- <xs:element minOccurs="0" maxOccurs="unbounded" name="salida">

- <xs:complexType>

- <xs:sequence>

<xs:element minOccurs="0" name="codigo" type="xs:string" />

<xs:element minOccurs="0" name="dias" type="xs:string" />

- <xs:element minOccurs="0" name="origen">

- <xs:complexType>

- <xs:sequence>

<xs:element minOccurs="0" name="nombre" type="xs:string" />

<xs:element minOccurs="0" name="nombre_corto" type="xs:string" />

</xs:sequence>

</xs:complexType>

</xs:element>

- <xs:element minOccurs="0" name="destino">

- <xs:complexType>

- <xs:sequence>

<xs:element minOccurs="0" name="nombre" type="xs:string" />

<xs:element minOccurs="0" name="nombre_corto" type="xs:string" />

</xs:sequence>

</xs:complexType>

</xs:element>

<xs:element minOccurs="0" name="fecha" type="xs:string" />

<xs:element minOccurs="0" name="estado" type="xs:string" />

<xs:element minOccurs="0" name="importe" type="xs:string" />

<xs:element minOccurs="0" name="comision" type="xs:string" />

<xs:element minOccurs="0" name="f_limite_emision" type="xs:string" />

<xs:element minOccurs="0" name="f_limite_pago" type="xs:string" />

<xs:element minOccurs="0" name="f_limite_confirmacion" type="xs:string" />

</xs:sequence>

</xs:complexType>

</xs:element>

- <xs:element minOccurs="0" maxOccurs="unbounded" name="pasajero">

- <xs:complexType>

- <xs:sequence>

<xs:element minOccurs="0" name="dni" type="xs:string" />

<xs:element minOccurs="0" name="nombre" type="xs:string" />

<xs:element minOccurs="0" name="telefono" type="xs:string" />

<xs:element minOccurs="0" name="num_documento" type="xs:string" />

<xs:element minOccurs="0" name="primera_emision" type="xs:string" />

<xs:element minOccurs="0" name="ultima_emision" type="xs:string" />

<xs:element minOccurs="0" name="pagado" type="xs:string" />

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:schema>

The XML Source is not ignoring hierarchy. In fact, I just tried using your schema with the XML Source, and it produced seven outputs. It then added an "id" column to the outputs, in order to represent the hierarchy. For instance, it creates a "response_id" for the response "table", and reserva has a response_id to tie it to the response row, and a reserva_id to identify the reserva row. oficina_responsable has empresa and nombre columns as well as a reserva_id to indicate which reserva row it is a child of.

This can be a pain in the neck, but it does preserve the hierarchy.

Looking at your schema, it seems to me that you might want to use an XSL transform to flatten your XML For instance, you could have an origen_nombre column and a destino_nombre column within the salida "table". I believe you could reduce it to three elements, and therefore three outputs from the XML source. You would have reserva, salida and pasajero. All three outputs would have a "reseva_id" column:

Code Snippet

<responses>

<reserva>

<localizador>x</localizador>

<salida>

<origen_nombre>nombre</origen_nombre>

...

</salida>

<salida>

<origen_nombre>nombre</origen_nombre>

...

</salida>

<pasajero>

...

</pasajero>

<pasajero>

...

</pasajero>

</reserva>

<reserva>

...

</reserva>

<responses>

Importing an XML file (hierarchy issue)

Hi,

I am trying to import an xml file using xml source object.

The problem is that this object ignores the hierarchy/dependeces of the elements, each output of the object has data of every element isolated... and i have to merge them to update my destination table, beacuse i need to insert several fields of diferent elements in the same table (only one). SISS doesnt take into account hierarchy and insert the records separatedly and fill the fields of the other elements with null...

Thats my xls:

Code Snippet

<?xml version="1.0" ?>

- <xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">

- <xs:element name="responses">

- <xs:complexType>

- <xs:sequence>

- <xs:element minOccurs="0" name="response">

- <xs:complexType>

- <xs:sequence>

- <xs:element minOccurs="0" maxOccurs="unbounded" name="reserva">

- <xs:complexType>

- <xs:sequence>

<xs:element minOccurs="0" name="localizador" type="xs:string" />

<xs:element minOccurs="0" name="programa" type="xs:string" />

<xs:element minOccurs="0" name="fecha_creacion" type="xs:string" />

<xs:element minOccurs="0" name="fecha_modificacion" type="xs:string" />

- <xs:element minOccurs="0" name="oficina_responsable">

- <xs:complexType>

- <xs:sequence>

<xs:element minOccurs="0" name="empresa" type="xs:string" />

<xs:element minOccurs="0" name="nombre" type="xs:string" />

</xs:sequence>

</xs:complexType>

</xs:element>

<xs:element minOccurs="0" name="plazas" type="xs:string" />

- <xs:element minOccurs="0" maxOccurs="unbounded" name="salida">

- <xs:complexType>

- <xs:sequence>

<xs:element minOccurs="0" name="codigo" type="xs:string" />

<xs:element minOccurs="0" name="dias" type="xs:string" />

- <xs:element minOccurs="0" name="origen">

- <xs:complexType>

- <xs:sequence>

<xs:element minOccurs="0" name="nombre" type="xs:string" />

<xs:element minOccurs="0" name="nombre_corto" type="xs:string" />

</xs:sequence>

</xs:complexType>

</xs:element>

- <xs:element minOccurs="0" name="destino">

- <xs:complexType>

- <xs:sequence>

<xs:element minOccurs="0" name="nombre" type="xs:string" />

<xs:element minOccurs="0" name="nombre_corto" type="xs:string" />

</xs:sequence>

</xs:complexType>

</xs:element>

<xs:element minOccurs="0" name="fecha" type="xs:string" />

<xs:element minOccurs="0" name="estado" type="xs:string" />

<xs:element minOccurs="0" name="importe" type="xs:string" />

<xs:element minOccurs="0" name="comision" type="xs:string" />

<xs:element minOccurs="0" name="f_limite_emision" type="xs:string" />

<xs:element minOccurs="0" name="f_limite_pago" type="xs:string" />

<xs:element minOccurs="0" name="f_limite_confirmacion" type="xs:string" />

</xs:sequence>

</xs:complexType>

</xs:element>

- <xs:element minOccurs="0" maxOccurs="unbounded" name="pasajero">

- <xs:complexType>

- <xs:sequence>

<xs:element minOccurs="0" name="dni" type="xs:string" />

<xs:element minOccurs="0" name="nombre" type="xs:string" />

<xs:element minOccurs="0" name="telefono" type="xs:string" />

<xs:element minOccurs="0" name="num_documento" type="xs:string" />

<xs:element minOccurs="0" name="primera_emision" type="xs:string" />

<xs:element minOccurs="0" name="ultima_emision" type="xs:string" />

<xs:element minOccurs="0" name="pagado" type="xs:string" />

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:schema>

The XML Source is not ignoring hierarchy. In fact, I just tried using your schema with the XML Source, and it produced seven outputs. It then added an "id" column to the outputs, in order to represent the hierarchy. For instance, it creates a "response_id" for the response "table", and reserva has a response_id to tie it to the response row, and a reserva_id to identify the reserva row. oficina_responsable has empresa and nombre columns as well as a reserva_id to indicate which reserva row it is a child of.

This can be a pain in the neck, but it does preserve the hierarchy.

Looking at your schema, it seems to me that you might want to use an XSL transform to flatten your XML For instance, you could have an origen_nombre column and a destino_nombre column within the salida "table". I believe you could reduce it to three elements, and therefore three outputs from the XML source. You would have reserva, salida and pasajero. All three outputs would have a "reseva_id" column:

Code Snippet

<responses>

<reserva>

<localizador>x</localizador>

<salida>

<origen_nombre>nombre</origen_nombre>

...

</salida>

<salida>

<origen_nombre>nombre</origen_nombre>

...

</salida>

<pasajero>

...

</pasajero>

<pasajero>

...

</pasajero>

</reserva>

<reserva>

...

</reserva>

<responses>