Selective Insert into a new table from multiple tables SSIS
Posted on 2010-09-24
I established a connection from oracle to SQL Server via SSIs package. There is a new table sitting in SQL Server with no data. Data is to be transfered from SEVERAL DIFFERENT TABLES FROM ORACLE.
HOW DO I DO THAT?
I know how to do it from single table.
NOw i have speacial requirements for each column
1st column-- > append of two columns from table 1
2nd column--> change date type of a column from table 1
3rd columns -> look for two columns in table 1 and using a switch case for every difrnt set of 2 values there exists a unique value in column 3rd column of table 2(look up table). so that unique value from table 4 is to be inserted.
more on 3rd column
Look up table
a 2 Good
a 5 Good
c 6 BAD
g 8 UGLY
a 5 GOOD
So for every record in i would have a CASE saying
IF C7 = a AND C8 = 2 ....INSERT ..GOOD
I have just four cases though.
so all those 1000s of records boil down to one of the four values in the corresponding column of target table in SQL Server.
When i did a direct transfer i have a query like
SELECT * FROM my_table_in_Oracle;
What do i have there now ??
and FYI the table is already created in SQL Server.
Do i do like
INSERT INTO TABLE VALUES
' SELECT CONCAT(COL1,COL2) FROM Oracle.Table1 ',
' SQL Code for second column depending on what needs to be inserted',
' SQL Code for second third depending on what needs to be inserted '
Im sorry for making it too lengthy.
Is this the way to do it ?
Or is there a pre defined package in SSIS for this kind of slective inserts.