Forum Discussion

fmdf's avatar
fmdf
New Contributor III
4 years ago

Salesforce data types not coming over when loading SQL Server

I am grabbing an object in Salesforce using the Salesforce Read Snap.

I am using SQL Bulk Load and it is flagged to create table if it doesn’t exist.

My numeric types are not being created in SQL. Almost everything is coming over as varchar(8000).

I have read the documentation on this snap many times and it is not well written to explain how do I get the data types to come across and create the tables correctly.

Anyone have issues with this? Do I use XML/Json? Bulk API makes sense to use because I am loading a data warehouse.

I’m stuck.

salesforce

5 Replies

  • koryknick's avatar
    koryknick
    Employee
    4 years ago

    @fmdf - Take a look at the “Match data type” property of the Salesforce Read snap. I believe this will accomplish what you want.

    • fmdf's avatar
      fmdf
      New Contributor III
      4 years ago

      I have turned this on and off several times. The documentation is confusing. That property only seems to apply in certain cases but the UI isn’t disabling it when it doesn’t apply.

  • koryknick's avatar
    koryknick
    Employee
    4 years ago

    @fmdf - I’m sorry you find the documentation confusing. You mentioned you were using the Bulk API which is why I mentioned the “Match datatype” option, which should preserve the numeric, boolean, etc. datatypes of the scalar elements (i.e. matching what is the returned when using the REST API mode).

    When using the “Create table if not exists” option, keep in mind that it creates the table based on the first input document. When pulling from a source like Salesforce, there are many optional values that may not be present in all incoming data. I would recommend that you determine which elements from the source object you want to load to the target and manually create the target table prior to running your pipeline to load it.

    • fmdf's avatar
      fmdf
      New Contributor III
      4 years ago

      I am extracting data for the purposes of loading a data warehouse using the bulk api as stated in my original post.

      The match data type documentation is not clear. It only applies in certain circumstances. I have checked and unchecked and run the pipeline through a dozen times and it is not behaving consistently.

      If I have to manually create the tables to get the data types to be correct, it eliminates the usefulness of the create table if not present checkbox.

      I do not have a way to generate a create table script from Salesforce.

      I can’t be the only person that has experienced this issue.

  • koryknick's avatar
    koryknick
    Employee
    4 years ago

    @fmdf - the “Create table if not present” option is s convenience feature for developers to get started quickly. I would not recommend using this feature for a production pipeline.

    You are correct that the “Match data type” feature only applies under specific criteria. I will mention to our documentation team that they should try to improve the clarity and ask the dev team if it would be possible to disable or hide the option when other settings make it irrelevant.