[b] Compare 2 siff database (DB2 and SQL server)[/b] 1

[sqlmann]

I have to extract data from Db2 to SQL server.

Source table Query
------------------
Select * from <db2-table> where group_id in (
Select group id from Sql server table)
---------------------

How to do this using SSIS

 

[RiverGuy]

Lots of different ways:

1. Create a linked server in SQL Server to DB2 and use a join.
2. Just download all of the data from DB2 to a staging table in SQL and load your final destination with a join query or using a lookup task.
3. Populate a staging table on the DB2 server containing the ID's you need and use that to join with your source query.

It really all depends on the number of records, the number of ID's to search for and what performs the best.

 

[sqlmann]

Thx for quick reply

In the source (DB2) has more than 100,000 records.Every day minimum 1000 new records will be added into db2. I have to extract the recods based on the ids (Id details are available in SQL sever)

and more over to create link serer will not posssible (Due to security policy of my client)

Can you please tell me alternate way to do this?

 

[RiverGuy]

Assuming an average-width table, 100,000 records is nothing to extract once per day when loading a staging table with Fast Load. It will run quicker than you think. The only issue I would see would arise if you're going over a slow network. That might be your best plan -- load all 100,000 into a staging table and then select from there in SQL Server to load your final destination.

The only other option I can think of would be to hack together a dynamic, kludgy SQL Statement. You can set a variable in SSIS to use as the Source SQL Statement for DB2. You can build that variable string with T-SQL or using a script task, basing it off of the ID's in your SQL table. Your final string would look something like:

SELECT * FROM DB2Table WHERE ID = 1
UNION ALL
SELECT * FROM DB2Table WHERE ID = 2
UNION ALL
SELECT * FROM DB2Table WHERE ID = 3
UNION ALL
etc...

It would probably be pretty inefficient for 1000 records however.

 

[MDXer]

if your IDs are sequential and you will only have to ever pull the newest IDs you could build your datasource in a way where you passed in the MAXID from the previous process into a variable and select where > than the variable. Now this is a pain in Oracle because the OLE DB drivers do not support parameterized queries, but there are work arounds that can be used if the OLE DB for DB2 does not support the use of parameters.