SSIS- execute sql task - Excel connection - query error

• nic2885

  SSC-Addicted

  Points: 479

  More actions

  January 17, 2018 at 12:46 pm

  #402843

  Hello,

  I am using Execute SQL task in SSIS, I am passing variable as input parameter and executing script. I am getting below error -
  [Execute SQL Task] Error: Executing the query "Declare @DBProfile varchar SET @DBProfile = ?..." failed with the following error: "Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
  Can someone please help?

  Query I am executing - 

  Declare @DBProfile varchar

  SET @DBProfile = ?

  If(@DBProfile = 'All')

  select
       DatabasesToRefresh
      ,RemoteBackupPath
      ,CopyFlag
      ,CopyLocation
      ,ODSBackupLocation
      ,DatabaseProfile
  from [Configuration$];

  Else

  select
       DatabasesToRefresh
      ,RemoteBackupPath
      ,CopyFlag
      ,CopyLocation
      ,ODSBackupLocation
  from [Configuration$]
  Where DatabaseProfile = @DBProfile;

• Phil Parkin

  SSC Guru

  Points: 247158

  More actions

  January 17, 2018 at 12:59 pm

  #1976190

  You cannot run anything more than very simple SQL against an Excel connection. The parameter stuff is probably causing the problem.

  ---

• Phil Parkin

  SSC Guru

  Points: 247158

  More actions

  January 17, 2018 at 1:13 pm

  #1976192

  So I suggest building your SQL dynamically via a package variable with an Expression and using that as the source for the ExecSQL task.

  ---