SSIS Feature pack for Azure released

downloadFinally after months testing and fiddling with new features, the SSIS team have released a new Feature Pack for SSIS 2012 and 2014.  One of the most interesting features included here, is the ability to instantiate and delete objects in Azure, this means that you now can include Azure in your daily on-prem ETL-FLOW – How neat is that? Continue reading →

Salesforce and integration with SQL Server – part 2

SFSQL

As described int the previous post, I’ve been working on a project which integrates data from Salesforce (SF) to Navision (NAV) manipulate and create new data, and then we needed to push it back to SF again.

To get all this to work together , there is a need for a local database in your SQL server, and in this case we call it LOCALSF – this is where alle the storedprocedures from DBAmp resides. But it is also the place for the local tables which is used to store data from the SF cloud storage.

The previous post showed you how to generate the tables to hold SF data, but DBAmp is also capable of Insert, Update and Delete in SF cloud storage. To mange this there is a need for either a Insert, Update or Delete table – these can be generated almost automatically by calling the stored procedure SF_Generate

To do this we have to issue a statement that will grab the table structure from SF containing all the attributes of which we can insert, update or delete values, the procedure wil then render a table in the local database.

The statement if we would like to create an insert table for account is this

EXEC SF_Generate @operation = 'insert', 
                 @table_server = 'SFCLOUD', 
                 @load_tablename = 'Account_insert'

This creates a table with an ID, ERROR attributes as well as alle the attributes needed to insert a new Account in SF, you then need to populate the table with values, ID and ERROR must be left empty as DBAmp uses these to keep hold of which record is deployed and which are not.

After the population of the account_Insert table, we need to puch the data into SF, this is done by calling the following statement.

EXEC SF_BulkOps @operation = 'Insert',
                @table_server = 'SFCLOUD', 
                @table_name = 'Account_Insert'

What DBAmp then does is, that it will grab all rows from the insert table and insert them into the account object in SF, afterwards it writes a status in the ERROR attribute on the insert table, so it’s possible to see if any given record is succesfully transfered.

If you instead want to generate a table for Deletes you have to issue the following statement.

EXEC SF_Generate @operation = 'delete', 
                 @table_server = 'SFCLOUD', 
                 @load_tablename = 'Account_delete'

This statement will generate a table called ‘Account_delete’ which will contain an ID, and ERROR attribute and that’s it.

Then you could populate the IDs with SF ID’s that needed deletion from SF, then as before you would need to issue a statement for deleting these records.

EXEC SF_BulkOps @operation = 'Delete', 
                @table_server = 'SFCLOUD', 
                @table_name = 'Account_Delete'

Again DBAmp will connect to SF in the cloud and delete the approriate records and save a status in the ERROR attribute.

In the next posts, i’ll be getting into the stored procedure SF_BulkOps that we shortly looked at in this post.