.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

Union all in SSIS join two tables row wise or column wise

Posted By:      Posted Date: October 27, 2010    Points: 0   Category :Sql Server
Union all in SSIS join two tables row wise or column wise

View Complete Post

More Related Resource Links

How to insert column wise data as a seperate rows using ssis


Hi All,

I have my data in excel like this ....


JOIN Performance wise which is better a.id=b.id or b.id=a.id

While joining 2 tables A & B... which one is better? from A a join B b on a.id=b.id --or-- on b.id=a.id   Is there any difference?

Convert excel rows wise data into column wise


public static string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;";

               OleDbDataAdapter adapter = new OleDbDataAdapter();

            OleDbCommand selectCommand = new OleDbCommand("SELECT * FROM [Sheet1$]");

            OleDbConnection connection = new OleDbConnection(connectionString);

            selectCommand.Connection = connection;

            adapter.SelectCommand = selectCommand;

            DataSet cities = new DataSet();


SQL - Join Tables on Column Name and Row Field Value



I have two SQL tables (SQL 2005) that I need to join but am in need of some assistance. Essentially I need to join a column name with row field values.

The first table contains the following columns

  • Loan ID
  • SSN
  • Ord_SignedApp
  • Ord_Title
  • Ord_Reg
  • Ord_Name

The second table contains the following columns

  • Item
  • Ord

However, the rows in the second table for the Ord column contain the following values:

  • Ord_SignedApp
  • Ord_Title
  • Ord_Reg
  • Ord_Name

I need to join the row field value of the second table with the column name of the first table. I want to display the values of the respsective columns of table one (Ord_SignedApp, Ord_Title, Ord_Reg, Ord_Name) and place them in a new column. These values are represented as dates

Any assistance you can provide would be much appreciated.

Thank you for your help,




need to join two tables and also convert top 3 rows to column


Hi, I have two tables, CUSTOMER and PAYMENT_DETAILS. I need to extract "name" and "phone" from CUSTOMER table and TOP 2 payments for matching email from PAYMENT_DETAILS table. Kindly refer to the attached image for table structure and Result required.




IN,EXISTS or INNER JOIN - which one is the best (performance wise)


hi all,

when we compare  IN,EXISTS or INNER JOIN with performance  wise which one is the best? Any Example to prove it?


Tharindu Dhaneenja

