July 8, 2008 at 3:46 pm
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
July 9, 2008 at 9:12 am
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...
July 9, 2008 at 10:46 am
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
July 9, 2008 at 10:59 am
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...
July 9, 2008 at 11:04 am
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
July 9, 2008 at 11:07 am
on the destination tables in sql server...
July 9, 2008 at 11:27 am
just let me check and ill get back to you
July 9, 2008 at 11:39 am
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
July 9, 2008 at 11:44 am
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...
July 9, 2008 at 11:57 am
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.
July 9, 2008 at 12:31 pm
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
July 9, 2008 at 12:35 pm
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