April 4, 2008 at 7:33 am
hi,
Iam importing a table from db2 containg 30 million rows to sql server table.
is there any procedure to import like 10,000 rows for batch because iam trying to import 30 million rows at a time,is this effect the sql server?
could you plz help me
thanks
April 4, 2008 at 8:09 am
It depends on how you are importing the rows. If you use SSIS and an OLDDB destination adaptor to your SQL server, you can set the batch size and commit size to whatever number you want.
April 4, 2008 at 9:02 am
where to set that batch size n commit size?
April 4, 2008 at 9:06 am
If you are using an OLEDB Destination in your data flow, open the editor for the component.
For the data access mode, choose "Table or view - fact load".
The options will appear below the dropdown for selecting the table.
April 4, 2008 at 12:44 pm
actually Iam using a OPENQUERY, which calls the server db2,in this case iam choosing sql command not table or view so i did not the option for batch in this case? how to set batch in this case
April 4, 2008 at 1:16 pm
I did not fully understand what you wrote.
I think you are looking at your source component, not your destination component.
The batch settings I am referring to are on the OLEDB Destination component.
April 7, 2008 at 7:40 am
Hi,
I attached a screen shot here. I jus wanna make sure that These are the options,like first row and last row for giving the batch size when we are importing millions of rows.
Thanx
April 7, 2008 at 7:44 am
Hey USE "Export Import " Wizard in SSIS. Either save it as package or run right there.
Its much easier and faster...
KUMAR
April 7, 2008 at 8:17 am
madhu.arda (4/4/2008)
actually Iam using a OPENQUERY, which calls the server db2,in this case iam choosing sql command not table or view so i did not the option for batch in this case? how to set batch in this case
You could also switch to the OLE DB driver for DB2 (which you can then call directly from SSIS), which may give you slightly better control over your batch sizes.
The driver is available for download from the SQL 2005 "feature pack" on the MS downloads web site.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 7, 2008 at 8:29 am
You are using a SQL destination adaptor, not an OLEDB destination.
I was referring to the OLEDB destination adaptor.
The SQL destination adaptor is designed for high performance batch insert of a lot of rows in a single transaction to a local SQL server.
Switch to the OLEDB destination adaptor to allow for small batches in controllable commit sizes.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply