Concatenate two columns and remove duplication in SSIS [Resolved]

Posted by vspsantu-21529 under C# on 4/2/2013 | Points: 10 | Views : 2001 | Status : [Member] | Replies : 5
Hi,

I need to import excel data to sql table. There is two columns First name and last name, now i need to concatenate these into a single column name as Name. How can i write query and where can write.

In SSIS, i am using Excel Source, Conditional Split, OLE DB Destination1, OLE DB Destination2, OLE DB Destination3.

And how i remove duplication records. Means, i debugging many times, the records are repeating. I doesn't need records are repeated.

How can i do above two things. Please tell immediately.

Thanks in advance.

Santhosh P


Responses

Posted by: Jayakumars on: 4/2/2013 [Member] [MVP] Bronze | Points: 50

Up
0
Down

Resolved
hi

refer this

http://firstlineofcode.blogspot.in/2012/06/ssis-package-export-data-from-database.html

Mark as Answer if its helpful to you

vspsantu-21529, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jayakumars on: 4/2/2013 [Member] [MVP] Bronze | Points: 25

Up
0
Down
try this code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OleDb;
using System.Data;

public partial class Default3 : System.Web.UI.Page
{
string strFileType = @"D:\Test.xls";
string strFpath = ".xls";
DataTable dt=new DataTable ();
OleDbConnection con;
OleDbCommand cmd;
protected void Page_Load(object sender, EventArgs e)
{

}
protected void Button1_Click(object sender, EventArgs e)
{
string query = null;
string connString = string.Empty;
if (strFpath.Trim() == ".xls")
connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFileType + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
else if (strFpath.Trim() == ".xlsx")
connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strFileType + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
OleDbConnection con = new OleDbConnection(connString);
OleDbCommand cmd = new OleDbCommand();
OleDbDataAdapter da = new OleDbDataAdapter();


query = "SELECT FirstName+LastName as Name,Empno FROM [" + "Sheet1" + "$]";
try
{
con = new OleDbConnection(connString);
if (con.State == ConnectionState.Closed) con.Open();
cmd = new OleDbCommand(query, con);
da = new OleDbDataAdapter(cmd);
dt = new DataTable();
da.Fill(dt);
}
catch (Exception ex)
{

}
finally
{
da.Dispose();
con.Close();
con.Dispose();
}


for (int i = 0; i < dt.Rows.Count; i++)
{
con.Open();
string query1="Insert into Ta values('"+ dt.DefaultView[i][0].ToString() +"','"+ dt.DefaultView[i][1].ToString() +"')";
cmd = new OleDbCommand(query1, con);
cmd.ExecuteNonQuery();
}


}
}

Mark as Answer if its helpful to you

vspsantu-21529, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: vspsantu-21529 on: 4/2/2013 [Member] Starter | Points: 25

Up
0
Down
Hi,

Excel Columns

ID Name Address Mob First Name Last Name City State Country





Mt DB Tables

1. tblProduct

ID Name Address

2. tblCustomer

Name(First Name+Last Name) Mob



Here i need to concatenate



3. tbltransact



City State Country



These are my Source and Destination.

How can i do this.

Can you please tell me. How can i write query. Should remove the dupilcation.




Santhosh P

vspsantu-21529, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jayakumars on: 4/2/2013 [Member] [MVP] Bronze | Points: 25

Up
0
Down
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OleDb;
using System.Data;

public partial class Default3 : System.Web.UI.Page
{
string strFileType = @"D:\Test.xls";
string strFpath = ".xls";
DataTable dt=new DataTable ();
OleDbConnection con;
OleDbCommand cmd;
protected void Page_Load(object sender, EventArgs e)
{

}
protected void Button1_Click(object sender, EventArgs e)
{

WebService wb1=new WebService ();
string Empame = "AAA";
if (Empame == "AAA")
{
string Rec1 = wb1.HelloWorld("Asp.net");
}
else
{
string Rec2 = wb1.HelloWorld("Asp.net", "Dotnet");
}


string query = null;
string connString = string.Empty;
if (strFpath.Trim() == ".xls")
connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFileType + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
else if (strFpath.Trim() == ".xlsx")
connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strFileType + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
OleDbConnection con = new OleDbConnection(connString);
OleDbCommand cmd = new OleDbCommand();
OleDbDataAdapter da = new OleDbDataAdapter();


query = "SELECT ID,Name,Address,Mob,FirstName+LastName as FullName, City,State,Country FROM [" + "Sheet1" + "$]";
try
{
con = new OleDbConnection(connString);
if (con.State == ConnectionState.Closed) con.Open();
cmd = new OleDbCommand(query, con);
da = new OleDbDataAdapter(cmd);
dt = new DataTable();
da.Fill(dt);
}
catch (Exception ex)
{

}
finally
{
da.Dispose();
con.Close();
con.Dispose();
}


for (int i = 0; i < dt.Rows.Count; i++)
{
con.Open();
string query1 = "Insert into tblProduct values('" + dt.DefaultView[i]["ID"].ToString() + "','" + dt.DefaultView[i]["Name"].ToString() + "','" + dt.DefaultView[i]["Address"].ToString() + "')";
cmd = new OleDbCommand(query1, con);
cmd.ExecuteNonQuery();

string query2 = "Insert into tblCustomer values('" + dt.DefaultView[i]["Name"].ToString() + "','" + dt.DefaultView[i]["FullName"].ToString() + "','" + dt.DefaultView[i]["Mob"].ToString() + "')";
cmd = new OleDbCommand(query1, con);
cmd.ExecuteNonQuery();

string query3 = "Insert into tbltransact values('" + dt.DefaultView[i]["City"].ToString() + "','" + dt.DefaultView[i]["State"].ToString() + "','" + dt.DefaultView[i]["Country"].ToString() + "')";
cmd = new OleDbCommand(query1, con);
cmd.ExecuteNonQuery();

}


}
}

Mark as Answer if its helpful to you

vspsantu-21529, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: vspsantu-21529 on: 4/2/2013 [Member] Starter | Points: 25

Up
0
Down
I need only ssis

Santhosh P

vspsantu-21529, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response