.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Is it possible to use an XML source with a Huge XML file?

Posted By:      Posted Date: April 14, 2011    Points: 0   Category :


I have a huge xml file which I need to parse and load into a database.  The file is about 1.7 Gig!  Fun!

I initially tried to use an XML source but I get an OutOfMemoryException error.  Is it possible to use an XML source and optimize the memory use so that it can run? 

If not, is there a good place to go to for learning how this could be done in a script task?  I have many nodes that need to be joined together in order to flatten the data, but I have no experience with scripting xml.

Thanks for any advice on this,


View Complete Post

More Related Resource Links

Issue: Load {NUL} from flat file to "Flat File Source" faild.

Hi guys, I've met a strange issue when I was working to use SSIS to load data from flat file to database. It is a story about {NUL}. Sample Flat File: FIELD_SAMPLE|OTHERS ABC{NUL}DEFG|Any Others   I placed an Flat File Source to load these into package, and the configuration of the Connection Manager are as following: FIELD_SAMPLE: Unicode string [DT_WSTR], Length 8   Then I click the "Preview" button and I can see this data in the "Preview" window correctly (with the value "ABC{NUL}DEFG"). But when I tried to run this package, I've got an error that the "ABC{NUL}DEFG" is too long for this field. Then I changed the length to 20 and it was loaded into SSIS package. But I found the value in SSIS package is "ABC", and "{NUL}DEFG" was not there~ I have no ideal why it is ok in Preview but it is not ok when the package running~     Anyway, I hope to load the {NUL} from the flat file into target database. So that is there anybody could give me a hand?

Need an example of error in OLE DB Source component when source is Excel file.

I want an example of error (not truncation) in individual row or individual cell that can occur in OLE DB Source component when source is Excel file. I'm trying out Error Output of data task component.  I have already tried out Error Output of OLE DB Destination component.

Load and parse a file where the file path is located in a column of a flat file source

Hi, I have a situation where I have a CSV that contains a bunch of data that populates a bunch of related tables with FK constraints directly.  Thats no problem.  The problem is that one of the columns specifies a filepath to an XML file that is supposed to contain data to be populated in a table with an FK constraint.  How do I specify the file name to the XML source component at runtime? For example TableA has columns: TableAID GroupName TableB has columns TableBID TableAID (FK to Table A) MemberID (the data in question) TableA has a 1 to many relationship with Table B. The CSV has a bunch of columns including: TableAID File path to list of members for each group. I don't know how to solve this problem.  I tried using a ForEach ... but I couldn't get it to work. Thanks in advance for any assistance.

SSIS - XML Source to SQL Server Destination. How to handle new tags in xml file?

Hi, I've created a SSIS package, which reads my XML file using XML Source, using SCD(Slowly Changing Dimension) to identify whether to do an Insert/Update and everything's fine so far. But there are chances that my xml file will have new tags added apart from what are already there. So In that case, i'm not able to figure out whether to alter the table to add new column or something like that. Need your suggestion. Thanks in advance Vijay

Error connecting to undo manager of source file?

When I run my project I get an error that says: Error connecting to undo manager of source file: path to my project/myproject/contact.aspx.designer.cs".How do I correct this?

Konesans Trace File Source adapter


Found the handy Konesans Trace File Source adapter that allows reading of SQL Profiler trace (.trc) files, and it works well. The adapter has properties that allow for Variable-based filename property interpretation...however being a total SSIS noob, I am not familiar how to set up the variables so that it will read from a series of files in a directory. The method I am familiar with, of using a ForEach container doesnt really work because the adapter itself does not rely on a connection manager, and I am unable to set the variable name for it via an expression.

Anyone out there using this tool or have some pointers?




wpf project with single source file?


For purposes of submitting sample code to this forum, I want to make VS2008 WPF projects with only 1 source file. I know how to do it with 2. I can make projects with Window1.xaml and Window1.xaml.cs. Is it possible to do it with only Window1.xaml?


Setup file(*.msi) also includes .aspx.cs (source code) files also


My project is recently migrated from 2008 to 2010

actualy we developed project in VS 2005 in that we created setup file but when we migrated to vs2008 setup file giving problem to us.

