March 3, 2011 at 10:32 am
I have an Oracle linked server created in one of my instances. I'm trying to create an SSIS job to load data from a flat file to that table.
I cannot get it to work because the table does not exist in a database. Is there any way to do this without creating an extra table? Is this possible?
March 3, 2011 at 12:14 pm
Can you try the following:
the OLE DB Destination has the property OpenRowSet, which specifies which table the data needs to be written to.
Manually set this to the destination table you want, using the four-part notation:
myServer.myDatabase.mySchema.myTable
Then open the OLE DB Destination editor. Does that work?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 3, 2011 at 12:38 pm
Thanks for the reply.
I can't do the four-part notation because the table is not associated with any local database.
It's like this: MyLinkedServer..OracleSchema.OracleTable
I did try your suggestion anyway with the above, but got 'Invalid use of schema or catalog..' when I clicked on the mapping tab in the editor. If I click on Preview I get:
Index and length must refer to a location within the string.
Parameter name: length (mscorlib)
Any thoughts?
March 3, 2011 at 12:41 pm
Allright, other option, download the OLE DB Oracle adaptor from Microsoft (search MSDN for attunity) and directly connect to Oracle.
Or is that not allowed?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 3, 2011 at 12:54 pm
I think that there are some significant issues with your solution. I have to strongly recommend that you switch from trying to push the data through a linked server to writing to Oracle directly.
You may be able to get this to work by creating a view that is effectively SELECT * from the Oracle server's table.
However I think that performance will be very slow, linked servers are not know for high-speed write performance.
I used to use linked servers all the time but over time I got bit enough times that now i use them sparingly or not at all.
SSIS can access Oracle directly with an OLEDB connection and I think you will find this MUCH faster and easier than trying to do it through a linked server..
CEWII
March 3, 2011 at 1:08 pm
Elliot - Genius! I don't know why I didn't think of that from the get-go. I guess I didn't realize SSIS could make the direct connection. I'm still learning.
It works. Thanks so much!!!
March 3, 2011 at 1:16 pm
You are very welcome. SSIS is a very good tool but its learning curve is pretty steep.
Good luck.
CEWII
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply