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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply