hello friends i am using SSIS for migrating Oracle to SQl 2008 migration. My first question is which transform should i use to convert oracle data types to the sql datatypes? Second question is i am already experimenting the SSIS on test server what i figured out is i am getting very weired schema. So how shd i map oracle schema to sql server schema? Thanks in advance dimrd_SQL

First question: Use a Data Conversion Transform in your Data Flow pipeline to convert the Source data to its desired format for inserting into SQL. FYI: Some of the columns my NOT need to be converted as I'm sure that Oracle and SQL have a lot of common data types. If you are using an OLE DB adapter for Oracle, it should send data to the pipeline in standard SSIS data types. Second: What do you mean by "wierd schema"? What do you mean by "map oracle schema to sql server schema"?Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

thanks man for replying I use data conversion task i am able to convert the data types but it started another issue that i am getting double the number of columns like ACTIVITY column i am getting ' copy of ACTIVITY' BUT SAME DATATYPE. Same thing i am getting for other columns. Is there any thing wrong with settings at data conversion transform? and about the second question actually on oracle side my tables are under schema like MIC and name of my sql server 2008 is MOM-MSDBSQL089.kom.us\Affinity but the table name which i am getting on sql side is MOM\my login.tablename. So its mapping to MOM\my login schema. How should i change that into dbo schema? THANKS dimrd_SQL

1) When you set up a Data Conversion Transform and click one of the columns, it adds it to the list at the bottom with a name of "Copy of <Original Column Name>" and it defaults to the same data type because, hey, even Bill Gates can't read your mind (yet) to know what the desired data type is. So you have to then manaully select the new data type. If you don't, then your new column will have the same data type as its original, kind of a waste. You can re-name the new column, but I suggest you do NOT try to use the same name as the original because then you will have TWO column of the same name with different types, and you will only be able to identify them according to the Object that added them to the pipeline, as in "Source.MyColumn" or "DataConversion.MyColumn". 2) I would suggest that you start by creating the tables you want in the dbo schema in SQL 2008. If you want to be brave, then you might want to consider grouping tables of like functional area or business sector into their own dedicated schemas. Take a look at the AdventureWorks database for some good samples. It has schemas for HR, Accounting, etc. Hope this helps.Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

todd thanks you are rt so what i am doing now is just making the table on the sql side with the dbo schema and with all the same columns as oracle and while mapping on the destination side what i am doing is that i am just mapping the 'copy of activity' column to activity column at destination so is this rt? secondly varchar2, date datatypes are ok they are mapping automatically but for mapping NUMBER datatype i am using 'double precision float[DT-R8]' data type while dataconversion transformation. Is it rt? thanksdimrd_SQL

Yes, you would map the "Copy of Activity" from the Data Conversion task to the Activity column in your destination. As for numeric data types, is uppose it depends on what types of number you are dealing with. I'm not sure how the Oracle Provider interprets the Oracle data types and maps them to SSIS data types. This is a case where you would need to know the Oracle data and what it represents. If you have a field like "InvoiceID" and your oracle Source thinks it should be 'double precision' or float, then I might quesion that. I mean really, wouldn't it make sense to have InvoiceID be an Integer? So look at the actual data in Oracle and talk to the developers on that side (assuming they would stoop so low as to even speak to a lowly SQL DBA!) and get their input.Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

thanks todd i will respond back after having dicussion with developers and will come up again for discussion. uptill now you helped alot Thanksdimrd_SQL