Unable to UPDATE or DELETE using SSIS and OLEDB connection
-
Greetings. I have been trying to develop an SSIS package that updates external data (Visual FoxPro tables) from SQL Server 2005. I have tried this various ways: using various Data Flow task components that flow to an OLEB Destination; using an Execute T-SQL Task; and even trying Management Studio interactively with the OpenDataSource('vfpoledb', etc.) statement. For each of these techniques, I have no problem performing a SELECT from the VFP data. Also, I have no problems performing an INSERT of new records using any of these techniques. However, both UPDATE and DELETE of existing records fail.
Is it possible the the OLE DB driver doesn't support UPDATE and DELETE operations? It appears that I'm not allowed to change or delete existing records, only add new ones. Or, are there other techniques I can be trying?
Thanks much,
Randy Witt
-
I know certain OLE DB sources require different syntax...
http://www.proteanit.com/b/2008/03/18/ole-db-command-for-jet-40-in-ssis/%5B/url%5D
I know this works in access and paradox. Perhaps it will work for you too.
Kindest Regards,
Frank Bazan
-
Does your user have UPDATE and DELETE rights in the destination?
-
The only other question I have, are there primary keys defined on the tables as well?
-
In this situation, I am using the SQL Server Developer edition installed on my own PC, which uses my own standard login/username and Windows authentication as the account under which it's running. Looking under "Services" in the Administrative Tools option of Windows, both the SQL Server Agent & SQL Server (MSSQLSERVER) services are logged on as myself. That's the same environment in which I run other applications, such as Visual FoxPro itself, from which I can edit/delete etc. any Visual FoxPro data in the paths I'm referencing. So, I'm assuming that I have full access from within the SQL Server Developer edition on my PC, to access these files similarly.
-
try the script component
-
Hi,
I came across this post of yours and it would be great if you can help me. You have mentioned that you were able to INSERT using a OLE DB command. Can you let me know the syntax you used for INSERT ? I am using the parameterized INSERT syntax below, but I get a syntax error.
insert
into employee ( employee_no, employee_name, employee_salary)
select ?, ?, ?
from Employee_Salary
Thanks,
Rahul.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply