Data Source Issues using DB2

Has anyone used SSIS with DB2 as a data source. I am able to make it work with the OLE/db driver for DB2, but it is verrrrry slow and fails if it is not an extreamly small table (probably < 1000 rows). It runs much faster and seems to handle large tables if I do it with a script component using an ODBC DataReader. We would like to not have to write code for every table and I can't get a pre-defined data source to work with ODBC.

The OLE driver also seems to have trouble running queries. What I mean by that is that it only works if I use the Table or View option on the OLE data source, it hiccups if I try to use the SQL Command option.

Sorry if I'm overloading the forum, but we have a number of questions.One thing you can try is to create an ADO.NET:ODBC connection in SSIS and use DataReaderSource adapter to pull out data, see whether that works.

We got reports of similar issues when customers use IBM OLEDB provider and the root causes seemed to fall into the provider's plate, not actually SSIS problems.
If instead you are using MS OLEDB Provider for DB2 and facing issues, please provide more detailed information about, for example the failure info, how "slow" when processing large amount of rows etc, and we will be able to better assist you.

Thanks
Wenyang|||Yes, I figured out the ado.net/datareader solution this morning and it works pretty well. We are using IBM's OLEDB provider. I will try and get the MS OLEDB for DB2 to see how that does.

Thanks for the help!
Harrysql

Data Migration :- SQL Server1 - SQL Server2

Hi,
I have 2 similar sql server databases DB1 and DB2 with around 450 tables and much data. My problem is I need to copy specific records from all tables in DB1 to corresponding tables in DB2. What I have done right now is, running seperate INSERT scripts for each table like

INSERT INTO DB2..table1 SELECT * from DB1..table1 where code='XX'

I would like to know whether this is the right approach or any other better way or tool available to do so. Also since the no of records are very high, I insert it in blocks say 30,000 records each, so that log file limit will not create problem.

Thanks in advance. Please help

I would use integration Service for a job like this. I nice easy way to start this is to rightclick the database -> tasks ->export data and follow the wizard.

The outcome from this will be an integration service packagde that following can be edited in SQL Server Business Intelligence Development Studio as a normal Integration service object.

Take a look into the toturials in there. They helped me a lot.

see also this http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=631236&SiteID=1

|||

other alternatives could be

define views and bcp the data out from DB1 and in to DB2

copy the database files (they should not be accessible) and attach them as DB2

Integration Services and use the Database Copy Task

Regards

Norbert

|||

you can use snapshot replication with row filter