now when we make setup file it is including .aspx.cs files also but this if we publish our project it wont including source code.

why setup file including .aspx.cs files is there any way to change.

plz replay ASAP

How to ensure all the fields from a source file is mapped to a destination file \ table


I am trying to verify if the SSIS package created by someone else has all the fields from source mapped to a destination table columns.

How to do that?

Also are there any best practices for testing for correctness of SSIS package.

How capture bad records from "Flat File Source" in SSIS


The flat file is having 10 columns so used 9 delimitor. Few records in the Flat file is having more than 10 columns. I need to redirect in to some error table and rest valid records needs to be loaded in target. Pls help.

SSIS: Error in the package when the data in the flat file source is modified


Hi All,

I have a package which loads data from a flat file source to an OLEDB destination, which is executed successfully and data is loaded perfectly.

But later when the data in the flat file source is modified i.e. if an extra column gets added to the text file, the package is throwing an error because it is unable to detect the extra added column.

How can i avoid this error??? I need my package to execute successfully ignoring the changes(added columns) happened in the flat file source.

Please provide me wth your suggestions and solutions....

Thanks in Advance!!

Huge XML file update



hope i'm in the right forum...

anyway, my problem is following : i have really huge xml file and don't want to load it into memory. For reading values, i'm streaming through that (i'm using external library which supports streaming based on xpath which is great). However, i face issues when i need to update that file like e.g. add new attribute to element or update its value...

so the question is : does anybody know of any common approach to that problem (if exists :-) ) or any external library providing this functionality ?

thanks for any help



Huge temdb and msdb data file after Cleanup Task


I've run the History Cleanup Task has a maintenance job.

After the job ran successfully, I noticed that the data files of msdb and temdb are over 2 GB of size. I've tried to shrink both databases and I didn't get much. It is normal to have the tempdb over 3 GB after run History Cleanup Task even when this is was not the size of the database tempdb before run the History Cleanup Task ??

Best regards

SSIS - load table from source text file with multiple record lengths


I have a text file that has mulitple records lengths that I need to load into a table.  The file has multiple record types and each record type detemines the length of the record.  Let me try to explain...the file has a balance line 1, multiple detail records, balance line 2, and a trailer record.

The first record is a balance line 1, which has a record length of 144.

Detail records with a record type of inv or crd, will have a record length of 147.  The remaining record types will have a length of 144.

The second to the last record is balance line 2, which has a a record length of 156.

And the trailer record, which has a record length of 162.

Here is a portion of my source file.  I have made each line bold where you can see the rec type.  The record starting with 9999 is the trailer.

077700001BL100000002010100120101008BALANCE FORWARD 0000000000000000001081175D0000000000{0000000000{0000000000{000001081175D00 0000000000{

077701000INV01953172010100120101008GROCERY - DRY 0000000000000000000073504I0000016678K0000000852G0000000000{000000057679D011N 0000093201E010

07779008099000000002010100820101008KC CASH REC. 0000000000000000001081175M0000000000{0000000000{0000000000{000001081175M00 00000000

Flat File Source Task Failure



I have an ETL process where I am loading flat files int a table with a Foreach Loop task. I came accross a file that keeps failing the SSIS package as the Flat File Task tries to read it. Is there a way quick way to locate the problem within the Flat file (text file).

Here is the error:

[Flat File Source [1]] Error: The column data for column "Immediate Cash Amount" overflowed the disk I/O buffer.

[Flat File Source [1]] Error: An error occurred while skipping data rows.

Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on component "Flat File Source" (1) returned error code 0xC0202091.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.





Reading a huge file from data base


Hi all.

I have a 1.5 GB file into a table. I want to read It from server and create a file stream with it for save.

When I use DataAdapter.Fill(DataSet); code OutOfMemoryException occurs.

What should I do for read a hage file from data base?

BIDS - The file has been modified outside of the source editor


Hi all,

Lately whenever I make a change to a package in my solution - I receive a message: "The file has been modified outside of the source editor. Do you want to reload it?"

Has anyone else come across this before? How do you fix?

Thanks, Clay


ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend