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

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

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

Posted By:      Posted Date: October 09, 2010    Points: 0   Category :Sql Server


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.


View Complete Post

More Related Resource Links

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]

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?

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?



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

SSIS Object model - dynamically identifying the source and destination tables of the data flow/ tran


Hello All,

I would like to identifying the source & destination tables of the data flow or the transformation task.

I need to create a custom component which i can put into existing packages which can do some validation.

Please suggest ways i can accomplish this.




How to output data from master detail tables into text file but on different lines.



I have a requirement to output data from two tables, a master table and a detail table, to a text file such that the output looks like the following:

master record 1

detail record  1 1

detail record   1 2

master record 2

detail record  2 1

master record 3

detail record 3 1

detail record 3 2

detail record 3 3



Is this possible in one query?  I am sufficiently familiar with the bcp utility to do the output from any of the tables separately but have some difficulty with doing it for the two tables and have the result structured as outlined above.

SSIS : Pulling large number of tables from Source system



we are migrating a datawarehouse application from asp - sql 2000 to dotnet2.0 - sql 2005
currently we are in the process of coming up with an optimum solution for backend design using sql server 2005.
This application pulls data from 3 different source systems (oracle,sql server and mainframe db2).

we have 3 different staging databases corresponding to each of the source application database.
For eg : RetailGarments application is an oltp application with backend oracle database .
we have a staging SQL server database called Retailgarments_stg for our datawarehouse application.
similarly OnlineTravelBooking is an OLTP application with DB2 database .we have corresponding
OnlineTravelBooking_Staging database for staging this data.

Each of these source systems have more than 100 tables and we are currently pulling most of the
tables to our side from the source .

These staging data from different datasources are accumulated in to a cleansed Schema database
which is used for Reporting and other OLAP requirements.

Our question is related to data pull from the source system.
Current SQL Server database pulls these data from sorce system using Stored proc dynamic queries containing
link server openquery.

We would like to improve the performance as part of sql server 2000 to

Master-Detail with the GridView, DetailsView and jQuery's ThickBox

One more article about grids from Matt Berseth. This time he shows how to create master-detail with the gridview, detailsview and jQuery's ThickBox. He writes:
So I thought I would check some of this out and along the way rebuild my Master-Detail with the GridView, DetailView and ModalPopup Controls and replace the ModalPopup with jQuery's ThickBox. I found it pretty interesting - read on to see how it went

GridView inline Master/Detail record display

This code drop is part of a smash and grab series. If you're in a rush, you can just grab this code and insert it into your application, no understanding is required. When you have some time (ya, right) you may want to review the source code.

Master-Detail with the GridView, DetailView and jQuery's ThickBox

An example of using the jQuery ThickBox with ASP.NET's GridView and DetailView controls

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

Not getting multiple composite controls on master page


I have created two composite controls in the same assembly and registered that assembly in a master page using <%@ Register tag.

When I try to use these controls using the registered tag prefix I am getting only one control along with the tagprefix .

How to get all the composite controls present in the registered assembly?

Cutting Edge: Master-Detail Views with the ASP.NET Ajax Library


When you think of data-driven Web pages, most of the time what you really have in mind is a master-detail view of some cross-related data. Dino builds an example with ASP.NET AJAX 4 and jQuery.

Dino Esposito

MSDN Magazine January 2010

Inherit from multiple master pages


How does one inherit from multiple master pages?


I.e.  I have a base master page that contains several ContentPlaceHolders.









I want to have separate master page files (one for each ContentPlaceHolder) so that I have 4 separate files: 3 ContentPlaceHolders (each adding specific content) and 1 base master page.

The ASPX files will then inherit from the base master page.



SUM col from multiple tables

I am trying to SUM a column from each of 3 tables, where an ID field is the same in each table and the date range is the same also It would look something like this: table1      table2      table3      table4  amt         amt         amt         t4ID  ID           ID           ID  date        date        date SELECT SUM(t1.amt),SUM(t2.amt),SUM(t3.amt) WHERE (each table).ID = @t4ID AND (each table).date BETWEEN @date1 AND @date2 the t4ID, date1 and date2 will be chosen by the user from controls and supplied to the statement via a parameter array. I have tried many variations ( with more to go) to send 1 @t4ID parameter, for example, and use that in all 3 tables (same with the dates). Any help to nudge me in the right direction would be appreciated. Thanks

Master-Detail GridView in Single Page example with paging throws error

___http://www.java2s.com/Code/ASP/Data-Binding/MasterDetailGridViewinSinglePage.htmI followed example at the link above.When I enablind paging=true (by pagesize=3).and clicking to pages 2|3|4 etc and so;error occuringIndex was out of range. Must be non-negative and less than the size of the collection.at cs line :sourceProducts.SelectParameters[0].DefaultValue = gridMaster.DataKeys[e.Row.DataItemIndex].Value.ToString();got any idea?what should I make to fix this ? is this error  fixable ?thanks much.

SSIS and XML Source

I have xsd schema. I had 2 problems that I needed to fix. 1. guid type I needed to change to string and 2. ErrorCode column that I had in xsd schema gave me duplicate error in SSIS and I renamed it. Now I am executing data flow and it runs sucessfully but 0 rows in the end of it and my XML has data in it. I do not get any error messages just this info. I am also trying to debug but it does not even open grid for debugging. What is it? Why I do not get any data back from it? Warning: 0x80047076 at Data Flow Task, SSIS.Pipeline: The output column "OrderIds" (104923) on output "ShippingOrderDetail" (18903) and component "XML Source" (1) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance. Warning: 0x80047076 at Data Flow Task, SSIS.Pipeline: The output column "Output_Id" (104926) on output "ShippingOrderDetail" (18903) and component "XML Source" (1) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance. Warning: 0x80047076 at Data Flow Task, SSIS.Pipeline: The output column "Copy of Guid" (105060) on output "Data Conversion Output" (53499) and component "Data Conversion" (53497) is not subsequently used in the Data Flow task. Removing this unused
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