.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

Importing multiple XML files with little or no transformation

Posted By:      Posted Date: September 28, 2010    Points: 0   Category :Sql Server


I'm new to SSIS but am fairly experienced with SQL Server (as a developer, not as a DBA). I'm using SQL Server 2005, Management Studio, and VS-2005 Business Intelligence Development Studio (BIDS).

I have a collection of externally sourced XML files that I want to eventually get into our database. While it hasn't yet been completely specified what I will need to do, I know that there may be some integrity testing on the data, and I will definitely need to handle errors (in the data as well as various runtime errors such as network failure and the like).

The game plan right now is to make a small "sourcing DB" and put the data from the XML files pretty much directly into it, then take it from there. I suppose it might be better from a performance point of view to handle everything "on the fly" and avoid such staging tables, but this is probably easier and I would guess more scalable as well. (Most of the files are small, < 100kB, but one is much bigger and may be a few hundred megabytes).

So my first question is this: Are there any compelling reasons why we should *not* perform this staging step and import the XML data as-is before attempting any cleansing/validation/data error handling? If so, I would appreciate a short explanation of why and alternative suggestions - or, of course, links to the same.

View Complete Post

More Related Resource Links

Importing multiple files to multiple tables in SSIS


I have a directory with 200+ txt files to import into SQL tables in a database. Each file name is the exact table name in the database(without the file extension, obviously). I am looping through each file with a for each loop and a variable is mapped and set in the source connection properties for the Expressions -> ConnectionString property, so each name will go into that variable without the file extensions, correct?  Now, I set the variable name to the table name in the destination for the table name under "Data Access Mode", but it is giving an error...do I have to assign variables to each part, (Connection String and Name)? Does anyone have a quick setup for this?



importing multiple dbf files into same table



I have an old dbf program that provides me with a dbf ouptut on a daily basis. Each file is identical, but the file name is different by using the following naming convention: MMMDDYY.dbf, for example, OCT2710.dbf.

My OS is Windows Server 2008 R2 64 bit
My version of SQL is 2008 R2 64 bit
The DBF files appear to be DIII (I am able to use this type to import into an access database, one file at a time, and it creates a new file, not appending).

I have about 2 years worth of dbf files that I would like to import into a single sql table in order to be able to create some reports. What I'm looking for is a macro that will enable me to run it against a file folder and import all of the dbf files until it has imported them all. I want to do this as an append as each of the files have identical layout and so append should work fine. I have tried to do an import via 32 bit as well as 64 bit import wizard but can't seem to figure out how to do it. I have created a 32bit odbc to the folder but the sql import for both 32 bit and 64 bit won't recognize the odbc connection.

Has anyone got any ideas about how to resolve this? I did manage to find an old sql 2000 server that was able to do the import, but it was quite a manual operation.


Error: Encountered multiple versions of the same assembly with GUID...try pre-importing...TlbImp


Hi!  Can someone tell me how I can troubleshoot the following error: "Encountered multiple versions of the same assembly with GUID...try pre-importing one of these assemblies".

The website developed in VS 2010 (.Net 3.5). This error is only received on my workstation.  Another person developing the site does not experience this issue at all.  Also, not sure if this matters, but on my workstation the 'Assembly Information...' dialog contains no values even though the 'AssemblyInfo.vb' file does specify values for the title, desc, etc.  The GUID being referenced in the error is the main project of the three projects within the solution.

I tried looking through the GAC, but do not see any references to the projects or DLLs in the VS solution and am not sure what else/where to look.

If I delete the copy of the solution on my local machine and pull down a copy from source control (AnkhSVN) the solution will build with no error.  Once I make any changes, such as adding a new aspx file, then the error is received.

I can provide any additional information needed.

Toolbox: Easy File Backup, Exploring Files And Folders Inside Visual Studio, Multiple Monitor Softwa


If the responsibility for creating, managing, and executing routine backups is yours, these tools will make it easier. Also see how you can browse folders and files from inside Visual Studio.

Scott Mitchell

MSDN Magazine May 2009

Upload multiple files in asp.net

The article Upload multiple files in asp.net was added by habdulrauf on Friday, July 09, 2010.

Some times we need to allow user to upload as many files as he/she wants instead of fixed number of files.So here is the procedure to achieve this. Idea is taken from Joe's video.%@ Page Language="C#" AutoEventWireup="true" CodeFile

