Help get this topic noticed by sharing it on Twitter, Facebook, or email.

DBAmp with SSIS with parms

Hi Bill,
I'm trying to use DBAmp with SSIS (2008R2) and hitting some issues. We have successfully used it with linked servers and the TSQL approach of EXEC 'soql statement' AT linked_server and while I could possibly do that with SSIS, I was really hoping to use it as a direct OLE DB connection.
The issue I'm seeing is that I need to pass a parameter (datetime) to limit the rows I'm retrieving. I've tried simply using an SSIS variable (typed as either string or datetime) and using the standard oledb parameter definition of ? within the query string. This definitely pushes the variable value to the query but puts quotes " ' " (i.e. single) around the value, which of course is not acceptable to SOQL.
I've also tried using an Expression, to put the entire SOQL query together as a single string, but this also fails.

Any hints on how to make this work, specifically with parameter passing to the SOQL?

Thanks,

Steve
1 person has
this question
+1
Reply
  • The issue is that internally SSIS and SQL linked servers interact differently with DBAmp in regard to parameters.

    If I might ask, why do you want to connect directly to the OLE DB provider? Our tests show that there is no performance advantage. Also, by connecting directly to DBAmp, you must have OLE DB connection managers each with a set of salesforce credentials. Multiply this by 20 SSIS packages and then imagine a sf password change.

    If you want to push forward in spite of this, then send me the SOQL statement you are working with so I can see what we can do. Send to support at forceAmp.com
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated

  • Hi Bill,

    I'll look at using linked servers as well.

    The main reason for wanting to use OLEDB is we can follow standard SSIS development patterns - ie create a connection, use that connection in a datasource. Unless I'm missing something, this will likely mean we don't have connections (at least not for sources) and we'll also need to have linked servers set up on every developer system.

    My assumption is that for linked servers & SSIS, I'll probably use TSQL tasks in the control flow, execute the statement and return the data to an object (recordset) variable and then attempt to use that within a subsequent data flow. Is there an different way of doing this that I'm not thinking of?

    Thanks again,

    Steve
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated

  • The TSQL usually done has a data flow source with a SQL statement that is using the linked server. Architecturally, this makes the SQL Server the single data tier . Within this data tier you have access to both local and salesforce data (via the linked server). You also have a single connection manager to the SQL Server. And the salesforce credentials are stored encrypted in one place: the linked server definition.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated

  • I’m happy
    Thanks Bill.

    In the end, I used a TSQL task to load a temp (##tbl) table and then used that in the data flow. I went this way because I need to achieve the following:
    - use a parameter (datetime) in my SOQL statement
    - use EXEC() AT [linked_Server] approach due to speed of results
    e.g. EXEC('SOQL statement WHERE clause = ?', @parm) AT linked_server

    If there is another way to do this other than using a execute SQL task and a target table (temporary or otherwise), I'd definitely like to know!
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated