Link to home
Start Free TrialLog in
Avatar of KSUF
KSUFFlag for United States of America

asked on

How to copy tables from IBM DB2 to MS SQL 2005

I'm trying to design an SSIS package to copy tables from an IBM DB2 database to SQL 2005 database.
I'd like to be able to select the tables I want copied and not have to write scirpts to create and insert.
I'm connecting to DB2 using IBM OLE DB provider for DB2.
I am trying to use the SSIS Import Wizard.
If I try to select tables or views to copy, I get this error:
'CLI0002W Data Truncated, SQLSTATE=01004 (IBM OLE DB Provider for DB2 Servers)'
If I try to use a query...select * from table...I get the following error:
'Exception from HRESULT: 0xc0202009 (Microsoft.SqlServer.DTSPipelineWrap)'
Can anyone help me correctly import this data?
Avatar of chapmandew
chapmandew
Flag of United States of America image

Use SSIS....select DB2 as the source, and the SQL Server as the destination.
ASKER CERTIFIED SOLUTION
Avatar of PedroCGD
PedroCGD
Flag of Portugal image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
...same answer I gave.
Dear Chapmandew,
For me this is not a competition, ok?
Some details must be done, like the connection source that must me ADO.NET and the source must me a DATA READER SOURCE
Regards,
Pedro
ADO.NET does not have to be the source...OLEDB should be the source and OLEDB or SQL Server could be the destination....
Dear chapmandew,
I'm already linked to DB2 and other databases like Sybase.
I needed to configure the connection manager using ADO.NET and the source only using Data Source Reader and the destination could be SQL destination or OLEDB Destination.
If you can get data with OLEDB Source to a DB2 Database, congratulations! And if you can, show it.
Thanks for you feedback.
Regards,
Pedro
Avatar of KSUF

ASKER

Thanks Pedro, I've got the ADO.NET datasource created, but I'm not sure where to write the query?
It may be because I have a feature pack installed for SQL 2005.....you have to have dev edition or enterprise edition.....


http://www.microsoft.com/downloads/details.aspx?FamilyID=50B97994-8453-4998-8226-FA42EC403D17&displaylang=en
Avatar of KSUF

ASKER

I think I found a place where I can enter the query...I'm still working toward the end of your solution. I'll let you know when I get it completed.
:-)
If I could help, please let me know!
Cheers!
Avatar of KSUF

ASKER

Thanks Pedro, I'm on step 4...I guess I'm not sure how to link the datareader to the destination?
Step for:
Drag and drop from DataSource Reader into OLEDB Destination

Chapmandew, gave us a link where you can download OLEDB drivers for DB2. Unfortunately I dont have for Sybase... but if you dont have problems to install it on your server, do that. But for now try to complete step 4...:-)
Cheers!
Avatar of KSUF

ASKER

Thanks again Pedro. I've completed step 4. I now have a datareader source for the DB2, I've added a data conversion step after that, then linked it to the OLE DB destination for the SQL 2005. The OLE DB destination shows an error 'can not convert between unicode and non-unicode string data types'. What do you think about this?
Maybe you have a nvarchar and varchar in your data source or destination... You need to change one of them. I suggest you to change nchar or nvarchar to char or nvarchar respectivately.
helped?
to change to varchar, in the data conversion set the new column to STRING

Avatar of KSUF

ASKER

I've set all the character fields to data type string[DT_STRING], but I still get the same error...
make sure that in the mappings destination of your OLEDB destination you are linking the correct columns. In the mappings go with mouse to each column source and destination and check in the data type is the same. Some column in source is different from destination...:-(
Avatar of KSUF

ASKER

I recreated my data source and destination...they are in sync now and I have gotten past that error. Now my error deals with data conversion. I can't get past the dates in the DB2 database, many of which are 0001-01-01. If I transform to a string, I get an overflow, if I transform to a date I get an invalid cast...can you help with this?
0001-01-01? Means what?
01-Jan-2001?
Avatar of KSUF

ASKER

That is the default value for dates in the DB2 database. Year 0001. In the SQL 2000 DTS packages, we transformed the dates to string, then we could update these dates if needed.
Where you tried to convert the default date 0001-01-01? Inside the data conversion or in the query you made to the source?!
Avatar of KSUF

ASKER

Inside the data conversion. The source has a datetime data type, I try to convert to string, then copy to my destination.
Dear Friend,
Click on the row that link data conversion to OLEDB Destination and and a data viewer to see the rows in the pipeline.
The date column is wrong? What the conversion to string gives to data viewer?
Cheers!!
any improvements?!
Avatar of KSUF

ASKER

Actually, the data looks good in the dataviewer, but I don't get all the rows back...still have some unexpected data somewhere. I'll have to put this aside for a few days, other work tasks are piling up. Thanks for your expertise. You have been very helpful. Maybe we'll talk again in a few days.