Tharindu Dhaneenja (http://dhaneenja.blogspot.com)

Join Two Tables and Prepare Report



            I have a select query which is executing well. Now, I want to add one more field to that query. That field is not in the current query table, It is in the another table.

How do I join those two tables and get that field value in the existing select query.?


SSIS Changing Column Order during Transformation

First let me say, I really can't believe this chain of events myself--and they are happening to me. I am upgrading several DTS packages to SSIS on what will be my new production server.  These packages create tables, export them to a flat file, and ftp them off to other locations. What is happening (on the SSIS side) is that the OLE DB Source is reordering some of the columns on its own (moving them to the end of the table/file.  Then when my pickup/load routines run, the data is out of place and they fail. Can anyone please explain what is happening here with the mapping.  I have evaluated the table and the columns are in the order that I expect.  When I preview the source table in the OLE DB Source Editor the columns are in the correct order/alignment, but when them in the OLE DB Source Editor --Columns section within BIDS the order is changed arbitrarily. I have been somewhat successful (2 out of 3) in being able to re-map the data, but this last table just doesn't want to change.  Thanks in advance for any help and/or information you can provide

C# newbie stuck - trying to access column data in a SharePoint list in an SSIS script task

Hello, I'm sure this is the simplest question but I can't figure it out, even with Google's help. I am trying to stumble through some C# code in an SSIS script task and I am frustrated that I can't figure out how to do the easiest things.  I eventually want to find data in a column,and then use another list as a lookup to replace that value with another where the existing value matches a value in the lookup list.  So, the data in my (multiple choice) column might be "apples; bananas" and in another list I have a row that contains two columns, the first holding the value "Apples" and the second containing "Red Delicious" and my original column should read: "Red Delicious; bananas." But, alas, I can't even figure out how to see the data that is in a column. Here is my code: /*<br/> Microsoft SQL Server Integration Services Script Task<br/> Write scripts using Microsoft Visual C# 2008.<br/> The ScriptMain is the entry point class of the script.<br/> */<br/> <br/> using System;<br/> using System.Data;<br/> using Microsoft.SharePoint;<br/> using Microsoft.SqlServer.Dts.Runtime;<br/> using System.Windows.Forms;<br/> using Microsoft.SharePoint.Utilities;<br/> <br/> namespace ST_08becda4c05c49cd9f30ea76110076cd.csproj<br/> {<br/> [


Hello EveryOne,               I am making a monthly attendance details of all employees who is working in my company. I am using sql server management studio 2005.       My table is in this format. staffcode   name                                   date            Intime   Outtime Duration                         1               Amit                                  01-08-2010    00:00    00:00    00:00 1              Amit                                   02-08-2010    09:52    20:01    10:09 1&n

any alternative for join tables?

 Hi guys, is  there any alternative for joining tables in ado.net? The problem is i have 2 tables: Transaction pkTransactionID TransactionDate FKTransactionCategory : refers to PKCAtegoryID   Category PKCAtegoryID CategoryName   I need to show a joined information from 2 tables in a gridview and be able to update data in tables. I can join them using linq to entity and show them in gridview. But i need to handle update and insert by myself then. Is there any way to make mapping so i can handle the data in those 2 tables as one table and bind it to bindingsource? Thanks in advance.      

ssis sql 2000 image to 2008 varbinary Failed to retrieve long data for column

Hi, I have a task that to migrate the image type column from sql 2000 to varbinary type in sql 2008. The source column having 3812353 max datalength size for the column. The package always failed with following error message. [OLE DB Source [13177]] Error: Failed to retrieve long data for column "attch_file_content_t". [OLE DB Source [13177]] Error: There was an error with output column "attch_file_content_t" (13209) on output "OLE DB Source Output" (13187). The column status returned was: "DBSTATUS_UNAVAILABLE". [OLE DB Source [13177]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "output column "attch_file_content_t" (13209)" failed because error code 0xC0209071 occurred, and the error row disposition on "output column "attch_file_content_t" (13209)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure. Meng Chew

Looping through Excel 2007 tables in SSIS?

Saw the (http://msdn.microsoft.com/en-us/library/ms345182(v=SQL.90).aspx) msdn article on MSDN, however I need to loop through Excel 2007 tables, which is not supported via the .Net Connection.  Is there a way to loop through the 2007 sheets without resorting to a script component?Steve Walker

Performance-wise, what is the best way to do a....

I have a combination of 2 tables (Header/Detail) that will have Millions of rows within a year or so.    I have to do an Insert into the detail table if the Header information exists and an Insert into Both if it does not exist.  there are several ways to do this (if Exists, If not exists, attempt the insert and catch a restraint violation). I was hoping someone could recommend the most efficient way to do this as opposed to me setting up several variations and try to "Time it" and see which is bettter. the header contains an identity column, Claim Number varchar(38), Partner varchar(30) and 3 other small varchar fields. the detail column has an identity column, a Bigint column that matches to the Identity from the Header, a "Date" field that is char(08) "CCYYMMDD" format,  batchNumber varchar(10) and 2 other varchar fields. it the Claim Number / Partner already exists on the Header table, I want to insert a new Detail record (Using the Old HeaderKey) and if it does not exist, I will insert the Header and then insert the Detail record (using the New headerKey). any suggestions are appreciated. Thanks, Daryl

Join two tables

I have following SQL command   SELECT     software_communication_comments.observation_comment,child_communication_observation_outcomes.communication_outcome_status, software_community_comments.observation_comment ,child_community_observation_outcomes.community_outcome_status From     child_observation,Child_Basic_Information  ,group_table ,software_communication_comments ,child_communication_observation_outcomes ,software_community_comments,child_community_observation_outcomes  where (child_observation.observation_ID = '2'  and   child_observation.child_ID = Child_Basic_Information.child_ID and group_table.group_ID = Child_Basic_Information.Group_ID) AND (child_communication_observation_outcomes.child_observation_ID = '2' AND software_communication_comments.comment_ID =  child_communication_observation_outcomes.communication_obser_outcomes_ID) AND ( child_community_observation_outcomes.child_observation_ID = '2' AND software_community_comments.comment_ID =  child_community_observation_outcomes.community_obser_outcomes_ID   ) This is working well if both table (child_community_observation_outcomes and child_communication_observation_outcomes) have records belongs to child_observation_ID =2 ,if one table dont have a relevent record it doesn't display anything. I need to ret

Select column from all tables in database

I want to retrieve the name and phone columns from all the tables in my database not in systables....   Ok this works but i dont want to get it from just the test table I want to get it from all the tables that I create "USE mrpoteat SELECT name, phone FROM mrpoteat.dbo.test where name = name and phone = phone"

Rename a column in multiple Tables using sp_rename

Hi,Any work around for the below issue?Originally "LoadDate" column (see below scripts) was there both in tables DIM_Table_1 and DIM_Table_2According to change in requirement, LoadDate in both the tables to be chnged to LOAD_DATE. It has to be done through script-----------------EXEC sp_rename 'dbo.DIM_Table_1.LoadDate',     'LOAD_DATE', 'COLUMN'         -- Successfully executedEXEC sp_rename 'dbo.DIM_Table_2.LoadDate',     'LOAD_DATE', 'COLUMN'         -- Error: See below Error: The new name 'LOAD_DATE' is already in use as a COLUMN name and would cause a duplicate that is not permitted.-----------------
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