Error when running SSIS package as a job

Question

Post reply

Error when running SSIS package as a job

ichervin

SSC-Addicted

Points: 409
More actions
April 15, 2025 at 5:54 pm

#4579438

Hello,
I've created an SSIS package that collects data from SQL Servers and databases such as CPU, Wait time, database growth, etc. The package runs without any issues in Visual Studio 2019; however, when I run the same package as a job I get this message:
Keyword not supported: 'provider'.
How do I fix it?
Thanks.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

TangoVictor SSChampion Points: 10976 More actions · Answer 1

What type of SQL connection are you using?

OLEDB looks like this:

Data Source=MyDBServer;Initial Catalog=MyDatabase;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;

ADO looks like this:

Data Source=MyDBServer;Initial Catalog=MyDatabase;Integrated Security=True;

Are you using an ADO source but provided an OLEDB connection string maybe? Do you use environments, did you perhaps configure an ADO connection to use an OLE environment variable?

ichervin SSC-Addicted Points: 409 More actions · Answer 2

I've tried Native OLE DB, Microsoft OLE DB Provider for SQL Server and Microsoft OLE DB Driver for SQL Server and still was getting the same error.

Phil Parkin SSC Guru Points: 247137 More actions · Answer 3

Does it run if you execute it in T-SQL?

DECLARE @execution_id BIGINT;
 
EXEC SSISDB.catalog.create_execution
    @package_name = N'package.dtsx'
,   @execution_id = @execution_id OUTPUT
,   @folder_name = N'folder'
,   @project_name = N'project'
,   @use32bitruntime = False
,   @reference_id = 1
,   @runinscaleout = False;
 
SELECT @execution_id;
 
DECLARE @var0 SMALLINT = 1;
 
EXEC SSISDB.catalog.set_execution_parameter_value
    @execution_id
,   @object_type = 50
,   @parameter_name = N'LOGGING_LEVEL'
,   @parameter_value = @var0;
 
EXEC SSISDB.catalog.start_execution @execution_id;
GO

Phil Parkin SSC Guru Points: 247137 More actions · Answer 4

Take a look in the All Executions report and see whether you get a more detailed error there.

ichervin SSC-Addicted Points: 409 More actions · Answer 5

It returns the value of 176. Not sure what that means

Phil Parkin SSC Guru Points: 247137 More actions · Answer 6

ichervin wrote:

It returns the value of 176. Not sure what that means

The All Executions report is way more verbose than that.

ichervin SSC-Addicted Points: 409 More actions · Answer 7

There are no error messages in the All Execution report. I created a table that gets populated when there are any errors when job gets executed. This is where I found this error.

Phil Parkin SSC Guru Points: 247137 More actions · Answer 8

ichervin wrote:

There are no error messages in the All Execution report. I created a table that gets populated when there are any errors when job gets executed. This is where I found this error.

Are you saying that the All Executions report says that the package completed successfully?

ichervin SSC-Addicted Points: 409 More actions · Answer 9

ichervin

SSC-Addicted

Points: 409

April 17, 2025 at 11:17 am

#4580424

Yes that is correct

Phil Parkin SSC Guru Points: 247137 More actions · Answer 10

OK, that is surprising.

What sort of connection are you using (ADO/OLEDB etc)? Can you supply a (redacted as needed) connection string that is failing?

Tav29 SSC Veteran Points: 269 More actions · Answer 11

Check connection string:

Sql Connection: Data Source=ServerName;Initial Catalog=DatabaseName;User ID=Username;Password=Password;

VS

OleDbConnection: Provider=SQLOLEDB;Data Source=ServerName;Initial Catalog=DatabaseName;User ID=Username;Password=Password;

If you're using SqlConnection in .NET, the connection string should not include the Provider keyword, as it's specific to OleDbConnection.