January 17, 2018 at 12:46 pm
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;
January 17, 2018 at 12:59 pm
You cannot run anything more than very simple SQL against an Excel connection. The parameter stuff is probably causing the problem.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 17, 2018 at 1:13 pm
So I suggest building your SQL dynamically via a package variable with an Expression and using that as the source for the ExecSQL task.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply