Selective Insert into a new table from multiple tables SSIS

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

table 1
C7 C8
a     2
a     5
c      6
g     8
a     5

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.

Thanks.

LVL 1
misermiceAsked:
Who is Participating?
 
ysovolConnect With a Mentor Commented:
Hi,

Firstly you are not clear about Column 2.
Secondly, the way you should go if you want to populate a table from information based on different source table is:
INSERT INTO DESTTABNAME (Col1, Col2...)
SELECT FROM SOURCEVIEWNAME (Col1, Col2...)

where DESTTABNAME is the table you want to be filled with data and SOURCEVIEWNAME is a view you have to create that combines & transforms the data (from the various SOURCE tables) in such a form that match the columns of DESTTABNAME.

In this case you would be writing something like:

insert into DestTableName (col1, col2, col3...)
select col1 || col2, some_column_from_table_1, t2.col3
from table1 t1, table2 t2
where t1.c7 = t2.col1(+)
and t1.c8 = t2.col2(+)

joining table1 with table2 in order to get your lookup value (the t2.col3 - Good, Bad)
0
 
CboudrozCommented:
maybe the following code will help you:
INSERT INTO my_table_in_SQL
(
	column_Name1
	, column_Name2
	, column_Name3
)
SELECT 
	COLUMN1 + ' ' +COLUMN2 -- WILL APPEND COLUMN 1 AND 2 WHIT A SPACE BETWEEN 
	, CONVERT(VARCHAR(100), COLUMN_TO_CHANGE_DATATYPE) -- WILL CONVERT DE DATA TYPE TO VARCHAR(100)
	, CASE  -- EXEMPLE OF A CASE IN T-SQL
		WHEN C7 = a AND C8 = 2 THEN 'GOOD'
		WHEN C7 = B AND C8 = 1 THEN 'BAD'
		WHEN C7 = C AND C8 = 22 THEN 'UGGLY'
		WHEN C7 = D AND C8 = 4 THEN 'NASTY'
		ELSE 'ERROR'
	  END
FROM 
	TABLE1 T1

Open in new window

0
 
vdr1620Connect With a Mentor Commented:
As you are using SSIS..you wouldn't need an insert statement... you can follow the below steps

1. Source - Oracle Database
2. Use Derived Column transformation Task (you can use below expressions)
    ---- ColumnName1 + ColumnName 2  (to concatenate your columns ,make sure that data types are same)
    ---- (Dt_STR,255,1252) ColumnName3  (change the data type conversion from DT_STR to one you need accordingly)
3. Use a lookup (use a sql statement and get the columns you need.. )
 ---- create a connection to your Lookup table, select SQl statement from dropdown and use SQl Like below
            SELECT C7,C*,C9 FROM LookUpTableName
 ---- in the Column Mappings map the Columns that you want to lookup on and then select the output column

4. Use a OLE DB destination --Map it to your destination Table and map the columns accordingly... The Derived Column  (ColumnName1 + ColumnName 2) should be mapped to Column1 of your destination and 2nd derived column should be mapped to second columns and c9 of lookup should be mapped to column3 of your destination table

here are some links that will help you

Lookup -- http://sqlblog.com/blogs/andy_leonard/archive/2010/02/16/ssis-snack-lookup-transformations-101.aspx
http://www.sqlshare.com/ssis-lookup-transform-basics_632.aspx

Derived Column - http://sqlblog.com/blogs/andy_leonard/archive/2009/02/04/ssis-expression-language-and-the-derived-column-transformation.aspx
 http://www.ssistalk.com/2007/01/23/derived-column/

 
0
 
misermiceAuthor Commented:
hhh
0
 
misermiceAuthor Commented:
Thanks for the detailed explanation.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.