• Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!

SQL Server / SSIS Data Import Question

ChrisPackit

Fingers like lightning
Joined
Nov 9, 2010
Messages
673
Likes received
0
Thanks received
0
Location
North West
Something I've never had to do before, but need to get some data from a DB2 database into SQL Server 2008 and wondering which is the preferred way to do it. I'm sure there are many ways to do this so I was thinking of;

a) Using OpenRowset without using a Linked Server, via SQL query
b) Creating a linked server via sp_addlinkedserver on the SQL Server
c) Creating a connection via a SSIS package and using a Data Flow task to move data.

Which is the best method, or should I be looking at another method?

I'm concerned about testing this on the Test environment, and then not working on Prod so which woule be the most portable and least likely to fail?

TIA
 
ever thought about employing a proper SQL Server resource? :eyes
alternatively, it might be time for you to open your wallet and buy some books/training
 
Thanks for the informative response.

Don't mention it :D

To answer your question - it depends.

Done properly they are all equally portable.
I would normally recommend SSIS, it gives the greatest amount of flexibility and can handle row level errors as part of the dataflow. However if you have no experience with SSIS then I suggest you avoid it. Also avoid if no one at the client co uses it.

Using open rowset with out the linked server means the entire script can be lifted from test to live and reran.
 
I don't mind admitting I'm a little out of my depth with this, but it's such a small part of a larger project that it wouldn't warrant bringing someone in for a day, when I could probably figure it out in a few days.

Thanks for the advice.

I'm using SSIS at the moment, but finding it very slow to append 1.2M records. Had to kill it after 10 mins after moving only 60K records so need to investigate further.

The drivers for DB2 aren't installed on the server to use the OpenRowset without a Linked Server, and just gives me the message to use a Linked Server instead! Would've preferred this method myself.

Any performance tips you could offer in SSIS why it may take so long from DB2 into SQL?

TIA
 
Any performance tips you could offer in SSIS why it may take so long from DB2 into SQL?

TIA

Generally :
Use the OleDB destination
make sure that the "Table or view - fast load" option is selected - do not do any error handling on the destination.
as with any bulk load operation, the target table needs to be empty or have the primary keys,indexes and foreign keys disabled or removed.

If you want to check data types, do it on the source component - right click and select advanced edit, then set the data types in the output. You'll then need to set the task to redirect rows on error or truncation and then pipe these to a different table

What method are you using to connect to the DB2 database? if it's still slow then start a new package, create your DB2 connection again and then take the output to a union all transformation and run. Check to see if the data is being slowed down coming out of the DB2 system
 

Advertisers

Back
Top