how to read config details from multiple config files

Hi, My web application contain 3 config files with different names, ex. firstconfig.config, secondconfig.config, thirdconfig.config.  Each config files have same Key Value pairs. Now what i want to do is, based on the condition i want to read specific info from specific config file. What i tried is, in Web.config files  <appSettings file="firstconfig.config"></appSettings>   In this way i get th info from that particular config file.  Now how do i get details from other 2 config files.   Can any one know abt this.   S. Ramkumar  Smiley

Multiple XML files into SQL SERVER Express 2005

Hello. I am familiar with classic ASP and use this with MS SQL SERVER EXPRESS. I have an SQL table and want to import multiple XML files into this on a daily basis. I currently have 3 files, transferdata.vbs which loops through the XML files. FAQschema.xml which maps XML to the SQL database and test.xml shows the xml in the test file. If I run transferdata.vbs I get the following error "Error opening the data file" line 33 char 3. Microsoft Bulkload for SQL Server". My SQL table is called EnqOrd id (int), Debitor (varchar), PurchaseDate (varchar)     transferdata.vbs set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad") objBL.ConnectionString = "Provider=SQLOLEDB; Data Source=XXXXXX\XXXXX; Initial Catalog=XXXX; User ID=XXXXX; Password=XXXXXX" objBL.ErrorLogFile = "E:\fuelsql\Teccom\error.log" ' Here is the path to your XML files Const path = "E:\fuelsql\Teccom\XML\" Dim Text, Title, oFile Dim fso, oFolder, oFiles, wsh ' Object variables Text = "Folder " Title = "XML Files" Set wsh = WScript.CreateObject("WScript.Shell") ' Create FileSystemObject object to access the file system. Set fso = CreateObject("Scripting.FileSystemObject") ' Get Folder object. Set oFolder = fso.GetFolder(wsh.ExpandEnvironmentStrings(path)) ' Get All Files Set oFiles = oFolder.

Import Multiple XML files into Sql Server

Hi, i have nearly 1000 xml files in one folder of similar format and I have xsd for the same as well. I would like to import all the files in to sql table by either looping through all the files or any other way. Thanks

Initialize snapshot fro alternate location - multiple .bcp files for an article

I have to replicate over 50GB of data over a slow network.  I did not use the option to initialize snapshot from database backup because the replication articles contain row filters.  If I do that, I'll have to run a lot of scripts to remove the data and other unnecessary database objects on the subscriber. Instead, I created a workaround.  On the publisher, I first create the actual push subscription to the target subscriber on the publication.  This subscription is set not to initialize from snapshot.  I then created a second push subscription on the same publication, but the subscriber is a random database on the publisher server.  This second subscription is a dummy subscription set to initialize from snapshot - the purpose is to generate the necessary snapshot files.  I then reinitialized all subscriptions and generate the new snapshot files. On the subscriber, an empty database is created with the same tables as the publisher database.  I created an identical publication on this empty database, and a dummy push subscription on the target subscriber.  The subscription is reinitialized, and the snapshot files on the empty database is created.  These dummy snapshot files are then overwritten with the actual snapshot files created on the publisher, and then I synchronize the the dummy subscriptions with the actual sn

Importing Excel with multiple worksheets via MVC

Is there a way wherein I could import data from multiple sheets of an excel file?  Thanks in advance...

Import Multiple Text Files

I have 150 data delimited text files that need to be imported to a text file. Each of them is named for the table for which they contain data e.g. address.txt contains data that needs to imported to the address table. So what I have done is create a SSIS package that contains a ForEach loop that iterates through the the files, and has a Load Data field object. What I haven't been able to figure out is what logic needs to be added for the Data Flow. I know I will need a Flat File Source with a connection source that is tied to file it is processing.  However, after all of that I am not sure what do because each text file contains a different table with different columns. Can someone explain to me how to handle this? Thanks, Isaiah 

Receive/Delete Multiple FTP Files based on condition using SCRIPT(VB) TASK

