We help IT Professionals succeed at work.

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.

Thanks!!
Comment
Watch Question

Sr. BI  Developer
Commented:
Hello,

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.

12-6-2011-8-03-59-AM.jpg
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. ;-)

INSERT INTO LookUpListTable
(
  LookupCatType
 ,LookUpValue
 ,LooupDescription
)
SELECT  1000 LookupCatType
            ,C.CountryAbbrev LookupValue
            ,C.CountryName LookupDescription
FROM   CountryListTable C
LEFT OUTER JOIN LookupListTeble L
ON C.CountryAbbrev = L.LookupValue
WHERE L.LookupValue IS NULL
UNION
SELECT  1001 LookupCatType
            ,S.StateAbbrev LookupValue
            ,S.StateName LookupDescription
FROM   StateListTable S
LEFT OUTER JOIN LookupListTeble L
ON S.CountryAbbrev = L.LookupValue
WHERE L.LookupValue IS NULL;

Open in new window