+ Start a Discussion
AmulAmul 

Dataload CLI for SQL server

Hello,

Can you please someone share how to integrate Salesforce with SQL server using dataloader CLI (use ODBC connection) .

Please share folder and file structure for the same.

I am able to integrate with csv files and salesforce.
But I wish to integrate using sql server.
 
NagendraNagendra (Salesforce Developers) 
Hi Amul,

Here are the steps to make your database-conf.xml DBDataSource bean work with SQL Server database and basically any database (even Access, although you will run into problems with the memo fields). This will explain how to set it up for SQL Server, there are very few differences with other databases though and you can figure the rest out relatively easily.

Create an ODBC Data Source

1)Go to Control Panel -> Administrative Tools -> Data Sources (ODBC) ->        System DSN tab.
2)Click the 'Add' button.
3)Double-Click the driver with the name 'SQL Native Client'.
4)Enter any name and description you want, in this case I'll call                        it 'SQLdsn' and give it the same description.
5)Choose the SQL Server you want to connect to,                                              then Click the 'Next' button.
6)Choose the bullet with the description 'With SQL Server                                  authentication....etc', Check the checkbox, and Enter your login                      information to the server.
7)Click the 'Next' button.
8)Click the 'Next' button again.
9)Click the 'Finish' button. (you can come back to all of this and change it)
10)Click the 'Test Data Source...' button, and it should (dont quote me) give you a message that ends with 'TESTS COMPLETED SUCCESSFULLY!'

Now that we have the Data Source set, you can set up your bean.

<bean id="dbDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
    <property name="driverClassName" value="sun.jdbc.odbc.JdbcOdbcDriver"/>
    <property name="url" value="jdbc:odbc:SQLdsn"/>
    <property name="username" value="MyUserName" />
    <property name="password" value="MyPassword" />
</bean>

Notice how 'SQLdsn' looks familiar? That's where you call for your specific data source. So if on step 4 you named it 'SQLSRVRdsn' your bean would look more like this.

<bean id="dbDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
    <property name="driverClassName" value="sun.jdbc.odbc.JdbcOdbcDriver"/>
    <property name="url" value="jdbc:odbc:SQLSRVRdsn"/>
    <property name="username" value="MyUserName" />
    <property name="password" value="MyPassword" />
</bean>

It is possible to connect directly to the server without going through ODBC Data Sources, but  this eliminates the need to download SQL Server JDBC Driver or make sure you're connecting to the server/database by running it in Command Prompt...If you can make the connection with ODBC you will have no problem with what I supplied for a connection.

Also, this way you can create an ODBC data source for MS Access for example, and then all you would have to change is what I have in bold on the bean. Which means pointing to an entirely new database is only a few keystrokes away. Granted, you will have truncation issues with MS Access if you have any fields which are over 255 characters, even with a memo field, it's not nice... It'd be best to just have a linked table.

[EDIT]
Here are a few errors you may recieve and how to handle them.
String data, right truncated (null)
One or more of the fields you are trying to write to is not long enough to hold all of the data you're entering. Increase the length/size of the field in the database.

Null Pointer Exception
This happens when you have a SQL statement, for example an insert statement where one of the values you're entering is null (blank).
To fix this, add the entry key of the field to the sqlParams property.

Datatype Mismatch
You're trying to write from a string to a numeric field type or vice versa. Could also be a date, but either way, you need to change the entry key of that field to a value that corresponds with the field type. Ex. "Java.lang.Boolean" for a boolean field or yes/no field for those Access Db people out there...
I really hope this helps someone.

Here are a few links to some helpful information dealing with this.
http://weka.wikispaces.com/Databases <- How you would set up your driver and URL normally
http://docs.database.com/dbcom/en-us/dbcom_help_working_with_data_loader_from_command_line.htm <- Almost everything you need to get started with this
http://docs.oracle.com/cd/B14099_19/web.1012/b15901/dataaccs002.htm <- Info on how to change your field mapping

Please mark my solution as the best answer if it helps you.

Best Regards,
Nagendra.P