I am trying to receive and delete multiple FTP files from a remote FTP server using Script task Below is the code   Dim FTPConnMgr As ConnectionManager FTPConnMgr = Dts.Connections("FTP Connection Manager") Dim FTPClientConn As FtpClientConnection = New FtpClientConnection(FTPConnMgr.AcquireConnection(Nothing)) Dim FileTimeStampNew As String = "20100913021807" Dim remoteFileNames(0) As String remoteFileNames(0) = Dts.Variables("FtpRemoteDirectory").Value & "*" & FileTimeStampNew & ".*" 'Below hardcoded FileName works good. But the problem is there are lots of file in the FTP folder that I dont want to receive 'remoteFileNames(0) = Dts.Variables("FtpRemoteDirectory").Value & "Company_alpha_Full_20100913021807.xml" Dim localPath As String = Dts.Variables("FtpLocalDirectory").Value FTPClientConn.Connect() FTPClientConn.ReceiveFiles(remoteFileNames, localPath, False, False) FTPClientConn.Close() Dts.TaskResult = ScriptResults.Success End Sub End Class If i specifically mention the RemotefileNames this works fine but, when I say *.* it executes succefully but doesn't receives the file. Please advice how to receive multiple file based on a variable BR, AWM

Problem importing text files with binary zeros (0x00) via SSIS(SQL2005). It is all fine when using D

Hi.   There is a "text" file generated by mainframe and it has to be uploaded to SQL Server. I've reproduced the situation with smaller sample. Let the file look like following: A17     123.17  first row          BB29    493.19  second             ZZ3     18947.1 third row is longer And in hex format: 00:  41 31 37 20 20 20 20 20 ? 31 32 33 2E 31 37 20 20  A17     123.17  10:  66 69 72 73 74 20 72 6F ? 77 0D 0A 42 42 32 39 20  first row??BB29 20:  20 00 20 34 39 33 2E 31 ? 39 20 20 73 65 63 6F 6E     493.19  secon30:  64 0D 0A 5A 5A 33 20 20 ? 20 20 20 31 38 39 34 37  d??ZZ3     1894740:  2E 31 20 74 68 69 72 64 ? 20 72 6F 77 00 69 73 20  .1 third row is 50:  6C 6F 6E 67 65 72       ?                          longer          I wrote "text" in quotes because sctrictly it is not pure text file - non-text binary zeros (0x00) happen sometimes instead of spaces (0x20).   The table is: CREATE TABLE eng ( src varchar (512) )   When i upload this file into SQL2000 using DTS or Import wizard, the table contains: select src, substring(src,9,8), len(src) from eng <               src                ><substr>             <len> A17     123.17  first row           123.17                  25BB29                                493.19                  22ZZ3     18947.1 third row           18947.1                 35   As one can see, everything was importe

Upload multiple files to Server

I have an ASPX Page that contains a fileuploader, I want to know how can I upload multiple files together to server and also create a new folder on Server and then upload these files into this new folder! Please send your sample codes in VB.NET,   Thanks,

import multiple Excel 2007 files using openrowset

hello, I Have a folder that includes multiple excle files (ver 2007), i am trying  to loop through each file using vbscript to import its data to sql 2000 using openrowset but i am getting this error in the openrowset line "[Microsoft][ODBC sql server driver] [sql server] [OLE/DB provider returned message: the microsoft office access DB engine cannot open or write to the file '', it is already open exclusively by another user or you need permission to view or write its data] code 80040E14 source microsoft OLEDB provider for ODBC drivers Any help please    

Insert multiple files to SQL Database in VB

My files are being inserted but the byte array is showing as a 0x0000... etc for every file after the first inserted file. The first inserted image is correct. The database is set up as an Image type. The problem exists in the code here. Thank you in advance! Dim uploads As HttpFileCollection uploads = HttpContext.Current.Request.FilesFor i As Integer = 0 To (uploads.Count - 1) If (uploads(i).ContentLength > 0) Then Dim c As String = System.IO.Path.GetFileName(uploads(i).FileName) 'Dim upload As HttpPostedFile = uploads(i) If Not fileUpEx.PostedFile Is Nothing And _ fileUpEx.PostedFile.ContentLength > 0 Then Try If CallingTab >= "" Then Call UploadImage(c, uploads(i).ContentLength, uploads(i).ContentType, uploads(i).InputStream, _ strUsername, intSiteID, intCompanyID, strDocumentType, CallingTab, NewVersionNumber) Else lblStatus.Text = "Document Not successfully uploaded to the database! Problem occured with the Calling Tab" End If Catch Exc As Exception Response.Write("Error: " & Exc.Message) End Try

remove upload multiple files link from upload.aspx..

hi, is there a way to remove the upload multiple files link from upload.aspx..without using any custom coding.
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