Large XML file source in SSIS?

Hi,

I have a problem where I want to import a 1.6 GB XML file with SSIS into a SQL Server database. My hunch is that SSIS is not very good with handling such large amount of XML data. My test shows that SSIS tries to read all of the file into memory.

Does anyone know if there is any solution of solving this memory problem. My problem is that I want to take this source XML file import it into a database, make some transformations on it (eliminate duplicates etc) then produce a NEW XML file as output in a different XSD-format.

Is really SSIS the right tool for this operation?

The source XML file also have mixed content on Complex Types which seems to be a problem for SSIS as well.

Best regs,

//Patrick

Which SSIS approach did you try, the XML Task, or the dataflow with an XML source? Presumably it was the task, because of stock SSIS xml source component's inability to handle mixed content?

The XML Task, in my experience, croaks on large XML files, and also doesn't work in loops if there is a single failure.

On the other hand, I have used the XML source in an SSIS dataflow with relatively large files, 100Mb or so, without a problem, but have never tested with Gb+ sized files.

SQLXmlBulkLoad, on the other hand, works fine with large xml files, but there again, I'm not certain about mixed content. I have posted a script task which uses SqlXmlBulkload in another forum post.

Also, what version of SQL Server are you using, since there a number of additional options in 2k5?

Large Inserts, TempDB Growing

I have a query that's joining a messload of tables to populate a single
table used later for OLAP reporting.
The source tables, and the OLAP table are in different databases.
Basic Form:
INSERT INTO OLAPDB.dbo.SomeTable
SELECT lots_of_columns
FROM atables
INNER JOIN lots_of_tables...
No ORDER BYs... no GROUP BYs
The query dies because there isn't enough disk space for TempDB.
When I look at the files, tempdb is HUGE, and the OLAPDB (destination)
is tiny.
Is there a way to insert the data straight into the destination?
without it using tempdb as an intermediate?
any ideas?
thanks
-MarkMark,
Maybe add a WHERE clause and perform the INSERT in multiple parts.
HTH
Jerry
"Mark" <AnonymousPerson12345@.gmail.com> wrote in message
news:1127429078.936480.53900@.o13g2000cwo.googlegroups.com...
>I have a query that's joining a messload of tables to populate a single
> table used later for OLAP reporting.
> The source tables, and the OLAP table are in different databases.
> Basic Form:
> INSERT INTO OLAPDB.dbo.SomeTable
> SELECT lots_of_columns
> FROM atables
> INNER JOIN lots_of_tables...
> No ORDER BYs... no GROUP BYs
> The query dies because there isn't enough disk space for TempDB.
> When I look at the files, tempdb is HUGE, and the OLAPDB (destination)
> is tiny.
> Is there a way to insert the data straight into the destination?
> without it using tempdb as an intermediate?
> any ideas?
> thanks
> -Mark
>|||Mark wrote:
> I have a query that's joining a messload of tables to populate a
> single table used later for OLAP reporting.
> The source tables, and the OLAP table are in different databases.
> Basic Form:
> INSERT INTO OLAPDB.dbo.SomeTable
> SELECT lots_of_columns
> FROM atables
> INNER JOIN lots_of_tables...
> No ORDER BYs... no GROUP BYs
> The query dies because there isn't enough disk space for TempDB.
> When I look at the files, tempdb is HUGE, and the OLAPDB (destination)
> is tiny.
> Is there a way to insert the data straight into the destination?
> without it using tempdb as an intermediate?
> any ideas?
> thanks
> -Mark
An INSERT INTO is a fully logged operation. A SELECT INTO OTOH is a bulk
logged operation. Instead of using tempdb, you could use a regular table
in a database of your choosing. However, if you are running out of
space in tempdb, you could make sure tempdb is adequately sized to begin
with and can auto-grow if needed. You could also try using SELECT INTO
which will keep transaction logging to a minimum, but does require SQL
Server create the table for you based on the columns in the query. You
could also try speeding up the query by using a stored procedure that
pulls data from the tables in a more efficient manner - if that's
possible.
David Gugick
Quest Software
www.imceda.com
www.quest.com