column update

  • hello,

    this is the first time im writing something and i need ur help as soon as possible guys

    the scenario is

    I have a database in oracle which has like 150 tables.

    then i have a schema in sql server 2005 which has like 100 tables. Now I am supposed to import the data from oracle to sql server. I figured out that the best way to do this is by using the SSIS which i used just recently and it did a great job for the simple import. But there is a problem

    Lets suppost i have a table in oracle as

    emp

    having columns empid,empname etc

    and has 100 rows

    now there is a table called employee in sql and it has

    columns such as

    employeeid,employeename etc

    but no data

    i have to import the data from emp table to the employee table with the data going from empid,empname to

    employeeid,employeename

    Guys help me. i dont have a clue to solve the problem.

    thanx

  • you can use dataflow task in ssis...you just need to map the source columns to appropriate columns in Destination provided the datatypes should be same...

  • thanks for the reply

    But that is giving me some weird kinds of errors such as

    1 [DTS.Pipeline] Error: The ProcessInput method on component "Destination - FORM1_APPLICANT_LICENSES" (43) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

    2.[Destination - test [43]] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "Violation of PRIMARY KEY constraint 'PK_test'. Cannot insert duplicate key in object 'dbo.test'.".

    please if u have some solution just reply me

    im at my office and waiting for some miracle to happen

    thanx

  • with my minimum knowledge I can suggest you to use import and export wizard if you have more than 100 tables to transfer..or else if u wanted to tranfer 1 table at a time u can use simple dataflow task...the above errors look like u ve primary keys on the tables...drop the primary key constraint on the tables and try again...

  • im actually using the import export wizard to do the transformation

    regarding the keys can u tell me from where i should remove the constraints

    source or destination

    thanx

  • on the destination tables in sql server...

  • just let me check and ill get back to you

  • well i dont think that this is the solution to this problem

    bcoz what i just did is took the smallest table in the database with two columns in it and one of them was the primary key based column.

    i used the import and export wizard on it and it worked

    but on certain tables it gives me those weird errors

    so what do u suggest

  • yep...the table which u r trying to export is having some duplicate records...in columns with the primary key constraint, they can not insert the duplicate records...so u need to drop either the primary constraints or u need to remove the duplicates...

  • but in some of the tables the data is not duplicating and i m not sure what seems to be causing the problem with it.

  • hey maddy

    i got to the root of the problem. i think the entries where creating the whole mess i was getting ino. But then i emptied the whole table and then did the whole process again and its done

    tnanx again for helping and hope to get reply from u soon

  • thats kool...so u r done....

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply