Copy Salesforce to a SQL/Server Financal System.

We wanted to have a copy of our Salesforce price books available on our financial system but did not want to write custom code to do it.  We did the schema creation and copy in 5 minutes using CopyForceSqlServer (4 minutes development + 1 minute execution time).

How simple can it get!


SET SFDC=production
SET SQLSERVER=//localhost;databaseName=MyCompanyTools;username=salesforce;password=miracle;


java -jar CopyForceSqlServer.jar -salesforce %SFDC% -sqlserver "%SQLSERVER%" -include product2,pricebook2,pricebookentry -schema -querybatch 1000

Later we will set this up as a CRON job to keep our financial system up to date automatically.

Beware the Salesforce QueryOptions parameter

When I learned that CopyForce was using up almost 1GB of memory copying some SalesForce instances, I had to understand why.  Three hours later.....the default batch size return from a SalesForce API query is 1000 rows.  Since CopyForce grabs all columns in a table, the amount of memory required can get huge. The solution:

• Set the "QueryOptions" parameter in the binding header.

The next revision of CopyForce will choose values for this parameter that cause memory usage to stay below 200mb (app and data). Naturally, CopyForce will also include a new flag to control the number of rows in a batch at runtime.

It looks like CopyForceOracle will be ready to post on Monday. It is passing of the standard CopyForce unit tests and copied my large test database with flying colors.

Cranking with CopyForceOracle

I had a few hours free last weekend and took a stab a CopyForceOracle (copy all or part of a SalesForce instance to Oracle). The status is pretty good:

• 1.0 code has been checked into SVN
• No problems seen when copying a 4GB Salesforce instance (all tables) to Oracle 10g/XE. The Salesforce instance has ~9000 account, 45000 contacts, 70000 assets, etc.
• Application seems to need about 500mb to run (e.g. using . -Xm500m). This is a mystery I will look into. It is more memory usage than I would expect. I am flushing to Oracle every 2MB from Salesforce.

Volunteers are need to try this out on their own production SalesForce instances.

Time for a workout at the YMCA.

CopyForceOracle or Something else?

I have been thinking about creating a version of CopyForce for Oracle (CopyForceOracle) using one of Oracle's type 4 drivers. Since I do not have a near term need, I am looking for 4-5 sites to help me test it. If a few people step up, I will put a version together. I have a few years of Oracle/JDBC practice and think creating a version that passes the CopyForce unit tests should take no longer than a week (part time).

Volunteers willing to lend a testing hand please drop me a note at gsmthfarmer@gmail.com.

Downloads of the various sqlforce tools are holding steady at 10-20 per week. Feedback is fairly sparse (not surprising since I rarely post comments on a tool that works). If you are using one of the tools, drop me a note --  good or bad feedback is welcome.

I Just Saved a Lot of Time....

A number of Salesforce price updates were entered by hand (100s) and the "UseStandardPrice" flag was set to true. It should have been set to false. The data was fixed in a couple of minutes with SQLForce.

UPDATE PriceBookEntry SET useStandardPrice=false WHERE useStandardPrice=true AND pricebook2.isStandard=false

One line of SQLForce instead an afternoon of Apex batch...I love it.

Lovely Salesforce (for Non-Admin Users)

When is a SOQL SELECT not a SELECT? When it references a table whose selection syntax restrictions depend on who is selecting.  I was running a complete CopyForce on a development database this morning to validate an upgrade to Eclipse/Helios. This time I ran the CopyForce using a non-admin user. I discovered that two tables (NewsFeed and EntitySubscription) will not accept the same SELECT syntax when referenced by a non-admin. Mine is not to wonder why.  Anyway, problem fixed and new distributions have been posted.

The CopyForce feedback I am seeing indicates that most people are taking the default options and trying to copy entire Salesforce instances.  No problem...this will work. For production purposes we usually restrict what is copied because quite a few of the tables (like EntitySubscription) are not important for us to backup anyway.

Here is an interesting use case: We wanted Salesforce pricebooks duplicated in our accounting system. CopyForce can do this on one line:

• java -jar CopyForceSqlServer.jar -salesforce production -schema -include Pricebook2,Product2 -sqlserver "...."

This will copy just Pricebook2 and Product2 into the target SQL Server database. Support we wanted to also copy all of the tables referenced by Pricebook2 and Product2. CopyForce does not have a command line switch for this case -- you have to use a CopyForce configuration file (see the -config switch). More on this in a later post.

A Busy SQLForce week in Flyover Land

There was a lot of SQLForce activity this week. New distros were published that fix all reported problems.

The jython module saved me a quite a bit of work today. Ages ago someone create a free form string column on Account that was supposed to hold the beginning month of an accounts fiscal year. Naturally, people had entered the data in a variety of forms. I wanted to changed the type of a picklist and convert the existing data to consistent month names. How long did this take? About 10 minutes.

1. Find all unique ways that people had expressed the data.
   
   SELECT DISTINCT FiscalYearEnd__c FROM Account WHERE FiscalYearEnd!=null
   
2. Write a tiny bit of Jython that maps all of the "expressed" ways to normal ways.
   
   myMap = { "Jan":["1/1", "January", "janary", "01-01", ], "Feb":[...] }
   
3. Write a tiny bit of Jython that creates SQL statements that look like:
   
   UPDATE Account SET FiscalYearEnd__c="Jan" WHERE FiscalYearEnd__c="1/1"
   
4. Run each SQL command (in the Jython).
   
   session.runCommands( "UPDATE Account ..." )
   

Try doing steps 1 and 3 using base Apex tools. There is no way I could finish the job in 10 minutes.

Time to pick up some pizza. Have a great weekend.