SQL Server / SSIS Data Import Question

[Spacecadet]

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

[ChrisPackit]

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

[Spacecadet]

Thanks for the informative response.

Don't mention it

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.

[ChrisPackit]

Thanks for the informative response.

[Spacecadet]

ever thought about employing a proper SQL Server resource?
alternatively, it might be time for you to open your wallet and buy some books/training

[ChrisPackit]

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