.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 files to multiple tables in SSIS

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

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?



View Complete Post

More Related Resource Links

extracting from multiple tables in an odbc call/ssis

Hi,   I have an odbc for DBISAM, and can use it to import into sql server, but only one table at a time.  (when it get's to the choose table screen, it will only let me create a single SQL query.   does anyone know if I can extract from multiple tables using this?   If not - what would I need to change in the DTSX file so that my query goes from "select * from tablea" to "select * from tableb" and the destination table is also "tableb"?   also - can the DTSX file be change to run for multiple tables?

Creating Multiple Excel files Using SSIS

Hello Friends,  I have to create multiple excel files(Per Year eg 2008 , 2009, 2010....) from datatable using SSIS  For that i have to use one template excel file that is already created on some location.  Any help would be appreciated.  Thanks Thanks

Data Flows in SSIS - Mapping Multiple source tables to Destination table **Newb question**

Hi I am new to SSIS and had a basic question. I have around 30+ tables in a db that needs to be migrated to a newer schema in the DB. The data flow task seems to be ideally suited for my requirement. My question is do I need to create 30+ different data flow tasks for this which will get executed one after the other or is there a better way to migrate large number of tables. Also how are referential constraints taken care of during such migration. Thanks and Regards, Ganesh Ranganathan
Ganesh Ranganathan
[Please mark the post as answer if it answers your question]

Importing multiple XML files with little or no transformation



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.

SSIS - XML Source to multiple SQL tables(Master-Detail)



I've a scenario in which i need to map my xml file to multiple sql server tables, maintaining Referential Integrity. 

Below is my sample xml structure







 <Hdr_ID>ID of the above record</Hdr_ID>




 <Hdr_ID>ID of the above record</Hdr_ID>


as u can see above, 1 parent record has 2 child records. So i need to save the parent record first, based on the ID which is generated automatically, i need to save my child records into the Detail table. I've searched everywhere in google, but couldn't get a solution yet.

(I had done this sort of thing in vb.net but now my job is to do it using SSIS)   :(

Can you gurus help me in achieving my task.

Thanks in advance.


Extracting data from multiple CSV files using SSIS package



I am new to SSIS. I am extracting data from multiple CSV files using SSIS package and copying that data into SQL server 2008  database. Can you please confirm if I can create only one package to handle all CSV files or I will have to create packages for each CSV file.

Column names and numbers are different in all CSV files. 







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.


Import multiple xml files(sharepoint webservices out put) to sql database using ssis



I am importing xml file in to sql server database i was able to do this task for single xml file in to sql database using ssis( i am using dataflow task, xml source, data conversion, oledb destination creating a ssis package).

Now i am having 3 xml files, need to store in different tables in sql server database can someone explain how i can import multiple xml files in to different tables in sql.can we create a xsd for each xml file while importing.

Dynamic PAckage to import multiple excel 2007 files in SSIS 2005


I want to import multiple excel 2007 files into Sql Server Database using SSIS 2005.

Can someone explain me the steps as i am new to SSIS.

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.

merging multiple tables in a single dataset to single table


 i have a stored procedure which returns three tables to a dataset ..... now i need to merge all three tables to a single table from d same dataset 

like dataset1 has table1 table2 and table3 .... i want all the three tabels to be merged into dataset1 itself .... instead of three diffrent tables so that i can show all three table data in a single datagrid  as a compact data and combination of 3 tables from d single dataset.....

can some1 help me please.....

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
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