Link to home
Start Free TrialLog in
Avatar of Gowtham Ramamoorthy
Gowtham Ramamoorthy

asked on

SSIS package creation: using values from lookup tables the destination column should be updated without inserting any null values to other columns.

Hi Experts,

I need to transform one single column values(columnA) from TABLE A in one database to another column B in TABLE B in some other database.

When i transfer columnA for example has (Employee names) but in the destination TABLE B it should be the (Employee ID). I have a lookup table which has the employeeID for the matching employee name.

Conditions:
I need only one single column gets updated in the destination TABLE B. without affecting any other columns.

I know this can be done in SSIS and i have created
* source oledb
*Lookup Transform
*Destination OLEDB

But the problem is at the destination output TABLE B the lookup transform is inserting NULL values to the unmatched column.

Can someone please guide me which is the best way to do this ?
Similarly  i need to include various flows for the destination table from various databases.
Avatar of Vikas Garg
Vikas Garg
Flag of India image

Hello,

It seems that there are two things

One - If the matching table has all the employee name and having id column some nulls then

you can use derived column and replace the null with any value '0'

Two - if the matching table has few employee name then

you can redirect the no matching records to unmatched output, Then you can use the derived column at unmatched output and add a column with id = 0
Then use union all transformation to join both matched and unmatched.
And then store the result to the destination table.

Hope this will help you...
Avatar of Gowtham Ramamoorthy
Gowtham Ramamoorthy

ASKER

Hi Vikas,

Thanks for your response.

My purpose is : Every time when i run the package I need to insert  new row in the destination TABLE B with column values from different databases and lookup reference tables. Please give me some idea on how to achieve this in SSSIS.

To the answer given :
My purpose is to insert(or Update) only one single column in the destination database from the matching lookup values in the lookup table.

I need one single column(for eg: Employee ID)  from lookup table to be inserted in the destination table.
Problem is after the look up transform i'm getting the employee ID column with no null values but the remaining columns as NULL in the destination table.(I mapped only the column(employee ID) to the  output table in OLEDB destination)

Is the mapping done by me is right  ? to get one single column values in output table.

i'm having a question here.... if i didnt map the other unwanted columns to the destination table in destination OLEDB will that be null value sin the destination table??
My point is i need those column data to be updated not inserted with other column values as null.



I can give you a example of screen shots of the package so that it would explain things a little bit more.(if you require)
I need to know what can be done if both the codition mentioned above exsist.

Thanks
Gowtham
Hi Lucky,

I already looked into these links but it doesnt help.

Regards
Gowtham
Can you give us some sample data for all 3 tables so that we can give try give you a working sample.
Please check the attached screen shot of the SSIS package which I created.

Also see the attached table querys  and data mock up for acheiving this one. In the attahed querys I need employee name from (Formula table) which needs to be looked up in the (lookup table) with the matching employeeID and that EmployeeID  from lookup table should be (Inserted ) in the output table column(EmployeeIDoutput).( I dont need null values in other columns in output table because i need to insert values in the other columns with the same process)
ScreenShotSSIS.png
ExpertExchange1.txt
Hi,

You are getting null values other columns since you might have not mapped them to the input columns.

You have to map other columns of the destination table to appropriate columns coming from the lookup output task.

User generated image
I have shown this in image. If you haven't mapped the other columns its obvious that then will remain NULL.
What if I want the other columns in other tables to also perform the same lookup operation and then do the insertion in the destination table. Is there anything I can do like merge join after the lookup transform?
Hi,

You need to mention in which output column what you want to map ?

do you need to do this which the mentioned two tables ?

As you explained the process for employee name lookup and get employeeid from lookup table , you have to mention the logic for each column so that I can tell you how things can be done
If the other columns are not mapped , they would be inserted as nulls
In the package You can use a staging table as an oledb destination .
Then write an update statement to update the data in actual destination table from the data in staging table.
Hi Vikas,

My requirement is to populate data into one destination table where data coming from various tables in various servers which doesnt have link with each other.

All I need is thier particular column values from different  tables in different servers.

As I already mentioned :
Take for example the destination table has 10 COLUMNS
Each column value in destination table is populated from different columns in different servers.(One such example branch is the code i Just attached to make you understand.

My question:
Is it possible to insert data into the destination table where the destination column values are from different databases residing in different servers.(which has no link to join)
Hi Lucky,

Is it possible for me to populate data in the staging table where I can create and insert values to individual columns ?
Hi Lucky,

Could you guide me in creating a staging table and then i can use it for pushing values inside the destination table.

Can you explain it to me so that I can try this on my project. ?

Thanks
Gowtham
ASKER CERTIFIED SOLUTION
Avatar of Anil Golamari
Anil Golamari
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi Gowtham,

For this problem please Create source as Oledb source and then use lookup and in lookup mached output connect to OLEDB command there write the update command.

if you did not get the answer keep in touch me .