Apex Code Development You need to sign in to do that
Don't have an account?
TJGAG DBAmp and SQL Server 2005 Integration Services - any experiences
Hi all, Has anyone tried using DBAmp with SQL Server 2005 Integration Services? It seems to be fine as a source but I can't get it to work as an OLE DB destination. I can call up the list of tables and select one but when I try to access the "Mappings" dialog I get this error:
Error at Package [Connection manager SALESFORCE.user.name@org.com]:
An OLE DB error has occurred. Error code: 0x80040E22. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040E22 Description: "Non-NULL controlling IUnknown was specified, and either the requested interface was not IUnknown, or the provider does not support COM aggregation.".
Error at data flow [OLE DB Destination [490]]: The AcquireConnection method call to the connection manager "SALESFORCE.user.name@org.com" failed with error code 0xC0202009.
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)
I think I'll look to get round this by encapsulating the updates/creates in stored procedures but I wondered if anyone else had come across the issue.
Thanks,
Andrew
We have customers that are using DBAmp with SSIS. There are some performance issues with DTS/SSIS that you have to watch out for (for example: the sf_replicate procedure can replicate a table much faster than DTS/SSIS).
Can you tell me exactly what it is you are trying to do and maybe I can help?
Bill Emerson
Author, DBAmp
Thanks for the prompt response.
I'm developing a kind of hub between SalesForce and an internal finance system. This is needed as we plan to replace an existing system with SalesForce functions. The restriction is that the current interfaces to the finance system cannot be changed. These are a mixture of FTP feeds and direct access to some work tables.
I can see three kinds of access to SalesForce that are needed. One, to extract data for transmission to the finance system: this is straightforward; two, to create new custom objects in SalesForce from data taken from the finance system; three, to update standard and custom objects with data from the finance system.
I was hoping to be able to create objects by using an OLE destination as this should, at least conceptually, be simple.
Current thinking is that I may write create/updates to intermediate tables then used stored procs to apply the changes to SalesForce.
Regards,
Andrew
'One, to extract data for transmission to the finance system: this is straightforward;'
For this piece, use the free version of DBAmp and the DBAmp sf_replicate stored proc. It's a very fasy replicator that uses the SQL Bulk load feature to rapidly load SQL tables.
'two, to create new custom objects in SalesForce from data taken from the finance system; three, to update standard and custom objects with data from the finance system.'
Alot of DBAmp users do something similiar to this. Here's the thing you have to watch out for: DTS/SSIS submits commands to the OLE DB provider as single commands. This causes scalability problems because it prevents DBAmp from batching the inserts/updates to get performance.
With DBAmp/Pro, you can use the DBAmp/Pro sf_bulkops stored proc. This proc takes SQL tables and either inserts/updates/deletes (in batch) the rows to Salesforce.com. So, your code builds the local SQL 'load' tables, and you schdeule a sf_bulkops proc to insert/update that table to Salesforce.
For more info, download the latest version of DBAmp (www.forceamp.com) and see the doc chapter on 'Bulk Operations'.
If you still want to pursue the SSIS option, let me know if you run into anything and need help.
Bill
Thanks for the update. I'll try out the replication option for extracting.
One question on bulk insert: does or will DBAmp support the "upsert" command recently released?
Andrew
Hopefully soon (right, Foghorn ?)
Bill
SalesForce Source Connector for SSIS
Try this link http://www.mdisconnector.com