SSIS Execute SQL Task error: Syntax error or access violation

Shivendoo Kumar 736 Reputation points

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)

SELECT * FROM @TableDatePartName


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.

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

3 answers

Sort by: Most helpful
  1. Shivendoo Kumar 736 Reputation points

    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 38,861 Reputation points

    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 No comments

  3. Monalv-MSFT 5,891 Reputation points

    Hi @Shivendoo Kumar ,

    Hope the following link will be helpful:

    Microsoft OLE DB Driver for SQL Server

    Best Regards,

    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 No comments