none
Retrieving data from Salesforce, but DT_WSTR error in SSIS RRS feed

  • Question

  • I am trying to retrieve data from salesforce via SSIS per a specific date value which keeps changing as only new data is to be loaded. So i have a variable in the Execute SQL task as AccountLastModdate = select max(lastmodifieddate) from Account. Within the Salesforce Source object, i have the statement/expression as "select accountnumber, accountsource from Account where lastmodifieddate >"+ @[User::AccountLastModDate] but i keep getting the below error. 


    The AccountlastModdate is fed by this query SELECT CONVERT(NVARCHAR,max(lastmodifieddate),126) from account as SF needs the date to be in a specific format. From reading in forums, issue seems to be datatype related. I see that the variable is of datetime , but it is in the format as per Salesforce. Looks like i've two issue, (1) is with the datetime conversion and (2) is the syntax for the expression. Please advise on how to resolve this. 

    thank you.

    Monday, June 29, 2020 3:11 AM

All replies

  • Hi doineedanid,

    The query SELECT CONVERT(NVARCHAR,max(lastmodifieddate),126) as AccountLastModdate from Account in the Execute SQL Task is fine.

    In the next Task would like:

    "select accountnumber, accountsource 
    from Account 
    where lastmodifieddate > ' "+ @[User::AccountLastModDate] +" ' "

    refer to: SSIS SQL TASK MAX(DATE) to Variable in DATA FLOW.

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com


    • Edited by Lily Lii Monday, June 29, 2020 9:52 AM
    Monday, June 29, 2020 9:15 AM
  • Hi Lily, I am still getting the same error using your recommendation. 

    Tuesday, June 30, 2020 2:36 AM
  • Hi doineedanid,

    The two steps could combine into one:

    select accountnumber, accountsource 
    from Account 
    where lastmodifieddate > (select max(lastmodifieddate) from Account)

    However, the table will return nothing in this way, there is no lastmodifieddate lager than max(lastmodifieddate), please feel free to let us know if I understand incorrectly.

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, June 30, 2020 6:57 AM
  • Lily,

    I'm not sure if that would work. So basically , i have the Account table in SQL server that was downloaded from Salesforce. I need to update that table from extracting data from Salesforce on a daily basis. Rather than drop and load/extract the entire table from Salesforce daily , i want to extract only that data from salesforce that isnt in Sql server. 

    So, i create a variable in Execute SQl task as per SELECT CONVERT(NVARCHAR,max(lastmodifieddate),126) which is assigned to a variable and then in the Dataflow task, under Salesforce Source component, i have to write the SOQL to extract only the data that is not in Account table on SQl server, which is this statement "select accountnumber, accountsource from Account where lastmodifieddate >"+ @[User::AccountLastModDate] . 

    And when i evaluate expression using what i wrote above or your suggestion on the format, i get the DT_wstr error. 

    Tuesday, June 30, 2020 1:57 PM
  • Can you simply try using the datetime variable for the user variable (e.g. extract LastModifiedDate directly without any conversion) and then use it also directly as parameter in your other task (e.g. use > ?  in the expression and set parameter) ?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, June 30, 2020 2:06 PM
  • Hi Naomi, I'm sorry as I dont follow your suggestion. Please elaborate. 
    Tuesday, June 30, 2020 7:23 PM
  • For your variable instead of your current query use 

    SELECT max(lastmodifieddate) from account as SF

    So, your variable will be DT_DATE format (you may even cast to it).

    Then in your second query are you able to use

    select accountnumber, accountsource 
    from Account 
    where lastmodifieddate > ?

    and map parameter to your variable?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, June 30, 2020 8:26 PM
  • Hi doineedanid,

    I tried the following way:

    In Execute SQL Task:

    Set the output:

    In Data Flow Task, I use a OLE DB source:

    Map the parameter:

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Proposed as answer by Naomi N Wednesday, July 1, 2020 3:49 AM
    Wednesday, July 1, 2020 2:12 AM