noob question of how to combine two tables from two different servers.

I got two tables from 2 different server A and B.

I do a OLEDB source [server A] with "select ID, currencyNo, exchangerate from table A"

I do another OLEDB [ server B] source with "select currencyNo, currencyName from table B"

i want to combine these two OLEDB sources with a resultset

"select ID, currencyNo, currencyName, exchangerate from table A , B

where A.currencyNo = B.CurrencyNo"

how do i do this in SSIS? sorry if i m a noob. I dun want to use linked servers. can someone help?

Running that kind of query is not something that SSIS is going to do magically for you. You have to ask your DBA to set link servers (assuming they are SQL Server).

Other option is to use 2 OLE DB source and then use a Merge Join transformation to merge both sources in a single output.

Both solutions may impact negatively the performance; but that will be really an issue depending in other factors like volume of data and hardware configuration.

A third option is to break work in 2 pieces; first extract data from each table and load it in a common database and then once both tables are in the same database you can use a single ole db source component.

As you can see you have several options

Rafael Salas

|||Just remember that if using a Merge Join transformation, your inputs need to be sorted on the key. Also, it is best to do the sort in your source SQL if possible, and avoid using the sort transformation in SSIS.

Phil|||

i got problems using the merge join....it say i need to sort the data....

|||That's what I said you needed to do in my previous post.|||Thanks, i solved my problem!!

No value given for one or more required parameters.

HI everyone, pls can u tell me whats the error in my code?

I am connecting to an oledb database

sql_edit_record = "UPDATE Anamnese SET ";

sql_edit_record += "ID=?,";

sql_edit_record += "Sexe = ?,";

sql_edit_record += "[Date de Naissance] = ?,";

sql_edit_record += "Classe = ?,";

sql_edit_record += "Ecole = ?,";

sql_edit_record += "Adresse = ?,";

sql_edit_record += "Telphone = ?,";

sql_edit_record += "[Cot de signalement] = ?,";

sql_edit_record += "[Motif de consultation] = ?,";

sql_edit_record += "[Histoire familale] = ?,";

sql_edit_record += "[Histoire mdicale] = ?,";

sql_edit_record += "[Histoire dveloppementale] = ?,";

sql_edit_record += "[Histoire scolaire] = ?,";

sql_edit_record += "[Situation socio-conomique] = ?,";

sql_edit_record += "[Histoire comportementale] = ?";

sql_edit_record += "WHERE Nom = ?";

//sql_edit_record = "UPDATE Anamnese SET Sexe = 'M' WHERE Nom = 'Dory'";

db_adapter.UpdateCommand.Connection = oledb_connection;

db_adapter.UpdateCommand.CommandText = sql_edit_record;

using (db_adapter.UpdateCommand)

{

textBox1.Text = sql_edit_record;

db_adapter.InsertCommand.Parameters.Add("@.ID", OleDbType.VarChar).Value = current_row.ToString();

db_adapter.InsertCommand.Parameters.Add("@.SEXE", OleDbType.VarChar).Value = sexe;

db_adapter.UpdateCommand.Parameters.Add("@.DATEDENAISSANCE", OleDbType.VarChar).Value = date_de_naissance;

db_adapter.UpdateCommand.Parameters.Add("@.CLASSE", OleDbType.VarChar).Value = classe;

db_adapter.UpdateCommand.Parameters.Add("@.ECOLE", OleDbType.VarChar).Value = ecole;

db_adapter.UpdateCommand.Parameters.Add("@.ADRESSE", OleDbType.VarChar).Value = adresse;

db_adapter.UpdateCommand.Parameters.Add("@.TELEPHONE", OleDbType.VarChar).Value = telephone;

db_adapter.UpdateCommand.Parameters.Add("@.SIGNALEMENT", OleDbType.VarChar).Value = signalement;

db_adapter.UpdateCommand.Parameters.Add("@.MOTIF", OleDbType.VarChar).Value = consultation;

db_adapter.UpdateCommand.Parameters.Add("@.HIS_FAM", OleDbType.VarChar).Value = his_familiale;

db_adapter.UpdateCommand.Parameters.Add("@.HIS_MED", OleDbType.VarChar).Value = his_medicale;

db_adapter.UpdateCommand.Parameters.Add("@.HIS_DEV", OleDbType.VarChar).Value = his_developpementale;

db_adapter.UpdateCommand.Parameters.Add("@.HIS_SCOL", OleDbType.VarChar).Value = his_scolaire;

db_adapter.UpdateCommand.Parameters.Add("@.HIS_SOCIO_ECO", OleDbType.VarChar).Value = his_socio_eco;

db_adapter.UpdateCommand.Parameters.Add("@.HIS_COMPORTEMENTALE", OleDbType.VarChar).Value = his_comportementale;

db_adapter.UpdateCommand.Parameters.Add("@.NOM", OleDbType.VarChar).Value =cbox_name.Text;

db_adapter.UpdateCommand.Connection.Open();

db_adapter.UpdateCommand.ExecuteNonQuery();

db_adapter.UpdateCommand.Connection.Close();

}

Without seeing any error messages it's hard to tell, however the first thing that stands out is the following:

sql_edit_record += "[Histoire comportementale] = ?";

sql_edit_record += "WHERE Nom = ?";

You should insert a space after the question mark on the first line, like so:

sql_edit_record += "[Histoire comportementale] = ? ";

sql_edit_record += "WHERE Nom = ?";

You may also find that you have to wrap the placeholders in single quotes where your table columns are of a textual datatype, for instance:

sql_edit_record += "[Histoire comportementale] = '?' ";

sql_edit_record += "WHERE Nom = '?'";

Is there a reason that you aren't using stored procedures to perform this task?

Chris

|||

Thanks for ur reply, the error is :

OleDBException was Unhandled

No value given for one or more required parameters.

Allthough I put the space after the ?.

|||

Sorry to state the obvious, but could the problem be that you haven't assigned values to all of the parameters?

Thanks
Chris

|||

Would an empty string("") raise such an error?