Insert data into to couple of columns in a table using SSIS

Should be in SSIS Zone.
My task,
1. I have to insert data into a lookuplist table.
2. Lookuplist table, has all the lookup categories such Country(1000), State(1001), City(1002) and others.

Database is Sql server2005.
This is how the data looks on lookuplist.
UniqID LookupCatType lookupvalue lookupdescription
1 1000 USA United States of America
2 1000 JPN Japan
3 1000 CAN Canada
4 1001 NY New York
5 1001 MO Missouri
6 1001 IL Illinois

Let's say State/country exists on different tables in the source database & I have list.
1. I need to do either Union All or Merge or Merge Join to get all State values from different tables. Correct ?? I'm good till here..

2. How would i insert these States values to above tables, UniqID is Identity Insert so no problem there but LookupCatType needs to be 1001(manually).
a) Which component should i use especially has destination.
b) Can you send me small package if its simple or atleast send me some screen shots or put me on right track.

Note: I know how to do this using execute sql/script task but i'm trying to find out
if i can do this using data flow destination components.

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.

Jason YousefSr. BI  DeveloperCommented:

I'm sorry I didn't get the first part...if you can explain or show a screen shot..

Anyway If you're confident with your flow and sure that you did the merge or lookup and have the correct values, you'll need to add an "OLE DB DESTINATION"  opr SQL SERVER DESTINATION if the SSIS sharing the same server with the DB, create the connection to the server, select your table and then map the columns to the flow output.


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
As a passing observation, I believe that the Execute SQL task will provide you with a better and more efficient solution, assuming that you have a well written SQL INSERT statement . . . which isn't that hard to do (see code sample). ;-)  It is often much more effecient to use a simple task rather than to use 1 rather complex task.  

I relalize that this may be just an exercise for you to try to learn the more complicated method; however, unless you have another task in mind that will require the more complicated method, I would suggest that you not complicate things unnecessarily. ;-)

SELECT  1000 LookupCatType
            ,C.CountryAbbrev LookupValue
            ,C.CountryName LookupDescription
FROM   CountryListTable C
ON C.CountryAbbrev = L.LookupValue
SELECT  1001 LookupCatType
            ,S.StateAbbrev LookupValue
            ,S.StateName LookupDescription
FROM   StateListTable S
ON S.CountryAbbrev = L.LookupValue
WHERE L.LookupValue IS NULL;

Open in new window

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 2005

From novice to tech pro — start learning today.