I am developing a SSIS package for inserting data in an Oracle database table. but looks like the 'bulk insert task' in SSIS does not support oracle database. I cannot set the destination connection property to use the oracle database connection.
I have created one OLEDB connection pointing to my oracle database. (using 'Oracle Provider for OLEDB' and I tried 'Microsoft OLEDB provider for Oracle' also). this connection is working fine.
When I go to the connection property in the 'Bulk Insert Task's edit dialogue box, in the DropDown list, I don’t see my Oracle connection listed there. (but a SQL server database connection existing in the same package could be seen).
Does anyone have solution for this problem?
Saurabh Kulkarni wrote:
I am developing a SSIS package for inserting data in an Oracle database table. but looks like the 'bulk insert task' in SSIS does not support oracle database. I cannot set the destination connection property to use the oracle database connection.
I have created one OLEDB connection pointing to my oracle database. (using 'Oracle Provider for OLEDB' and I tried 'Microsoft OLEDB provider for Oracle' also). this connection is working fine.
When I go to the connection property in the 'Bulk Insert Task's edit dialogue box, in the DropDown list, I don’t see my Oracle connection listed there. (but a SQL server database connection existing in the same package could be seen).
Does anyone have solution for this problem?
I don't know for sure but I can make an educated guess.
The Bulk Insert task leverages SQL Server's bulk insert functionality. Hence, it is to be used with SQL Server only. The first sentance in the BOL topic about Bulk Insert Task says "The Bulk Insert task provides the quickest way to copy large amounts of data into a SQL Server table or view"
i.e. Not Oracle.
Did you read the documentation?
-Jamie
|||
Jamie is right; here is the link to the BOL that talks about Bulk insert task:
http://msdn2.microsoft.com/en-us/library/ms141239.aspx
To solve your problem you can use OLE DB Destination instead and choose fast load option...
|||Rafael,
As Jamie pointed out, Bulk Insert task leverages BULK INSERT functionality supported SQL Server and hence can be used only with SQL Server. The link you provided in your post does not say that you can insert data into any database. Can you point me to the location in this BOL page that could have confused you?
Thanks.|||
Rafael,
As Jamie pointed out, Bulk Insert task leverages BULK INSERT functionality supported SQL Server and hence can be used only with SQL Server. The link you provided in your post does not say that you can insert data into any database. Can you point me to the location in this BOL page that could have confused you?
Thanks.
I was not confused; I provided the link to support Jamie statement. I have edited my previous post to clarify it.
|||sorry guys I was away from this talk for a while. using OLEDB destination is an alternate method but I'm worried about the data loading performance. I am dealing with a huge amount of data and I feel OLEDB destination might just become a bottleneck in the dataflow.
Thanks for the response. That atleast made it clear that I cannot use the bulk insert task with Oracle.
Regards,
Saurabh
|||OLE DB destination has several moving parts that will allow you to tune it for large amount of rows. First at all; you should use fast load as the data access mode; at least against SQL server that works as bulk inserts. Then you have 'rows per batch' and 'Maximum Insert commit size' parameter that would help to control how often the transactions will commit. I have seen at least 1 thread in this forum discussing how to use those options. Also and if third party providers are an option for you; I think there is at least 1 vendor offering a high performance connector for Oracle DBs (http://www.persistentsys.com/products/ssisoracleconn/ssisoracleconn.htm). I recommend you to run some tests and see by yourself.
|||Loading data into Oracle could be done by using the "execute process task" and then use sqlldr as command. You have to install the oracle client - which contains the sqlldr - on the maschine, where your package will run. Using the process task gives you all the flexibility for dealing with paramters, config-files and so on, which makes sqlldr fast.
I realized a similar scenario for transfering Sybase data into a MS SQL Server by using standard utilities from Sybase to get data out (or in, whatever the direction is), dependent on a configration table (so fully dynamic).
Norbert
|||sorry guys I was away from this talk for a while. using OLEDB destination is an alternate method but I'm worried about the data loading performance. I am dealing with a huge amount of data and I feel OLEDB destination might just become a bottleneck in the dataflow.
Thanks for the response. That atleast made it clear that I cannot use the bulk insert task with Oracle.
Regards,
Saurabh
The OLE DB Destination is perfectly fine for loadif to Oracle as long as you use it the right way. Read these links:
Persistent Oracle Connector
(http://blogs.conchango.com/jamiethomson/archive/2006/04/07/3575.aspx)
Loading to Oracle
(http://blogs.conchango.com/jamiethomson/archive/2005/11/15/SSIS_3A00_-Loading-to-Oracle.aspx)
-Jamie
No comments:
Post a Comment