.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

first ssis project - with master child table

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

Hello all,

I'm planning to develop a new SSIS project where I have a data in a table (Raw form) and I want to put into our production database. I just want to start with a test project and if you provide me little inside that will be helpful.

Here is what I'm trying to do

Typical order table (Order Header and Order Detail)

Temporary table with raw data which need to import in Order Table.

Order Header contains auto increment primary key which needs to be in order details table. So how should I develop a project where I go thru each record of temp table, write a record in order table, get order id (pk) and write data in order detail table.

Hope it is very much clear.

Hopefully I will get expert advice soon.

Thanks in advance,



mark it as answer if it answered your question :)

View Complete Post

More Related Resource Links

Adding Oracle table into SSIS project --Oracle error occurred, but error message could not be retrie

Hi all I am new to design SSIS packages,to day I stated creating package SSIS package and adding the Tables,while adding tables,one of the table giving an below error . I verified the table structure it has one "CLOB" datatype. can some body help how to add this table( whole idea of the package is I have Dump those tables with structure and Data to SQL Server 2008 Database)  =================================== Oracle error occurred, but error message could not be retrieved from Oracle. Data type is not supported. (Microsoft Visual Studio) ------------------------------ Program Location: at System.Data.OleDb.OleDbCommand.ProcessResults(OleDbHResult hr) at System.Data.OleDb.OleDbCommand.PrepareCommandText(Int32 expectedExecutionCount) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.OleDb.OleDbCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.DataWarehouse.Design.DataSourceConnection.FillDataSet(DataSet dataSet, String schemaName, String tableName, String tableType) at Microsoft.AnalysisServices.Design.DataSourceDesigner.AddRemoveObjectsFromDSV() Thanks in Advance.   SNIVAS

SSRS Reporting with Sub Report or Master-Child Table.


I have a list called "Product" which contains list of products with category. The product is available in More then one category. The product category is available in list called "Category".

Following table explain how the data is strored in the "Product" List. 

Product ID   Name   Category 
P1  Product1  Cat1
P2  Product2 Cat2
P3  Product3 Cat3
P4  Product4 Cat1, Cat3
P5  Product5 Cat2, Cat3
P6  Product6 Cat4
P7  Product7 Cat1,Cat4

Now i want the data to displayed SSRS report in following way. So kindly provide valueable inputs to achive same. 

GridView control to show master-child or master-slave data, written in c#, asp.net

I previously made a master slave datagrid using Visual Studio 2003 .Net (original article) that would allow users to show data to clients using collapse and expand features. These features however did not carry over to gridview for many users. This tutorial will explaing how to create a collapsable/expandable gridview in frameworks 2.0 and higher

Display column from child table. Possible ?



I'am using dnamic entity with EF4. On a list page of a table, I would like to display a column containing information from a child table of the current element. Sample :

Order List :

Order Data | Required Date | Shipped Date | etc... | ... | Customer Name (foreign key with tostring() method override) | Customer Postal Code (Column that I want to add) |

I don't known how to do that. Is it possible ? Maybe I have to to create my own Metadata proxy that will add dynamicly a column on the MetaColumn list of the table.

Does someone have an easier or better idea ?

Thank you for any help.

Do not see the table in Database after moving the project folder



I created a ASP.NET project under Visual Web Developer 2008. Under that project I had a Database (along with ASPNETDB.MDF) which has about 3/4 tables (I had SQL server 2008). Everything was working fine (like insert, delete, etc.). And I was able to view the tables in database explorer view.

Then I had to format my computer for some reason and I copied the whole project in a USB drive and then after formatting the computer I copied the folder and tried to open it in Visual Web Developer (now I have VWD 2010). I can see the both Database under App_Data folder (ASPNETDB.mdf and the DB I created). ASPNETDB has all the tables and data as expected. But I do not see the tables under the DB I created.

I also tried in one of my friends computer who has Visual Web Developer 2008. But same result.

I am just wondering if anyone can help me with retreiving the table. I have a bad feeling that I might needed to do something to backup the data (not sure though). But the tables should be still there ... isn't it ?

Any help will be appreciated. Thank you in advance.

InfoPath and Access - two parent tables and one child table


Hi there,

I'm trying to link an InfoPath form to an Access database, I want to connect the InfoPath form to 3 tables in the Access database but InfoPath will only recognise parent-child relationships in a series (e.g. the "Company" table is the parent to the "Customer" table, which is again parent to the "Orders" table)

I need to have two parent tables and one child table, though (e.g. "Customer" is parent to "Orders", but "Inventory_Item" is also parent to "Orders"). Is there any way to establish this in InfoPath? I'm using Windows XP, InfoPath 2007 and Access 2007.

Cheers, Patrick

SSIS Package Transaction locks entire table

Hello, I have SSIS package that using transactions and working as expected. But this locks all the tables involved in the data flow that are get inserted/loaded. If I query those tables during package execution the query waits till SSIS releases the lock. But I can still query those tables using NOLOCK hint. Is this possible SSIS will lock only the rows that it insert/update/delete etc? So that other user can still use that table or another instance of same SSIS Package can load data into those tables? My Package: In the package level I have TransactionOption = Required. The Sequence (Data Flow is inside this sequence) TransactionOption = Supported. I had some issue in my package that I posted here: http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/dfb5a8b9-d43c-452b-bb21-d6aea192fc97 and also resolved. Now I descovered this issue and trying to resolve. Thanks, Prabhat

SSIS XML Source with random child ID??

