Solved

Selective Insert into a new table from multiple tables SSIS

Posted on 2010-09-24
6
760 Views
Last Modified: 2013-11-10
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.

0
Comment
Question by:misermice
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 7

Expert Comment

by:Cboudroz
ID: 33754715
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
 

Accepted Solution

by:
ysovol earned 250 total points
ID: 33754841
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
 
LVL 16

Assisted Solution

by:vdr1620
vdr1620 earned 250 total points
ID: 33755854
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
 
LVL 1

Author Comment

by:misermice
ID: 33980593
hhh
0
 
LVL 1

Author Closing Comment

by:misermice
ID: 34504739
Thanks for the detailed explanation.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

632 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question