Share via

SSIS Execute SQL Task error: Syntax error or access violation

Shivendoo Kumar 736 Reputation points
2020-09-24T07:02:42.477+00:00

Hi All,

I am running below code in SSIS Execute SQL Task and Passing an input parameter to code. My connection is OLEDB connection so I am using ? but it is failing with error message: "failed with the following error: "Syntax error or access violation". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."

DECLARE @TableDatePartName TABLE
(DatePartName VARCHAR(20))

INSERT INTO @TableDatePartName (DatePartName)
VALUES
(?)

SELECT * FROM @TableDatePartName

27961-capture42.png

I learnt from this thread that it can be problem with SQL Server SP. It is failing when I run this pointing to SQL server 2012 "Microsoft SQL Server 2014 (SP3) (KB4022619) - 12.0.6024.0 (X64) Sep 7 2018 01:37:51 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: )" but same code works when I run against SQL Server 2016.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/8ea3ab6a-d284-4c73-ac5d-d3cdd92f0d0b/execute-sql-task-executing-the-query-quotexec-quot-failed-with-the-following-error?forum=sqlintegrationservices

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,475 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,566 questions

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Shivendoo Kumar 736 Reputation points
    2020-09-24T07:13:07.137+00:00

    I changed this from Data Source=Server;Initial Catalog=AX;Provider=SQLOLEDB.1;Integrated Security=SSPI;Auto Translate=False;

    to Data Source=Server;Initial Catalog=AX;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;
    and now it is working

    3 people found this answer helpful.
  2. Olaf Helper 41,886 Reputation points
    2020-09-24T08:00:03.21+00:00

    Depending on the data provider the parameter number (name) in mapping is zero-base or one based, see Execute SQL Task => Parameter names and markers

    0 comments
  3. Monalv-MSFT 5,896 Reputation points
    2020-09-24T08:40:10.073+00:00

    Hi @Shivendoo Kumar ,

    Hope the following link will be helpful:

    Microsoft OLE DB Driver for SQL Server

    Best Regards,
    Mona

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments