December 5, 2011 at 2:12 pm
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.
Thanks!!
December 5, 2011 at 2:19 pm
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 destinations.
December 6, 2011 at 3:02 pm
Your best bet is to just use a stored procedure and run it either directly in the database or through an execute SQL task.
There's no reason to use SSIS for this approach. Having a script that says
INSERT INTO LookupTable (Fields)
SELECT DISTINCT Fields
FROM Table
LEFT JOIN LookupTable ON Fields = Fields
WHERE LookupTable.ID IS NULL
And writing this for each table, is your best bet for sure. It will be way faster than using any SSIS approach.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply