+ Start a Discussion
john hejohn he 

Data Loader has error when export the User whose lastlogindate blank

I am trying to export the User from SFDC to Sql Server database, I create the process-conf.xml, database-conf.xml and sdl file.  All the thing looks fine.  But when I run the command line to export the user record, it gives me the error:

--------------------------------------------------------------------------------------------------

2012-08-23 18:40:04,163 INFO [userExport] controller.Controller executeAction (Controller.java:119) - executing operation: extract
2012-08-23 18:40:04,163 INFO [userExport] action.AbstractAction execute (AbstractAction.java:115) - Loading: extract
2012-08-23 18:40:05,033 ERROR [userExport] database.DatabaseWriter writeRowList (DatabaseWriter.java:165) - Database error encountered while preparing row #2
r writing row #1 through row #11. Database configuration: insertUser. Error: Type not supported: java.sql.TimeStamp.
java.lang.UnsupportedOperationException: Type not supported: java.sql.TimeStamp
at com.salesforce.dataloader.dao.database.DatabaseContext.getSqlType(DatabaseContext.java:219)
at com.salesforce.dataloader.dao.database.DatabaseContext.setSqlParamValues(DatabaseContext.java:190)
at com.salesforce.dataloader.dao.database.DatabaseWriter.writeRowList(DatabaseWriter.java:144)

--------------------------------------------------------------------------------------------------

After some research I find the root cause is that the user's LastLoginDate is null.  Data loader may works as below:

1. the dataloader will query the SFDC User object, and retieve the records per the soql in process-conf.xml.

2. the Dataloader Jar package will mapping the data from SDFC to the Sql Server table per the sdl file

3. After Data loader does the mapping, it will do the data type conversion per database-conf.xml and insert the data into SQL Server.  the following statement used for the datatype conversion:

<entry key="LastLoginDate" value="java.sql.TimeStamp"/>

 

If the soql in step 1 query one user record with the LastLoginDate = null, then in Step 3, the jar package can't convert null to java.sql.TimeStamp.  So the error is raised.  that means if the user's LastLoginDate = null, it will break the process.

 

I have test it by filter the user records which LastLoginDate <> null.  all the users can be exported if his LastLoginDate <> null.

 

Did anyone face the similar issue beofre?  How can I export the user with the LastLoginDate == null?  

 

I know there is a workaround, firstly, to export the user's whose LastLoginDate <> null; secondly, export those LastLoginDate == null but exclude the LastLoginDate  from the soql..... But it is hard for me to maintenance.

 

oaklandoakland

I got this same stackTrace ( database.DatabaseWriter writeRowList (DatabaseWriter.java:165) )when my SQL had a missing @.

 

I had this:

<bean id="insertDB2ProfileSql"

class="com.salesforce.dataloader.dao.database.SqlConfig" singleton="true">

<property name="sqlString">

<value>INSERT INTO MYSCHEMA.PROFILE (ID,NAME) VALUES (@ID,@NAME)</value>

</property>

<property name="sqlParams">

<map>

<entry key="ID" value="java.lang.String"/>

<entry key="NAME" value="java.lang.String"/>

</map>

</property>

</bean>

 

Instead of this:

<bean id="insertDB2ProfileSql"

class="com.salesforce.dataloader.dao.database.SqlConfig" singleton="true">

<property name="sqlString">

<value>INSERT INTO MYSCHEMA.PROFILE (ID,NAME) VALUES (@ID@,@NAME@)</value>

</property>

<property name="sqlParams">

<map>

<entry key="ID" value="java.lang.String"/>

<entry key="NAME" value="java.lang.String"/>

</map>

</property>

</bean>

 

S CS C
Environment: data loader v36.0.0, sqljdbc42.jar, MS SQL Server 2012 SP1

Our solution to problem in the first post was:
  1. To define the column on the SQL Server table as a string
  2. In the database-config.xml file, set the value for the field as java.lang.String (which accepts nulls)
  3. Optionally convert that value into another date column.
Not sure if this is the best practice, but it works.
Is there another preferred solution?