Hi, I am trying to load a XML document using SSIS into relational tables and pulling my hair out.  In it's simpliest form I have: <Batch>   <POSBasket>     <Header>       <SomeDetail/>     </Header>   </POSBasket>   <POSBasket>     <Header>       <SomeDetail/>     </Header>   </POSBasket> </Batch>   Now, when you have a parent child situation, you get a column in the output called xxx_Id, in my case, POSBasket_Id. This allows you to relate your header back to your root and SomeDetail back to header etc etc. Loading all of this in parrallel does not work because there is nothing unique once the load has happened more than once. i.e. You will have repeating POSBasket_Id's as well as Header_Id's. I ended up loading POSBasket into a table with an identity column. Once done, I would load the header details and do a lookup (eek) on the POSBasket table by using POSbasket_Id "where isComplete = 0" (A flag to tell me current import). Great, this works find with 1 item in my document. Add a second (2 POSBaskets) and it randomly works. (???) Add more and it hardley works. The look ups are failing with "no match found".   On using Data Views, I see that each XML source creates different POSBasket_Id's. Some matching, some not. Also seem to randomly match. My question is why? Why would there be t

Master Table

My application consist of region,compaign(IBM,Microsoft,Dell),Line Of business(lob),weekly schedules of employees,Employee Data.User(Company Authorityuploads weekly schedules of the employee by selecting region,Compaign and LOB in dropdownlist. Should i need to use master table for this,if yes can i use same master for the all the compaigns.One authority may upload dell copmaign data with 500 employees schedule,another authority may upload microsft compaign schedule. Give me table struture for this. EmpNo,EmpName,Monday(date and tiimings in and out).sunday,Region,Compaign and LoB

How to load a new table with the value of a variable from SSIS package?

Hi, i have two variables in a SSIS package Var1 and Var2. Both of these variables have values. is there any way i can put the values of these two variables in a new table? e.g In New table col1 having value of Var1 and col2 having value of Var2. Thanks

C#: How to add a row to a child table with a DataSet backing?

On the back end I am using XML files, xsd schemas a DataSet(dsThingPrograms) and DataTables. I have a parent ->child table relationship defined through the DataSet designer as such:   things[RFID(pkey), name] programs[ID(pkey/autogen), name, path, thing_RFID(FK references things->RFID)]   In my code I have:   dsThingPrograms.things.AddthingsRow("lala" , "00ffe4" ); dsThingPrograms.things.WriteXml("path\to\things.xml" , System.Data.XmlWriteMode.WriteSchema);   which works fine. However the problem comes when I try to add a row to the Programs table. When I do:   dsThingPrograms.programs.AddprogramsRow("someprog" , "some\path" , <???>);   VS wants me to add a "dsThingPrograms.thingsRow parentthingsRowByFK_things_programs" where the <???> is. I've tried a bunch of things but I could not satisfy that requirement and can't even get the code to compile. What is supposed to go in there to satisfy the FK property?

How to send record(which is a weblink) from a table to the value of the variable in SSIS package and

Hi Folks, I have table called Table1 with columns, col1 and col2 with col1 having weblinks for the report and col2 the name of the report. Now, i have a package with a variables var1 and var2 which should get the col1 and col2 values respectively from table1 and send it through an email. if the weblink gets updated in the table, package should send the updated link. i know the reverse way of it but trying to do somethig like this. Appreciate any help from you guys. Thanks

Accessing controls / events on a master page from child page

I have a problem i am trying to work out the best method with my limited skills. Im using vb.net ( VS 2010  ) I am using a dll for an IFrame , which is call from code behind.  On my site I havea masterpage which has two gridviews. ( GrdViewWebBureau is the one i am using for this  query)  GrdViewWebBureau is contained within an update panel and updated on a timer control trigger. GrdViewWebBureau updates the page with file counts and status from our clients. I can click on any one of the cells and the code behind is fired. What I am trying to do is put an IFrame on the master page and every time a user clicks on the grid, the Iframe is fired up.  I have tested the iframe and vb code behind from a dummy page and it works, however when the Iframe is on the master page it doeant work.  What I am tryingh to achieve is that no matter what page a user is on the site, they will be able to click on the GrdViewWebBureau and the iframe will fire with the correct childpage. Each webPage may also have an Iframe as well, so i dont know if it is possible to make the IFrame global and just call that control ??  I have no idea if 1 this is possible with the code i have and 2, if i have the brains to understand the who what why and when. Please can you look at my code and pass anyt suggestions / help /  solutions. Master Pa

Integrate SSIS project in Windows Form

I have a windows form project in which I would like call a SSIS project passing 2 variables simlutaneously...How do I accomplish this?

SSIS to MS Project

Hi All, My requirements are that I am supposed to export a lot of data from a work tracking system (MS SQL based) to MS Project 2003 or higher. Can any one guide me how I'll accomplish this? Thanks!GBM

How to declare a table variable in SSIS and then insert rows into it

Hello everyone,   I'll try to explain my problem as clearly as I can: 1)I have an Execute SQL Task that's inside a Foreach Loop Container. 2)Inside the Execute SQL Task, I have an int OUTPUT column. 3)With each iteration, I need to insert the data from the INT output colum into a table variable. 4)After the Foreach Loop finishes the iterations, I need to use the table variable to create a report based on the data inside.   My question is the following: How can I declare a table variable so that I can do all of the above?   Thank you, CostinP

can create table statement be used in and ssis expresssion?

I need to modify the preparation sql task built by the import wizard. I wanted to create an expression  using the create table sql but insert the variable to append the date and another descriptor  to make it dynamic. The source is Excel The expression would look something like this "Create Table" + @user::descriptor +  @user::todaysdate+" ( name varchar(50) ..........." SSIS cannot evaluate the sql although it runs fine in ssms The column mappings will always be the same but the table name needs to be dynamic
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