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.

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.
Gowtham RamamoorthyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vikas GargAssociate Principal EngineerCommented:

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...
Gowtham RamamoorthyAuthor Commented:
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.

Anil GolamariCommented:

You can do it by doing a lookup transformation in SSIS. Below links has good amount of information.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Gowtham RamamoorthyAuthor Commented:
Hi Lucky,

I already looked into these links but it doesnt help.

Anil GolamariCommented:
Can you give us some sample data for all 3 tables so that we can give try give you a working sample.
Gowtham RamamoorthyAuthor Commented:
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)
Vikas GargAssociate Principal EngineerCommented:

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.

I have shown this in image. If you haven't mapped the other columns its obvious that then will remain NULL.
Gowtham RamamoorthyAuthor Commented:
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?
Vikas GargAssociate Principal EngineerCommented:

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
Anil GolamariCommented:
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.
Gowtham RamamoorthyAuthor Commented:
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)
Gowtham RamamoorthyAuthor Commented:
Hi Lucky,

Is it possible for me to populate data in the staging table where I can create and insert values to individual columns ?
Gowtham RamamoorthyAuthor Commented:
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. ?

Anil GolamariCommented:

These links have step by step instructions on how to create a staging table. Please follow these steps and let us know if you are able to create them.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Venkat Sudheer KothapalliCommented:
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 .
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.